Why does this code run slower when called in an sp?

Last Post 29 Aug 2008 10:32 AM by nathankatcgfdotorg. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

29 Aug 2008 10:32 AM
I have code which is 470 lines. I've got Query ANalyzer open, two different windows and in one I've got the sp and param values and am calling it like this:

exec usp_TESCourseEQMigrateReceivesFlatCurrent2 37875, 26729, 269390

It takes 27 minutes to run.

In the other connection window in Query Analyzer I have the code from the sp above and the same parameter values set and when I run the code like that it takes 31 seconds to run.

The code is identical in both cases. It starts out with about 40 variables declared. Has some selects with assignments. And it takes so long in both cases because it is looping through a record set and inserting records to a table.

Again, the code is exactly the same in both cases. The same result can be repeated at anytime, meaning server load variations aren't the cause. I even recompiled the sp to make extra sure the code was exactly the same and to see if there was some sort of compilation problem.

Anyone know why one would take 31 seconds and the other 27 minutes?

Acceptable Use Policy