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 slower then query Expand / Collapse
Author
Message
Posted Monday, September 24, 2007 12:25 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 09, 2007 10:12 AM
Points: 818, Visits: 10
This is an odd one I just ran into today.  I rebuilt an older query that aggregated together a bunch of information.  The query had slowed down to the point that it was regularly timing out. 

The primary table that is being accessed is about 80k records so it's not a huge data set. 

To optimize the query I removed a table variable that worked similar to this (with more column)

select Type, OrigionalLogID, (select top 1 b.field from table b where b.type=a.type and b.OrigionalLogID=a.OrigionalLogID and TranType in ('these', 'change') order by b.field desc), .... into @table from table a where .... order by OrigionalLogID

I built a view that used min/max(case ...) to build the same data.  Doing that dropped a considerable amount of time off of the execution time of the query and simplified things considerably.

I then rebuilt fields that were selected as (select) as fieldname into count(case ...) statements to speed things even further.  I managed to drop the query that ran for 60 seconds for a month of data to run in about 2 seconds for a month, and about 7 seconds for a full history.

I then took the query I had built and copied it into a stored procedure created the procedure and executed with the same parameters I was using for test and the stored procedure takes over 2 min to execute (it generates the exact same execution plan)

Does anyone have any idea what could be breaking or how I could fix it?

thanks,

  Kevin

Post #402194
Posted Monday, September 24, 2007 12:37 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:53 PM
Points: 22,511, Visits: 30,236
Without seeing the code for the stored procedure, it is a guess.  It could be parameter sniffing at work.  Are you using the variables declared in the header of the sproc in the query?  If so, declare local variables inside the procedure, set those variables to the values you are sending in, and use the local variables in your query.




Lynn Pettis



For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #402198
Posted Monday, September 24, 2007 1:05 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 09, 2007 10:12 AM
Points: 818, Visits: 10
THANK YOU!!!!!! I have been fighting with this stored procedure for about 3 days now.  I was sure this was going to kill the last bit of motivation I still had left to optimize this beast.

I had 4 parameters going in to the Stored procedure.  In the query I had declared and set them at the top so I could easy copy/paste when I finished.

I prefixed all the variables coming in to the stored procedure with an o declared the variables in the stored procedure and set them equal to the o prefixed variables and the stored procedure ran as expected.

again THANK YOU. I'll have to try to remember this in case I ever run into it again.  For now I'm off to fix the o variables to make sense.

Lynn Pettis (9/24/2007)
Without seeing the code for the stored procedure, it is a guess.  It could be parameter sniffing at work.  Are you using the variables declared in the header of the sproc in the query?  If so, declare local variables inside the procedure, set those variables to the values you are sending in, and use the local variables in your query.

Post #402201
Posted Wednesday, September 26, 2007 9:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 41,531, Visits: 34,448
Try also leaving the parameters and marking the proc for recompile every time it runs. (CREATE PROCEDURE ... WITH RECOMPILE)

Using variables, the optimiser can not make any conclusions about the number of rows that will be affected by any query. I believe it will make a guess at about 1/3 of the table. Hence, you're unlikely to get very good performance but you are guarenteed to never get very bad performance. Essentially, you'll get average performance

Using recompile, you'll get the best possible plan for each run of the procedure at a cost of a slight CPU overhead for the compile.

Give it a try and see what's best for you.

I will recommend that you don't switch parameters for variables unless you know you are having this kind of problem with a particular proc. It's called parameter sniffing and, in general, it's a good thing.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #403102
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse