problem running SP vs. script

Question

problem running SP vs. script

jeschumm

SSC Enthusiast

Points: 144
More actions
July 23, 2009 at 1:17 pm

#208632

Hi All,
I have a SP that takes 5 minutes to run (when I use the exec statement), but when I take the code out of the SP and run it as a script in SSMS, it takes under 20 seconds. The code inside the SP is exactly the same. Has anyone seen this before? Any thoughts on how to fix this would be greatly appreciated.
Thanks!
Jeannine

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Parameter sniffing is the first thing that comes to my mind. Might help if you read the second article in my signature block below and follow the guidelines for posting it contains for performance problems. I'm sure with the right information some one here will be able to help you solve your problem.

jeschumm SSC Enthusiast Points: 144 More actions · Answer 2

Hi Lynn,

Thanks so much for your advice! I did some research on parameter sniffing (which I have to admit I'd never heard of) and found a great example of how to fix it. Once I made the changes to my SP, it ran in 10 seconds. Awesome!

Jeannine

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

jeschumm (7/23/2009)
Hi Lynn,
Thanks so much for your advice! I did some research on parameter sniffing (which I have to admit I'd never heard of) and found a great example of how to fix it. Once I made the changes to my SP, it ran in 10 seconds. Awesome!
Jeannine

Would you post the URL for that please? Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jeschumm SSC Enthusiast Points: 144 More actions · Answer 4

Sure -- here it is: http://elegantcode.com/2008/05/17/sql-parameter-sniffing-and-what-to-do-about-it/%5B/url%5D

Jeannine