SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using WAITFOR DELAY


Using WAITFOR DELAY

Author
Message
Karthika Raman
Karthika Raman
SSC-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 Visits: 169
I would like to solicit expert opinion about using WAITFOR DELAY and its impact on performance, lock issues etc. If you have a better alternative solution, please feel free to share it.

Following is an overview of the requirement and where WAITFOR DELAY is to be used.

Currently we have a tool that migrates some data into a sql staging table from a different system.
We have several sql tasks ( rolled into a scheduled job) that are meant to happen after the above migration.
Currently there is no link between the two and the sql scheduled job starts at the specified time regardless of the success/failure/completion of the migration task
All processes run during off hours

So

I am in the process of designing a solution to integrate the two processes.

The migration tool would update a database with the status of its completion/failure/success.
A SQL job scheduled to start at a certain time runs a stored procedure which does the following:

Checks the database for completion status - by polling the database at a set interval until a certain time.
IF it finds a successful completion, then it does further checking for each table and makes sure source and destination matches. If that match is successful, then it executes the next set of SQL tasks that are to happen after migration
IF it does not find a successful completion within the specified time, it stops and sends a notification.

The stored procedure code goes something like this
SET status = 0
WHILE curenttime < 'SpecifiedTime'
BEGIN
Look for migration status
IF status is successful
BEGIN
Do a comparison between source and destination
IF match is successful
Execute Job(SQL tasks)
SET status = 1
ELSE
Send notification of match failure
SET status = 1
END
ELSE
WAITFOR DELAY 'timeinterval'
END
If Status = 0
BEGIN
Send notification for migration failure
END


Thanks in advance for your thoughts on this.

KR
Adam Haines
Adam Haines
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4696 Visits: 3135
While this is a feasible solution there are potential pitfalls including networking congestion, timeouts, bad timing (BOL has a great example of how a wait delay can actually run longer than expected due to networking complications), locking etc..

To be honest the best and most robust solution would be to create an SSIS package to control the flow of the entire process. You can even save the package in SQL server and schedule it.

The benifits of a SSIS package:
1.This allows for error checking because you know exactly where the process failed and can even write the errors to a table and/or send a notification.
2.You never have to worry about timing, as you can control percisely when tasks fire.
3.You do not have to change your scripts because you can add tsql tasks to an SSIS package and still control the flow.

SSIS is a more scaleable solution and adds more data flexiblity.

-Adam



My blog: http://jahaines.blogspot.com
Karthika Raman
Karthika Raman
SSC-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 Visits: 169
I will not be able to control the entire process through SSIS. The migration tool cannot be controlled through SSIS. So given that I have to have a way of detecting when the process is complete and wait for it to happen and then fire off the next set of events. So there is waiting requirement whether I do it through SSIS or through Stored procedures. IS there a tool in SSIS that can replace the WAITFOR function and do it more efficiently?

Also this from BOL: You can create a deadlock by running a query with WAITFOR within a transaction that also holds locks preventing changes to the rowset that the WAITFOR statement is trying to access. SQL Server identifies these scenarios and returns an empty result set if the chance of such a deadlock exists.
-- However it is not clear to me in what situation the deadlock would actually be created. Obviously and I have tested my code this does not necessarily happen. In my WaitFor statement it looks for certain values in certain columns of a table that is being updated by the third party tool as it completes its migration process, and those updates seem to go fine while this stored procedure is still active.

Thanks
KR
Lowell
Lowell
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48182 Visits: 40593
Karthika Raman (1/17/2008)
I will not be able to control the entire process through SSIS. The migration tool cannot be controlled through SSIS. So given that I have to have a way of detecting when the process is complete and wait for it to happen and then fire off the next set of events. So there is waiting requirement whether I do it through SSIS or through Stored procedures. IS there a tool in SSIS that can replace the WAITFOR function and do it more efficiently?

Thanks
KR

It looks like you can have the migration tool at least set a flag, right? If that migration tool can write to a log table or something, then you can have a schedule job check for that status, and either run (if status is ready) or halt and await the next scheduled time (if status is zero?);that would avoid the waitfor altogether.
then you can just have the job scheduled to run every 2 minutes from midnight to 4 or whatever is the time window for execution.
at the end, the job can set the status to status=zero, so that if it runs at 12:14, all subsequent jobs just halt due to the flag...
is that a possibility?

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Steve Jones
Steve Jones
SSC Guru
SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)

Group: Administrators
Points: 108678 Visits: 19348
The issue I have with a continuous process is that when it fails, and it will, how do you detect that? I vote for a scheduled job that checks for the completion (based on your logic) and then starts the rest of your process.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Karthika Raman
Karthika Raman
SSC-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 Visits: 169
Both the above comments are very good points.

The reason for the continuous process design was that after detecting the flag(s) the migration tools can set, there is another level 'check' that I run at a table level - a successful match that should trigger the next steps in a job.

I still need help from both/either of you as to how I would do this in a scheduled job - If I am understanding you correctly - A scheduled job that runs every two minutes has a job step that runs a stored procedure that looks for the status of the flag (let us say value of the flag should be 0). The next step is set to run on the success of this step. How can I translate the value of the flag to the success/failure of a job step?

Thanks
KR
Adam Haines
Adam Haines
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4696 Visits: 3135
I would agree with the process running table. You should segregate the process into separate jobs like Steve and Lowell said. This way the second process will never run, unless the processed flag returns true.

I would personally avoid using waitfor because there are too many uncertainties.



My blog: http://jahaines.blogspot.com
Karthika Raman
Karthika Raman
SSC-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 Visits: 169
I would love to do it in the proposed design of doing it in separate job steps or through separate jobs. What I am having trouble is how I would cascade the steps/jobs based on the success or failure of the first step/job which is the migration. I am not sure how I would take the result of a query and translate it to job step logic. Also, I am trying to figure out how a failure notification would fit into this. I do not want a failure notification everytime the job runs looking for the flag value indicating success, but I do want to get the notification when the window for the job to have run is complete.

I hope I am making it clear at what I am looking for.

Any help towards this effort is greatly appreciated

Thanks
KR
Lowell
Lowell
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48182 Visits: 40593
Without more details, I'd build a prototype job that looked something like this:

IF EXISTS(SELECT STATUS FROM SOMETABLE WHERE STATUS='READY')
BEGIN
SET XACT_ABORT ON
BEGIN TRAN
EXEC PR_STEP1
EXEC PR_STEP2
EXEC PR_STEP3
UPDATE SOMETABLE SET STATUS='DONE'
COMMIT TRAN
END
ELSE
BEGIN
INSERT INTO TBLOG(MSG,MSGDATE) VALUES ('Status was not ready at this time',getdate())
END



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Adam Haines
Adam Haines
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4696 Visits: 3135
To addon to Lowell post you could also check for the existance of the flag and then start the job; otherwise, just finish up


IF EXISTS(SELECT STATUS FROM SOMETABLE WHERE STATUS='READY')
BEGIN
EXEC msdb.dbo.sp_start_job N'MyJob';
END
ELSE
BEGIN
INSERT INTO TBLOG(MSG,MSGDATE) VALUES ('Status was not ready at this time',getdate())
END

You would have to reset the value at the end of the second job, "MyJob".



My blog: http://jahaines.blogspot.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search