SQL Server Performance

Why my same stored procedure getting slower

Discussion in 'General DBA Questions' started by vladimir_sim, Jan 5, 2006.

  1. vladimir_sim New Member

    Hi,<br /><br />Anyone faces this issue, of why the same SP in SQL2k SP4<br />when initially run, is fast and okay.<br /><br />However, when i run it the second time or more, it just<br />keep getting slower from 30seconds to 2minutes.<br /><br />Anyone know who or how it is happening.<br /><br />My SP just using a cursor and even when<br />I change it to using loop, it does not<br />help.<br /><br />Thank you<br /><br />[<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />
  2. dineshasanka Moderator

  3. Adriaan New Member

    You haven't given us much info, so we can only give you wild guesses.

    Does your procedure insert data into the same tables from which it is reading information in your cursor?
    Are you creating huge temporary tables, or at least temporary tables with no indexes?
  4. Twan New Member

    Hi ya,

    I'd sugegst you run SQL Profiler to check the execution time of each statement within the proc, and see if you can see anything which might explain it?

    Cheers
    Twan

Share This Page