SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored procedure slower then query


Stored procedure slower then query

Author
Message
Kevin Brown-242904
Kevin Brown-242904
SSC Eights!
SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)

Group: General Forum Members
Points: 882 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

Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51254 Visits: 38683
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.

Cool

Cool
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)
Kevin Brown-242904
Kevin Brown-242904
SSC Eights!
SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)

Group: General Forum Members
Points: 882 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.

Cool


GilaMonster
GilaMonster
SSC Guru
SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)

Group: General Forum Members
Points: 115348 Visits: 45518
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, MVP, M.Sc (Comp Sci)
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


summer12in
summer12in
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 97
I was just reading about Stored Procedure performance and I came to this blog. I observed that in the below mentioned query -

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 could see your query has co-related sub query , hence the inner query will execute every time for each row in the table. Which makes this query slow.

It would help if the above query is rewritten as -

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search