exec of SP vs. exec of lines
in [Programming SQL]
|
Prev: Best table settings for excessive insert and delete operations with small number of records
Next: SCOPE_IDENTITY issue with INSTEAD_OF trigger
From: Kyle Jedrusiak on 23 Jul 2008 10:43 This is very weird to me. I have an SP that I wrote that seems to be taking a long time to run. So I did a test using Query Analyzer. If I take out all the lines of the SP and run them in QA it takes about 21 seconds to complete the task. If I exec the SP it's running for long enough to time out. What's going on? Kyle
From: sloan on 23 Jul 2008 11:00 Google "parameter sniffing" as one option. ......... "Kyle Jedrusiak" <kjedrusiak(a)princetoninformation.com> wrote in message news:OlACoLN7IHA.1428(a)TK2MSFTNGP06.phx.gbl... > This is very weird to me. I have an SP that I wrote that seems to be > taking a long time to run. > > So I did a test using Query Analyzer. > > If I take out all the lines of the SP and run them in QA it takes about 21 > seconds to complete the task. > > If I exec the SP it's running for long enough to time out. > > What's going on? > > Kyle >
From: Plamen Ratchev on 23 Jul 2008 11:06 Do you have any parameters in the SP? It could be the plan is optimized for parameters that differ from those you are running now. Try to execute the stored procedure using WITH RECOMPILE to see if it makes a difference. HTH, Plamen Ratchev http://www.SQLStudio.com
From: William Vaughn (MVP) on 23 Jul 2008 12:46
Sloan and Plamen are on the right track. Consider that a SP is compiled and its query plan is cached the FIRST time its used. This plan is based on the settings of the parameters passed to it. For any number of reasons, the plan might not be suitable for subsequent executions. That is, the next time the SP is invoked, it uses the cached plan (if it's still in memory). Think of the query plan like this: suppose you want to go to the dentist. It's Friday so you decide to take the bridge into town and the trip takes about 20 minutes. However, the next day your tooth starts to hurt so you get an appointment for a follow-up visit that afternoon. You remember the route you took last time only took 20 minutes so you try it again. However, this time the bridge is closed for maintenance and you get stuck in traffic and have to drive around. The trip now takes 2 hours. Query plans work the same way but don't cause physical pain when they take longer. The real solution is to evaluate the SP to see if there are parameters that trigger different query plans--those that are so different that they really should be executed as different SPs. Consider how often you pass the parameter(s) that make the plan unsuitable. Perhaps you could re-write the application to prevent that parameter value or call a custom SP that can deal better with that parameter choice. Consider simplifying the SP and breaking it up into more discrete operations and have a "driver" SP call the appropriate plan based on incoming parameters. I discuss this issue in detail in the book. -- __________________________________________________________________________ William R. Vaughn President and Founder Beta V Corporation Author, Mentor, Dad, Grandpa Microsoft MVP (425) 556-9205 (Pacific time) Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) ____________________________________________________________________________________________ "Kyle Jedrusiak" <kjedrusiak(a)princetoninformation.com> wrote in message news:OlACoLN7IHA.1428(a)TK2MSFTNGP06.phx.gbl... > This is very weird to me. I have an SP that I wrote that seems to be > taking a long time to run. > > So I did a test using Query Analyzer. > > If I take out all the lines of the SP and run them in QA it takes about 21 > seconds to complete the task. > > If I exec the SP it's running for long enough to time out. > > What's going on? > > Kyle > |