|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Slow Stored ProcI have a stored proc that can take 30 minutes to run. In QA it takes only 30
seconds. Once complete, however, the execution plans are essentially the same. My other similar SP's don't have the issue and I've dropped and recreated the SP just in case. Any reason a SP would be slower than QA? As always, it depends. Poorly-written client code can execute a perfectly
good stored proc, yet retrieve the data inefficiently. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com .. "EradicusMax" <NoM***@myhouse.com> wrote in message I have a stored proc that can take 30 minutes to run. In QA it takes only 30news:%23lURmSEpFHA.1480@TK2MSFTNGP10.phx.gbl... seconds. Once complete, however, the execution plans are essentially the same. My other similar SP's don't have the issue and I've dropped and recreated the SP just in case. Any reason a SP would be slower than QA? Sorry, I when I run the Stored Proc in QA it can take 30 min to run, but if
I take the code in the SP and run it in QA, it runs in 30 sec. That's what I meant, not that a client app it taking that long to execute the SP. Show quote "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:OY5wkWEpFHA.3036@TK2MSFTNGP14.phx.gbl... > As always, it depends. Poorly-written client code can execute a perfectly > good stored proc, yet retrieve the data inefficiently. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada > www.pinpub.com > . > "EradicusMax" <NoM***@myhouse.com> wrote in message > news:%23lURmSEpFHA.1480@TK2MSFTNGP10.phx.gbl... > I have a stored proc that can take 30 minutes to run. In QA it takes only > 30 > seconds. Once complete, however, the execution plans are essentially the > same. My other similar SP's don't have the issue and I've dropped and > recreated the SP just in case. Any reason a SP would be slower than QA? > > Google "parameter sniffing".
-- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com .. "EradicusMax" <NoM***@myhouse.com> wrote in message Sorry, I when I run the Stored Proc in QA it can take 30 min to run, but ifnews:OqGlNaEpFHA.2444@tk2msftngp13.phx.gbl... I take the code in the SP and run it in QA, it runs in 30 sec. That's what I meant, not that a client app it taking that long to execute the SP. Show quote "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:OY5wkWEpFHA.3036@TK2MSFTNGP14.phx.gbl... > As always, it depends. Poorly-written client code can execute a perfectly > good stored proc, yet retrieve the data inefficiently. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada > www.pinpub.com > . > "EradicusMax" <NoM***@myhouse.com> wrote in message > news:%23lURmSEpFHA.1480@TK2MSFTNGP10.phx.gbl... > I have a stored proc that can take 30 minutes to run. In QA it takes only > 30 > seconds. Once complete, however, the execution plans are essentially the > same. My other similar SP's don't have the issue and I've dropped and > recreated the SP just in case. Any reason a SP would be slower than QA? > > Thanks a bunch Tom, I had no idea parameters caused that much trouble,
everything runs in short order now. Show quote "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:ux$e7fEpFHA.3512@TK2MSFTNGP15.phx.gbl... > Google "parameter sniffing". > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada > www.pinpub.com > . > "EradicusMax" <NoM***@myhouse.com> wrote in message > news:OqGlNaEpFHA.2444@tk2msftngp13.phx.gbl... > Sorry, I when I run the Stored Proc in QA it can take 30 min to run, but > if > I take the code in the SP and run it in QA, it runs in 30 sec. That's what > I > meant, not that a client app it taking that long to execute the SP. > > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message > news:OY5wkWEpFHA.3036@TK2MSFTNGP14.phx.gbl... >> As always, it depends. Poorly-written client code can execute a >> perfectly >> good stored proc, yet retrieve the data inefficiently. >> >> -- >> Tom >> >> ---------------------------------------------------- >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >> SQL Server MVP >> Columnist, SQL Server Professional >> Toronto, ON Canada >> www.pinpub.com >> . >> "EradicusMax" <NoM***@myhouse.com> wrote in message >> news:%23lURmSEpFHA.1480@TK2MSFTNGP10.phx.gbl... >> I have a stored proc that can take 30 minutes to run. In QA it takes only >> 30 >> seconds. Once complete, however, the execution plans are essentially the >> same. My other similar SP's don't have the issue and I've dropped and >> recreated the SP just in case. Any reason a SP would be slower than QA? >> >> > > Make sure you have SET NOCOUNT ON as well. While it obviously wasn't the
main issues here it can make a big difference if you are processing that many rows. -- Show quoteAndrew J. Kelly SQL MVP "EradicusMax" <NoM***@myhouse.com> wrote in message news:%23rFQy8EpFHA.2952@TK2MSFTNGP15.phx.gbl... > Thanks a bunch Tom, I had no idea parameters caused that much trouble, > everything runs in short order now. > > > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message > news:ux$e7fEpFHA.3512@TK2MSFTNGP15.phx.gbl... >> Google "parameter sniffing". >> >> -- >> Tom >> >> ---------------------------------------------------- >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >> SQL Server MVP >> Columnist, SQL Server Professional >> Toronto, ON Canada >> www.pinpub.com >> . >> "EradicusMax" <NoM***@myhouse.com> wrote in message >> news:OqGlNaEpFHA.2444@tk2msftngp13.phx.gbl... >> Sorry, I when I run the Stored Proc in QA it can take 30 min to run, but >> if >> I take the code in the SP and run it in QA, it runs in 30 sec. That's >> what I >> meant, not that a client app it taking that long to execute the SP. >> >> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message >> news:OY5wkWEpFHA.3036@TK2MSFTNGP14.phx.gbl... >>> As always, it depends. Poorly-written client code can execute a >>> perfectly >>> good stored proc, yet retrieve the data inefficiently. >>> >>> -- >>> Tom >>> >>> ---------------------------------------------------- >>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >>> SQL Server MVP >>> Columnist, SQL Server Professional >>> Toronto, ON Canada >>> www.pinpub.com >>> . >>> "EradicusMax" <NoM***@myhouse.com> wrote in message >>> news:%23lURmSEpFHA.1480@TK2MSFTNGP10.phx.gbl... >>> I have a stored proc that can take 30 minutes to run. In QA it takes >>> only >>> 30 >>> seconds. Once complete, however, the execution plans are essentially the >>> same. My other similar SP's don't have the issue and I've dropped and >>> recreated the SP just in case. Any reason a SP would be slower than QA? >>> >>> >> >> > > EradicusMax wrote:
> I have a stored proc that can take 30 minutes to run. In QA it takes Run both from QA in the same batch with Show Execution Plan option > only 30 seconds. Once complete, however, the execution plans are > essentially the same. My other similar SP's don't have the issue and > I've dropped and recreated the SP just in case. Any reason a SP would > be slower than QA? turned on and compare them. Make sure they are exactly the same. If not, post the details of what changed. Are they running identical code?  |
|||||||||||||||||||||||
Other interesting topics