December 6, 2011 at 1:10 pm
Can someone help explain why these two queries have such different performance characteristics?
The top query takes 5 seconds to return, the bottom one takes almost no time. It doesn't matter how many times I run the query.
DECLARE @item VARCHAR(50),
@location VARCHAR(25)
SET @item = '19390'
SET @location = 'TEST'
SELECT
*
FROM
vw_test_item_locations
WHERE
item = @item AND
location = @location
SELECT
*
FROM
vw_test_item_locations
WHERE
item = '19390' AND
location = 'TEST'
The view is defined:
CREATE VIEW [vw_test_item_locations] AS
SELECT
item_locations.item,
item_locations.location,
CONVERT(BIT,
(SELECT
COUNT(1)
FROM
item_balances
WHERE
item = item_locations.item AND
location = item_locations.location AND
on_hand_quantity <> 0
) +
(SELECT
COUNT(1)
FROM
vw_item_location_bom_reverse
WHERE
item = item_locations.item AND
location = item_locations.location AND
has_on_hand_balances = 1
)
) has_on_hand_balances
FROM
item_locations
GO
December 6, 2011 at 1:48 pm
When posting performance problems it helps those who want to help you if you follow the instructions in this article
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 6, 2011 at 1:54 pm
Thank you, I will attach the missing information shortly.
December 6, 2011 at 1:55 pm
With the limited info you provided, I can say it's likely that the peformance gain you are noticing is due sql server caching the execution plan and reuses it on subsequent executions. This means the database engine doesnt spend time compiling execution plans every time a similar query is run.
The differences between the two queries is called Parameterization. You can find more info by researching that topic.
December 6, 2011 at 1:56 pm
NJ-DBA (12/6/2011)
With the limited info you provided, I can say it's likely that the peformance gain you are noticing is due sql server caching the execution plan and reuses it on subsequent executions. This means the database engine doesnt spend time compiling execution plans every time a similar query is run.The differences between the two queries is called Parameterization. You can find more info by researching that topic.
I reread your original post and take all of this back... I will leave it here to be mocked and ridiculed as it deserves.
December 6, 2011 at 2:04 pm
Can you post the execution plans for both queries?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 6, 2011 at 2:08 pm
NJ-DBA (12/6/2011)
NJ-DBA (12/6/2011)
With the limited info you provided, I can say it's likely that the peformance gain you are noticing is due sql server caching the execution plan and reuses it on subsequent executions. This means the database engine doesnt spend time compiling execution plans every time a similar query is run.The differences between the two queries is called Parameterization. You can find more info by researching that topic.
I reread your original post and take all of this back... I will leave it here to be mocked and ridiculed as it deserves.
It probably does have to do with parameterization though, parameter sniffing specifically. I wouldn't be too surprised if the parameterized version was originally run with different parameter values that were "sniffed" and used to compile a plan that is sub-optimal when executed with the current set of values. The second query, not using parameters, triggered a fresh compilation, and the optimizer generated a execution plan optimized to the current set of values. Forcing the parameterized version to recompile with the current parameter values might clear up the issue.
EDIT: Looking at the execution plans as MyDoggieJessie suggested above should help determine in short order if this is what's happening.
December 6, 2011 at 2:17 pm
Here are the row counts for the tables:
item_balances = 7
item_bill_of_materials = 51,855
item_locations = 17,866
items = 22,320
Attached are the scripts to create the tables and views used in the two queries I posted.
In the attachment are the execution plans as well.
I know the performance is to do with the section of the has_on_hand_balances part of the view.
When the parameterized query runs it seems like it must query all vw_item_location_bom_reverse and filters the results by item / location after all of the results have been processed. When the non-parameterized query runs it seems that it is able to only run the vw_item_location_bom_reverse on the item / location being queried. This seems odd to me.
Thank you for your help in understanding the differences.
December 6, 2011 at 2:26 pm
It probably does have to do with parameterization though, parameter sniffing specifically. I wouldn't be too surprised if the parameterized version was originally run with different parameter values that were "sniffed" and used to compile a plan that is sub-optimal when executed with the current set of values. The second query, not using parameters, triggered a fresh compilation, and the optimizer generated a execution plan optimized to the current set of values. Forcing the parameterized version to recompile with the current parameter values might clear up the issue.
I restarted SQL Server and then re-ran the query multiple times only with the same item / location never swithing any of the values and get the same results. The parameterized one takes 5 seconds and the non-parameterized one takes miliseconds.
December 6, 2011 at 4:54 pm
mpaulus-1000078 (12/6/2011)
I restarted SQL Server and then re-ran the query multiple times only with the same item / location never swithing any of the values and get the same results. The parameterized one takes 5 seconds and the non-parameterized one takes miliseconds.
Well that's odd. It definitely appears that in the slow query, the one view is not getting the search predicates applied to it, it's returning all rows and then filtering, as you mentioned; but why that happens for the first query and not the second is a bit of a puzzle, especially if you bounced SQL before running. (Side note: DBCC FREEPROCCACHE will clear out the procedure cache without having to bounce SQL). I suspect the views can be modified to perform better (haven't looked at them too closely yet), but that doesn't necessarily explain the different query plans.
Do you have forced parameterization turned on for this database?
December 6, 2011 at 5:14 pm
I do not have forced parameter turned on. Thank you for your insight. I'm at a loss as well, that is why I posted. The SQL Server is a dev machine and I couldn't remember the command, but thanks for posting what the command is to reset the cache. If anyone can explain why this is happening that would be wonderful. I'm going to try and rewrite the query to hopefully get better results, but I like to understand why something doesn't work the way I would expect it to. Thanks in advance for the time you take to contribute to the community.
December 6, 2011 at 5:53 pm
mpaulus-1000078 (12/6/2011)
I do not have forced parameter turned on. Thank you for your insight. I'm at a loss as well, that is why I posted. The SQL Server is a dev machine and I couldn't remember the command, but thanks for posting what the command is to reset the cache. If anyone can explain why this is happening that would be wonderful. I'm going to try and rewrite the query to hopefully get better results, but I like to understand why something doesn't work the way I would expect it to. Thanks in advance for the time you take to contribute to the community.
Would be interest to know if updating stats has any affect
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply