September 24, 2007 at 12:25 pm
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
September 24, 2007 at 12:37 pm
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.
😎
September 24, 2007 at 1:05 pm
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.
😎
September 26, 2007 at 9:55 am
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
September 29, 2015 at 3:56 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply