Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored procedure runs faster than the code it contains... Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2013 2:38 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 10:32 AM
Points: 546, Visits: 1,063
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?
Post #1424253
Posted Tuesday, February 26, 2013 5:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:41 PM
Points: 6,880, Visits: 13,465
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...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1424294
Posted Wednesday, February 27, 2013 12:36 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 10:32 AM
Points: 546, Visits: 1,063
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.

Post #1424692
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse