February 26, 2013 at 2:38 pm
I have a stored procedure which runs in about 1 second. If I take the code out of the stored procedure and run it by itself it takes about 5 minutes. The execution plan for the stored procedure saves the paralellism to the very end and the plan for the code itself runs the paralellism at various points throughout the plan.
Why would the plans be different, first off? Does SQL treat the stored procedure with different performance enhancements than it does regular sql?
February 26, 2013 at 5:32 pm
Do you use exactly the same code as used in the sproc?
Or did you replace a few variables in the query with hardcoded values?
It seems like the compiled version of the sproc is based on a different sample data distribution as the ad-hoc query leading to different execution plans...
February 27, 2013 at 12:36 pm
It's exactly the same code, variables included. Part of what's missing is that there is a cartesian product in the code. Basically one of those..."let's look at a million rows where the ID is in (select another million rows)..." so the code, when executed outside the procedure produces an 80 million row select..
That part of the code execution plan is NOT in the procedure execution plan and I suspect that the optimizer might be rewriting that particular part when it's compiling.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply