All Forums Database
ysrinu 44 posts Joined 04/07
21 Oct 2008
How to Wait, Pause or Sleep

Hi,in my stored procedure i want to wait/pause/sleep for few milliseconds.I am not waiting for any action to complete, i just have to pause for a while.How can i do this?Thanks,-srinivas yelamanchili

joedsilva 505 posts Joined 07/05
21 Oct 2008

There's a tricky way I can think of ( or probably a bunch of tricky ways to explore)...But why are trying to do it ? is it for debugging purposes ?There could be a better way to do it that we might be able to suggest.

Adeel Chaudhry 773 posts Joined 04/08
21 Oct 2008

Hello,You can also try UDF with a sleep function in it.But again, whats the purpose for such activity?Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

ysrinu 44 posts Joined 04/07
21 Oct 2008

My stored procedure is a recursive one, and it inserts the current timestamp for every call.The procedure runs so fast that the timestamp is the same for two consecutive calls upto precision of 6seconds.I therefore, want to wait for at least 0.000001 sec before the next call.I know, i can use a new column and store counter value that is incremented for each call, but would prefer to use the timestamp instead.UDFs are not supported and not recommended by our DBAs in our project.Thanks,-srinivas

Adeel Chaudhry 773 posts Joined 04/08
21 Oct 2008

couple of questions...one from where SPs are being called, some sort of front-end application or bteq etc? second why UDFs are not supported/recommended....this one is for my knowledge.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Adeel Chaudhry 773 posts Joined 04/08
21 Oct 2008

Can you also provide the part of SP code in which you are inserting a row?

-- If you are stuck at something .... consider it an opportunity to think anew.

ysrinu 44 posts Joined 04/07
22 Oct 2008

Hi,i am calling the SP from bteq. This shouldn't matter much, as i am only interested in keeping different timestamps in the debug table for sp calls that run linearly.No UDFs have ever been written yet, and the DBAs are not really in favor of this, i can't explain why.When the SP calls itself recursively based on if condition, the rows inserted in the debug table have same timestamp for two consecutive calls,and therefore want to sleep for a fraction of second before the sp call.Here's the pseudo-code:sp_sample(INOUT io_retcd){if (...) then ... insert into debug table (current_timestamp,...);else call sp_sample(...) insert into debug table (current_timestamp,...);end if}Thanks,-srinivas

Adeel Chaudhry 773 posts Joined 04/08
22 Oct 2008

For such case you dont need to have sleep functionality, i'll suggest you to have a timestamp parameter for the SP and in the recursive call simply add 1 second to it or add 0.000001 second to it (as per your requirement).At the top you will also have to check for the first time input, i.e. if the timestamp parameter is null, then just set the variable equal to current_timestamp.HTH!Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

joedsilva 505 posts Joined 07/05
22 Oct 2008

Few ways to approach.Pass the timestamp as argument to the initial proc call, and then it can increment it and pass it on to the next call etc...mySP(ts timestamp){do stuff...(if call again) call mySP(ts+1);}call mySP (current_timestamp(0));Use a counter variable in the same way, pass 0 to the first call, and any next calls can increment and pass it on ...mySP(cnter INTEGER){do stuff...(if call again) call mySP(cnter+1);}I would prefer the second approach.

huwlewis 3 posts Joined 10/08
22 Oct 2008

Don't forget that the precision of current_timestamp is not 6 digits, even if you specify current_timestamp(6). Check out the precision (I think it is only 2 decimal digits normally) as adding 0.000001 seconds may make no difference, unless you sleep for at least 0.01 seconds.

ysrinu 44 posts Joined 04/07
22 Oct 2008

Thanks to all for your suggestions on timestamp.I have two timestamps (start and end) and i have to increment both to avoid duplicate values and this canbe too much of man-handling the timestamps.I am therefore using a new column that increments with every call.Thanks about the precision too. I did notice that even though it's declared for (6) digitsthe last 4 are always '0000' and the duplicates are always on the first 2.I initially wondered how two consecutive calls can have same timestamp upto 6digits of seconds (unless TD engine is running on super computer),now i know it's only precise upto .00 only (and not until 0.000000)Thanks once again.-srinivas

bloggersid 1 post Joined 04/10
06 Jul 2010

Here is my case.

I will have one table which will have a set of records. Once I reach a limit (lets say 100), i need to do some processing in my SP.

So, I would want to place a WHILE loop and check for the count of records. If it did not reach 100, i will wait some time and then check...

is this possible or is there any better way for this ?

Thanks
- Ravi

You must sign in to leave a comment.