SQL Server Performance

WAITFOR

Discussion in 'General Developer Questions' started by surendrakalekar, Nov 16, 2005.

  1. surendrakalekar New Member

    I want to use WAITFOR to execute one of our sp. Before that I would like to know the experience of all forum GURU's. Whether it is reliable?


    Surendra Kalekar

  2. Chappy New Member

    Its reliable, in that it will wait for the period you ask it to.

    But I wouldnt recommend it, remember this period could also be extending the duration for which the stored proc is holding locks, you need to be very careful.

    Could I ask why you want to use it? Perhaps theres another way
  3. surendrakalekar New Member

    quote:Originally posted by Chappy

    Its reliable, in that it will wait for the period you ask it to.

    But I wouldnt recommend it, remember this period could also be extending the duration for which the stored proc is holding locks, you need to be very careful.

    Could I ask why you want to use it? Perhaps theres another way

    The particular sp will execute in the midnight to update a particular column of the table. It#%92s a very small sp which will update few thousand records. This sp will be used only for this task.


    Surendra Kalekar

  4. Madhivanan Moderator

    Then why dont you schedule it as a job?

    Madhivanan

    Failing to plan is Planning to fail
  5. surendrakalekar New Member

    quote:Originally posted by Madhivanan

    Then why dont you schedule it as a job?

    Madhivanan

    Failing to plan is Planning to fail
    Do you mean scheduled job will be better solution than WAITFOR?



    Surendra Kalekar

  6. Madhivanan Moderator

    Not sure thats just an idea [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  7. Chappy New Member

    Yes, without a doubt, a job is better than WAITFOR <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />
  8. Chappy New Member

    I suppose I should say why..

    With a job you get all the extra features for free, such as 'retry N times' if job fails.
    You also get an audit trail in the job history, error reporting such as ability to email you when it fails etc

    SQL Agent runs as a service, its more reliable than leaving a single sql process/connection running, using WAITFOR. It also scales better in the future if you have 100 jobs, rather than 100 processes all doing WAITFOR
  9. surendrakalekar New Member

    Ok thanks Paul and Madhivanan I will go for Job then.


    Surendra Kalekar

Share This Page