Results 1 to 9 of 9

Thread: time a SQL quey

  1. #1
    Join Date
    Feb 2004
    Posts
    8

    Unanswered: time a SQL quey

    Hi,
    is there a function in INFORMIX(9.3) that can time a SQL query ?

    thaks

  2. #2
    Join Date
    Feb 2004
    Posts
    32
    Add the following before and after the query........
    select distinct current from systables;

    -Abraham

  3. #3
    Join Date
    Feb 2004
    Posts
    8
    tkanks ! I'll try tomorrow

  4. #4
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    It needs slight change as below:

    select current as start_time from systables where tabid=1
    into temp x with no log;
    -- query to be benchmarked for response time
    select current - start_time from systables,x where tabid=1;

    Regards,
    Shriyan

  5. #5
    Join Date
    Feb 2004
    Posts
    8
    hi again,
    please what is the type of the returned value ? I want to handle this value in a java program so I need to know the type to be able to take it from my result set:

    rs.getInt () ?
    rs.getDate() ?
    ...

    thanks

  6. #6
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    Hi,

    Well, the return data type is DATETIME DAY TO FRACTION, according to the format below:

    DDDDDD HH:MMS.FFF

    I'm not a java programmer, so I may not be the right person to think of the equivalent data type in java.

    Regards,
    Shriyan

  7. #7
    Join Date
    Feb 2004
    Posts
    8
    okey thanks anyway

  8. #8
    Join Date
    Dec 2002
    Location
    Portland, OR, USA
    Posts
    26

    Is CURRENT your friend? Hmmm...

    Hi,

    I am not sure if this helps, but I have trouble trusting CURRENT, because of the internals- you cannot see when the db is updating the value of CURRENT. For instance, during execution of a stored procedure, returns the same value, no matter how much time has actually elapsed.

    I would suggest that you might be better off doing your timing in your Java code, or barring that, if you have an engine running on the DB server that manages the connections, do it in there.

    We're doing it in our C++ request handler, which seems to work pretty well. It really depends on what you're trying to measure: raw database speed, or the time from the beginning to the end of a txn, and whether you're trying to isolate the impacts of network traffic... at least for us.

    Hope this helps...

    Regards,
    Joe

  9. #9
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    183
    Probably cuurent will give you coorect answers but it only gives you full seconds output. If this is the case you can change this behaviour by setting the configuration parameter OSTIME to 1 instead of 0 (default). Now IDS will not read the time out of shared memory but will do a system call.

    Another more advanced solution is to create some stored procedures that will do the trick. Probably C is the best solution is the best idea. This is quite easy to find out. You can read more about this at the developers corner on the informaix part of the IBM website.

    Maybe a different approach,

    Rob Prop

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •