Current time in Stored Procedures
A user wanted sub-second timing inside a stored procedure to measure how long part of it takes; CURRENT and the sysmaster:sysshmvals trick only give one-second granularity. Suggestions included setting USEOSTIME 1 in ONCONFIG, but it was pointed out that CURRENT is frozen for the duration of the top-level SQL statement, and calling a nested procedure doesn't refresh it. sysshmvals does update during execution but is still only second-granular, as Jonathan Leffler acknowledged. One poster said he fetches fractional time from a second database instance. No clean in-engine solution was recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I want the execution time for part of a Stored Procedure (SP) and need=20 higher precission than second which I can get from the function=20 SELECT DBINFO('utc=5Fto=5Fdatetime', sh=5Fcurtime) sh=5Fcurtime fr= om=20 sysmaster:sysshmvals; Is there any other method which gives the current time in a SP with=20 fractions of second? Olle Str=F6mhielm Frontec Sverige AB, Torpavallsgatan 9, 416 73 G=F6teborg Tel: 031-707 1081, Fax: 031-707 1199
Tricky territory. Check USEOSTIME 1 in your ONCONFIG file. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | "olle.stromh...."| | | <olle.stromhielm@| | | frontec.se> | | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 07/31/2003 12:37 | | | AM | | | | |---------+----------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: Current time in Stored Procedures [1608] | | | >------------------------------------------------------------------------------- --------------------------------------------------------------| I want the execution time for part of a Stored Procedure (SP) and need=20 higher precission than second which I can get from the function=20 SELECT DBINFO('utc=5Fto=5Fdatetime', sh=5Fcurtime) sh=5Fcurtime fr= om=20 sysmaster:sysshmvals; Is there any other method which gives the current time in a SP with=20 fractions of second? Olle Str=F6mhielm Frontec Sverige AB, Torpavallsgatan 9, 416 73 G=F6teborg Tel: 031-707 1081, Fax: 031-707 1199
There is one more problem about that. Informix really gives better accuracy with " USEOSTIME 1". Unfortunately, the 'Current' time doesn't change while the stored procedure is running. That is, even with USEOSTIME=1 it's impossible to measure the duration of the stored procedure ------------------------------------------ Alexey Sonkin Senior Database Administrator > -----Original Message----- > From: Jonathan Le.... [mailto:jleffler@us.ibm.com] > Sent: Thursday, July 31, 2003 2:13 PM > To: ids@iiug.org > Subject: Re: Current time in Stored Procedures [1610] > > > > > > Tricky territory. Check USEOSTIME 1 in your ONCONFIG file. > -- > Jonathan Leffler (jleffler@us.ibm.com) > STSM, Informix Database Engineering, IBM Data Management > 4100 Bohannon Drive, Menlo Park, CA 94025 > Tel: +1 650-926-6921 Tie-Line: 630-6921 > "I don't suffer from insanity; I enjoy every minute of it!" > > > > > |---------+----------------------------> > | | "olle.stromh...."| > | | <olle.stromhielm@| > | | frontec.se> | > | | Sent by: | > | | forum.subscriber@| > | | iiug.org | > | | | > | | | > | | 07/31/2003 12:37 | > | | AM | > | | | > |---------+----------------------------> > >----------------------------------------------------------------------- > ----------------------------------------------------------------------| > | > | > | To: ids@iiug.org > | > | cc: > | > | Subject: Current time in Stored Procedures [1608] > | > | > | > >----------------------------------------------------------------------- > ----------------------------------------------------------------------| > > > > > I want the execution time for part of a Stored Procedure (SP) and need=20 > higher precission than second which I can get from the function=20 > SELECT DBINFO('utc=5Fto=5Fdatetime', sh=5Fcurtime) sh=5Fcurtime > fr= > om=20 > sysmaster:sysshmvals; > Is there any other method which gives the current time in a SP with=20 > fractions of second? > > Olle Str=F6mhielm > Frontec Sverige AB, Torpavallsgatan 9, 416 73 G=F6teborg > Tel: 031-707 1081, Fax: 031-707 1199 > > >
but you can call another stored proc at start and end for time Alexey Sonkin wrote: > There is one more problem about that. > Informix really gives better accuracy with " USEOSTIME 1". > Unfortunately, the 'Current' time doesn't change > while the stored procedure is running. That is, > even with USEOSTIME=1 it's impossible to measure > the duration of the stored procedure > > ------------------------------------------ > Alexey Sonkin > Senior Database Administrator > > > -----Original Message----- > > From: Jonathan Le.... [mailto:jleffler@us.ibm.com] > > Sent: Thursday, July 31, 2003 2:13 PM > > To: ids@iiug.org > > Subject: Re: Current time in Stored Procedures [1610] > > > > > > > > > > > > Tricky territory. Check USEOSTIME 1 in your ONCONFIG file. > > -- > > Jonathan Leffler (jleffler@us.ibm.com) > > STSM, Informix Database Engineering, IBM Data Management > > 4100 Bohannon Drive, Menlo Park, CA 94025 > > Tel: +1 650-926-6921 Tie-Line: 630-6921 > > "I don't suffer from insanity; I enjoy every minute of it!" > > > > > > > > > > |---------+----------------------------> > > | | "olle.stromh...."| > > | | <olle.stromhielm@| > > | | frontec.se> | > > | | Sent by: | > > | | forum.subscriber@| > > | | iiug.org | > > | | | > > | | | > > | | 07/31/2003 12:37 | > > | | AM | > > | | | > > |---------+----------------------------> > > >----------------------------------------------------------------------- > > ----------------------------------------------------------------------| > > | > > | > > | To: ids@iiug.org > > | > > | cc: > > | > > | Subject: Current time in Stored Procedures [1608] > > | > > | > > | > > >----------------------------------------------------------------------- > > ----------------------------------------------------------------------| > > > > > > > > > > I want the execution time for part of a Stored Procedure (SP) and need=20 > > higher precission than second which I can get from the function=20 > > SELECT DBINFO('utc=5Fto=5Fdatetime', sh=5Fcurtime) sh=5Fcurtime > > fr= > > om=20 > > sysmaster:sysshmvals; > > Is there any other method which gives the current time in a SP with=20 > > fractions of second? > > > > Olle Str=F6mhielm > > Frontec Sverige AB, Torpavallsgatan 9, 416 73 G=F6teborg > > Tel: 031-707 1081, Fax: 031-707 1199 > > > > > >
> > > Is there any other method which gives the current time in a SP with fractions of second? Of course. I receive the current time in SP with fraction of second from another instance of dbserver. ----- Original Message ----- From: "preetinder ...." <preetinder.dhaliwal@dhl.com> To: <ids@iiug.org> Sent: Friday, August 01, 2003 1:15 AM Subject: Re: Current time in Stored Procedures [1613] > but you can call another stored proc at start and end for time > > Alexey Sonkin wrote: > > > There is one more problem about that. > > Informix really gives better accuracy with " USEOSTIME 1". > > Unfortunately, the 'Current' time doesn't change > > while the stored procedure is running. That is, > > even with USEOSTIME=1 it's impossible to measure > > the duration of the stored procedure > > > > ------------------------------------------ > > Alexey Sonkin > > Senior Database Administrator > > > > > -----Original Message----- > > > From: Jonathan Le.... [mailto:jleffler@us.ibm.com] > > > Sent: Thursday, July 31, 2003 2:13 PM > > > To: ids@iiug.org > > > Subject: Re: Current time in Stored Procedures [1610] > > > > > > > > > > > > > > > > > > Tricky territory. Check USEOSTIME 1 in your ONCONFIG file. > > > -- > > > Jonathan Leffler (jleffler@us.ibm.com) > > > STSM, Informix Database Engineering, IBM Data Management > > > 4100 Bohannon Drive, Menlo Park, CA 94025 > > > Tel: +1 650-926-6921 Tie-Line: 630-6921 > > > "I don't suffer from insanity; I enjoy every minute of it!" > > > > > > > > > > > > > > > |---------+----------------------------> > > > | | "olle.stromh...."| > > > | | <olle.stromhielm@| > > > | | frontec.se> | > > > | | Sent by: | > > > | | forum.subscriber@| > > > | | iiug.org | > > > | | | > > > | | | > > > | | 07/31/2003 12:37 | > > > | | AM | > > > | | | > > > |---------+----------------------------> > > > >----------------------------------------------------------------------- > > > ----------------------------------------------------------------------| > > > | > > > | > > > | To: ids@iiug.org > > > | > > > | cc: > > > | > > > | Subject: Current time in Stored Procedures [1608] > > > | > > > | > > > | > > > >----------------------------------------------------------------------- > > > ----------------------------------------------------------------------| > > > > > > > > > > > > > > > I want the execution time for part of a Stored Procedure (SP) and need=20 > > > higher precission than second which I can get from the function=20 > > > SELECT DBINFO('utc=5Fto=5Fdatetime', sh=5Fcurtime) sh=5Fcurtime > > > fr= > > > om=20 > > > sysmaster:sysshmvals; > > > Is there any other method which gives the current time in a SP with=20 > > > fractions of second? > > > > > > Olle Str=F6mhielm > > > Frontec Sverige AB, Torpavallsgatan 9, 416 73 G=F6teborg > > > Tel: 031-707 1081, Fax: 031-707 1199 > > > > > > > > > > > >
No; time in general is frozen for the duration of the top-level SQL statement that you are executing - calling another stored procedure does not help. IN GENERAL! However, the original code gets around this by pulling values from sysmaster:sysshmvals, and this really does change on the fly. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+-----------------------------> | | "preetinder ...." | | | <preetinder.dhaliw| | | al@dhl.com> | | | Sent by: | | | forum.subscriber@i| | | iug.org | | | | | | | | | 07/31/2003 04:15 | | | PM | | | | |---------+-----------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: Re: Current time in Stored Procedures [1613] | | | >------------------------------------------------------------------------------- --------------------------------------------------------------| but you can call another stored proc at start and end for time Alexey Sonkin wrote: > There is one more problem about that. > Informix really gives better accuracy with " USEOSTIME 1". > Unfortunately, the 'Current' time doesn't change > while the stored procedure is running. That is, > even with USEOSTIME=1 it's impossible to measure > the duration of the stored procedure > > ------------------------------------------ > Alexey Sonkin > Senior Database Administrator > > > -----Original Message----- > > From: Jonathan Le.... [mailto:jleffler@us.ibm.com] > > Sent: Thursday, July 31, 2003 2:13 PM > > To: ids@iiug.org > > Subject: Re: Current time in Stored Procedures [1610] > > > > > > > > > > > > Tricky territory. Check USEOSTIME 1 in your ONCONFIG file. > > -- > > Jonathan Leffler (jleffler@us.ibm.com) > > STSM, Informix Database Engineering, IBM Data Management > > 4100 Bohannon Drive, Menlo Park, CA 94025 > > Tel: +1 650-926-6921 Tie-Line: 630-6921 > > "I don't suffer from insanity; I enjoy every minute of it!" > > > > > > > > > > |---------+----------------------------> > > | | "olle.stromh...."| > > | | <olle.stromhielm@| > > | | frontec.se> | > > | | Sent by: | > > | | forum.subscriber@| > > | | iiug.org | > > | | | > > | | | > > | | 07/31/2003 12:37 | > > | | AM | > > | | | > > |---------+----------------------------> > > >----------------------------------------------------------------------- > > ----------------------------------------------------------------------| > > | > > | > > | To: ids@iiug.org > > | > > | cc: > > | > > | Subject: Current time in Stored Procedures [1608] > > | > > | > > | > > >----------------------------------------------------------------------- > > ----------------------------------------------------------------------| > > > > > > > > > > I want the execution time for part of a Stored Procedure (SP) and need=20 > > higher precission than second which I can get from the function=20 > > SELECT DBINFO('utc=5Fto=5Fdatetime', sh=5Fcurtime) sh=5Fcurtime > > fr= > > om=20 > > sysmaster:sysshmvals; > > Is there any other method which gives the current time in a SP with=20 > > fractions of second? > > > > Olle Str=F6mhielm > > Frontec Sverige AB, Torpavallsgatan 9, 416 73 G=F6teborg > > Tel: 031-707 1081, Fax: 031-707 1199 > > > > > >
OTOH, sysmaster:sysshmvals.curtime has a granularity of 1 second. There isn't a sub-second version of it as far as I know. Sorry for the misleading info earlier. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | Jonathan | | | Leffler/Menlo | | | Park/IBM@IBMUS | | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 08/01/2003 11:49 | | | AM | | | | |---------+----------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: Re: Current time in Stored Procedures [1619] | | | >------------------------------------------------------------------------------- --------------------------------------------------------------| No; time in general is frozen for the duration of the top-level SQL statement that you are executing - calling another stored procedure does not help. IN GENERAL! However, the original code gets around this by pulling values from sysmaster:sysshmvals, and this really does change on the fly. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+-----------------------------> | | "preetinder ...." | | | <preetinder.dhaliw| | | al@dhl.com> | | | Sent by: | | | forum.subscriber@i| | | iug.org | | | | | | | | | 07/31/2003 04:15 | | | PM | | | | |---------+-----------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: Re: Current time in Stored Procedures [1613] | | | >------------------------------------------------------------------------------- --------------------------------------------------------------| but you can call another stored proc at start and end for time Alexey Sonkin wrote: > There is one more problem about that. > Informix really gives better accuracy with " USEOSTIME 1". > Unfortunately, the 'Current' time doesn't change > while the stored procedure is running. That is, > even with USEOSTIME=1 it's impossible to measure > the duration of the stored procedure > > ------------------------------------------ > Alexey Sonkin > Senior Database Administrator > > > -----Original Message----- > > From: Jonathan Le.... [mailto:jleffler@us.ibm.com] > > Sent: Thursday, July 31, 2003 2:13 PM > > To: ids@iiug.org > > Subject: Re: Current time in Stored Procedures [1610] > > > > > > > > > > > > Tricky territory. Check USEOSTIME 1 in your ONCONFIG file. > > -- > > Jonathan Leffler (jleffler@us.ibm.com) > > STSM, Informix Database Engineering, IBM Data Management > > 4100 Bohannon Drive, Menlo Park, CA 94025 > > Tel: +1 650-926-6921 Tie-Line: 630-6921 > > "I don't suffer from insanity; I enjoy every minute of it!" > > > > > > > > > > |---------+----------------------------> > > | | "olle.stromh...."| > > | | <olle.stromhielm@| > > | | frontec.se> | > > | | Sent by: | > > | | forum.subscriber@| > > | | iiug.org | > > | | | > > | | | > > | | 07/31/2003 12:37 | > > | | AM | > > | | | > > |---------+----------------------------> > > >----------------------------------------------------------------------- > > ----------------------------------------------------------------------| > > | > > | > > | To: ids@iiug.org > > | > > | cc: > > | > > | Subject: Current time in Stored Procedures [1608] > > | > > | > > | > > >----------------------------------------------------------------------- > > ----------------------------------------------------------------------| > > > > > > > > > > I want the execution time for part of a Stored Procedure (SP) and need=20 > > higher precission than second which I can get from the function=20 > > SELECT DBINFO('utc=5Fto=5Fdatetime', sh=5Fcurtime) sh=5Fcurtime > > fr= > > om=20 > > sysmaster:sysshmvals; > > Is there any other method which gives the current time in a SP with=20 > > fractions of second? > > > > Olle Str=F6mhielm > > Frontec Sverige AB, Torpavallsgatan 9, 416 73 G=F6teborg > > Tel: 031-707 1081, Fax: 031-707 1199 > > > > > >