Hi All,

     the background to this challenge is a repetitively used stored procedure that usually take no longer than 5 seconds each execution. Unfortunately, it is run thousands of times and so every millisecond I can drop off the SP execution time will help. So the plan of attack was to insert selects of the current timestamp in different places in the SP and run using debug to find the offending section in the SP. Basically a divide and rule approach.

BUT

1. We know that selecting the current timestamp in an SP is useless because, although it gives you fractions of a second, the timestamp does not change for the duration of the SP. 

2. We can use select (dbinfo-utc_to_datetime sh_curtime)  from sysmaster:sysshmvals but that sh_curtime is just an integer and thus has a resolution of just one second. 

So, would I be right in saying that my only way around this is to call a UDR written in C or something that will provide me with the accuracy that I need?

Andrew:

Yes, creating and calling a UDR is the only solution. I have a UDR coded that I can give you. Reach out to me directly.

Example:

Hi All,

     the background to this challenge is a repetitively used stored procedure that usually take no longer than 5 seconds each execution. Unfortunately, it is run thousands of times and so every millisecond I can drop off the SP execution time will help. So the plan of attack was to insert selects of the current timestamp in different places in the SP and run using debug to find the offending section in the SP. Basically a divide and rule approach.

BUT

1. We know that selecting the current timestamp in an SP is useless because, although it gives you fractions of a second, the timestamp does not change for the duration of the SP. 

2. We can use select (dbinfo-utc_to_datetime sh_curtime)  from sysmaster:sysshmvals but that sh_curtime is just an integer and thus has a resolution of just one second. 

So, would I be right in saying that my only way around this is to call a UDR written in C or something that will provide me with the accuracy that I need?

Hi All,

     the background to this challenge is a repetitively used stored procedure that usually take no longer than 5 seconds each execution. Unfortunately, it is run thousands of times and so every millisecond I can drop off the SP execution time will help. So the plan of attack was to insert selects of the current timestamp in different places in the SP and run using debug to find the offending section in the SP. Basically a divide and rule approach.

BUT

1. We know that selecting the current timestamp in an SP is useless because, although it gives you fractions of a second, the timestamp does not change for the duration of the SP. 

2. We can use select (dbinfo-utc_to_datetime sh_curtime)  from sysmaster:sysshmvals but that sh_curtime is just an integer and thus has a resolution of just one second. 

So, would I be right in saying that my only way around this is to call a UDR written in C or something that will provide me with the accuracy that I need?

Hi All,

     the background to this challenge is a repetitively used stored procedure that usually take no longer than 5 seconds each execution. Unfortunately, it is run thousands of times and so every millisecond I can drop off the SP execution time will help. So the plan of attack was to insert selects of the current timestamp in different places in the SP and run using debug to find the offending section in the SP. Basically a divide and rule approach.

BUT

1. We know that selecting the current timestamp in an SP is useless because, although it gives you fractions of a second, the timestamp does not change for the duration of the SP. 

2. We can use select (dbinfo-utc_to_datetime sh_curtime)  from sysmaster:sysshmvals but that sh_curtime is just an integer and thus has a resolution of just one second. 

So, would I be right in saying that my only way around this is to call a UDR written in C or something that will provide me with the accuracy that I need?

Hi All,

     the background to this challenge is a repetitively used stored procedure that usually take no longer than 5 seconds each execution. Unfortunately, it is run thousands of times and so every millisecond I can drop off the SP execution time will help. So the plan of attack was to insert selects of the current timestamp in different places in the SP and run using debug to find the offending section in the SP. Basically a divide and rule approach.

BUT

1. We know that selecting the current timestamp in an SP is useless because, although it gives you fractions of a second, the timestamp does not change for the duration of the SP. 

2. We can use select (dbinfo-utc_to_datetime sh_curtime)  from sysmaster:sysshmvals but that sh_curtime is just an integer and thus has a resolution of just one second. 

So, would I be right in saying that my only way around this is to call a UDR written in C or something that will provide me with the accuracy that I need?

Hello Andrew,

It is easier. You can determine the current time with this select:

define ts datetime year to fraction(5);

select dbinfo('utc_to_datetime', sh_curtime) into ts from sysmaster:sysshmvals;

Make sure that the variable USEOSTIME is set to 1 in the onconfig so that you get the full time resolution.

regards Andreas

Hi All,

     the background to this challenge is a repetitively used stored procedure that usually take no longer than 5 seconds each execution. Unfortunately, it is run thousands of times and so every millisecond I can drop off the SP execution time will help. So the plan of attack was to insert selects of the current timestamp in different places in the SP and run using debug to find the offending section in the SP. Basically a divide and rule approach.

BUT

1. We know that selecting the current timestamp in an SP is useless because, although it gives you fractions of a second, the timestamp does not change for the duration of the SP. 

2. We can use select (dbinfo-utc_to_datetime sh_curtime)  from sysmaster:sysshmvals but that sh_curtime is just an integer and thus has a resolution of just one second. 

So, would I be right in saying that my only way around this is to call a UDR written in C or something that will provide me with the accuracy that I need?

Hi All,

     the background to this challenge is a repetitively used stored procedure that usually take no longer than 5 seconds each execution. Unfortunately, it is run thousands of times and so every millisecond I can drop off the SP execution time will help. So the plan of attack was to insert selects of the current timestamp in different places in the SP and run using debug to find the offending section in the SP. Basically a divide and rule approach.

BUT

1. We know that selecting the current timestamp in an SP is useless because, although it gives you fractions of a second, the timestamp does not change for the duration of the SP. 

2. We can use select (dbinfo-utc_to_datetime sh_curtime)  from sysmaster:sysshmvals but that sh_curtime is just an integer and thus has a resolution of just one second. 

So, would I be right in saying that my only way around this is to call a UDR written in C or something that will provide me with the accuracy that I need?

------------------------------
Andrew Cilia
------------------------------

Hi All,

     the background to this challenge is a repetitively used stored procedure that usually take no longer than 5 seconds each execution. Unfortunately, it is run thousands of times and so every millisecond I can drop off the SP execution time will help. So the plan of attack was to insert selects of the current timestamp in different places in the SP and run using debug to find the offending section in the SP. Basically a divide and rule approach.

BUT

1. We know that selecting the current timestamp in an SP is useless because, although it gives you fractions of a second, the timestamp does not change for the duration of the SP. 

2. We can use select (dbinfo-utc_to_datetime sh_curtime)  from sysmaster:sysshmvals but that sh_curtime is just an integer and thus has a resolution of just one second. 

So, would I be right in saying that my only way around this is to call a UDR written in C or something that will provide me with the accuracy that I need?

Hi Andrew,

how about this guy:

  create procedure ostime(inout integer, inout integer) external name '(_ostime)' language C;

Through its two inout params it will give you seconds and micro-seconds immediately from a call to gettimeofday().

To demonstrate:

HTH,
 Andreas

Hi All,

     the background to this challenge is a repetitively used stored procedure that usually take no longer than 5 seconds each execution. Unfortunately, it is run thousands of times and so every millisecond I can drop off the SP execution time will help. So the plan of attack was to insert selects of the current timestamp in different places in the SP and run using debug to find the offending section in the SP. Basically a divide and rule approach.

BUT

1. We know that selecting the current timestamp in an SP is useless because, although it gives you fractions of a second, the timestamp does not change for the duration of the SP. 

2. We can use select (dbinfo-utc_to_datetime sh_curtime)  from sysmaster:sysshmvals but that sh_curtime is just an integer and thus has a resolution of just one second. 

So, would I be right in saying that my only way around this is to call a UDR written in C or something that will provide me with the accuracy that I need?

Andreas:

Very cool function, but the underlying _ostime() function is an undocumented internal function. Also, it requires USEOSTIME to be set to 1 (and a server restart if it is not already set). 

FWIW, here's a version of your "test" program that returns only five subsecond digits so it can be cast to a datetime for other uses and resolutions:

So:

Hi Andrew,

how about this guy:

  create procedure ostime(inout integer, inout integer) external name '(_ostime)' language C;

Through its two inout params it will give you seconds and micro-seconds immediately from a call to gettimeofday().

To demonstrate:

HTH,
 Andreas

Hi All,

     the background to this challenge is a repetitively used stored procedure that usually take no longer than 5 seconds each execution. Unfortunately, it is run thousands of times and so every millisecond I can drop off the SP execution time will help. So the plan of attack was to insert selects of the current timestamp in different places in the SP and run using debug to find the offending section in the SP. Basically a divide and rule approach.

BUT

1. We know that selecting the current timestamp in an SP is useless because, although it gives you fractions of a second, the timestamp does not change for the duration of the SP. 

2. We can use select (dbinfo-utc_to_datetime sh_curtime)  from sysmaster:sysshmvals but that sh_curtime is just an integer and thus has a resolution of just one second. 

So, would I be right in saying that my only way around this is to call a UDR written in C or something that will provide me with the accuracy that I need?

Hi Art,

I don't think it depends on USEOSTIME, it works well also with USEOSTIME 0.

And careful with the microseconds: e.g. a value of 153 first had to be left padded to full six digits 000153 before any decision on how many digits after the dot you're interested in.

Also: OUT instead of INOUT would of course be good enough in this case.

Andreas

Andreas:

Very cool function, but the underlying _ostime() function is an undocumented internal function. Also, it requires USEOSTIME to be set to 1 (and a server restart if it is not already set). 

FWIW, here's a version of your "test" program that returns only five subsecond digits so it can be cast to a datetime for other uses and resolutions:

So:

Hi Andrew,

how about this guy:

  create procedure ostime(inout integer, inout integer) external name '(_ostime)' language C;

Through its two inout params it will give you seconds and micro-seconds immediately from a call to gettimeofday().

To demonstrate:

HTH,
 Andreas

Hi All,

     the background to this challenge is a repetitively used stored procedure that usually take no longer than 5 seconds each execution. Unfortunately, it is run thousands of times and so every millisecond I can drop off the SP execution time will help. So the plan of attack was to insert selects of the current timestamp in different places in the SP and run using debug to find the offending section in the SP. Basically a divide and rule approach.

BUT

1. We know that selecting the current timestamp in an SP is useless because, although it gives you fractions of a second, the timestamp does not change for the duration of the SP. 

2. We can use select (dbinfo-utc_to_datetime sh_curtime)  from sysmaster:sysshmvals but that sh_curtime is just an integer and thus has a resolution of just one second. 

So, would I be right in saying that my only way around this is to call a UDR written in C or something that will provide me with the accuracy that I need?