Why same stored procedure getting slower ??

Question

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Unfortunately, without seeing the code, knowing if this process is the only item running on the server, etc. it is hard to guess as to why

Good Hunting!

Vladimir Sim Ten Centuries Points: 1310 More actions · Answer 2

Hi,

Actually, my SP just having 02 cursor

where the outer cursor will pass some

info to the inner cursor to process

and update some data.

I have modify the outer cursor into

using loop as my testing show that

changing the inner cursor to loop

does not have any speed improvement.

Or maybe it does have some improvement

as subsequence running of the same SP

will deteriorate my system, causing the

same SP to slow down to maybe 2 or 3 minutes.

Anything to look out for.

Thank you

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 3

Could you post your actual code (maybe changing some names)?

Actually, my SP just having 02 cursor

where the outer cursor will pass some

info to the inner cursor to process

and update some data.

is still too generic and maybe the cursors can be thrown out all together.

Does it help storing your stored procedures with the option WITH RECOMPILE?

Chris Castle Old Hand Points: 381 More actions · Answer 4

Is your inner cursor performing an update? What is the sp doing?

dan trate Old Hand Points: 352 More actions · Answer 5

Since you didn't post the code snipet, one of the first things I would check is to make sure that you DEALLOCATE the cursors after you CLOSE them.