October 29, 2008 at 6:33 am
I came across a strange problem in which two nearly identical queries behave very differently. Can anyone shed some light on why this is?
Note that vwQXD_SODetails joins tblQXD_SODetails to tblQuests, and returns tblQuests.QuestBatchGUID as q__QuestBatchGUID.
There is an index on tblQuests.QuestBatchGUID. There are approx. 3500 rows in vwQXD_SODetails, 1400 rows in vwQXD_ItemElements, and 240,000 rows in tblQuests.
Version 1:
--This version works very well: returns in a second or two
SELECT
qxd.q__QuestID,
qxd_r.q__QuestID
FROM
vwQXD_SODetails qxd
JOIN vwQXD_ItemElements qxd_r ON
qxd_r.ItemNumber LIKE qxd.ItemNumber
JOIN tblQuests q ON
qxd.QuestID = q.QuestID
WHERE
q.QuestBatchGUID ='83EA67D1-3D68-48C8-8E27-C4FB1F372037'
Version 2:
--This version never returns (at least not within 5+ minutes)
--The join to tblQuests is not needed, but left in to show that
--the query is virtually identical to version 1. Removing this
--join has no effect.
SELECT
qxd.q__QuestID,
qxd_r.q__QuestID
FROM
vwQXD_SODetails qxd
JOIN vwQXD_ItemElements qxd_r ON
qxd_r.ItemNumber LIKE qxd.ItemNumber
JOIN tblQuests q ON
qxd.QuestID = q.QuestID
WHERE
qxd.q__QuestBatchGUID = '83EA67D1-3D68-48C8-8E27-C4FB1F372037'
October 29, 2008 at 6:44 am
The first query is eliminating all rows based on the table BEFORE running the view so there will be fewer rows touched because the filter is on the TABLE. The second query has to, I believe, materialize the view BEFORE it applies the filter so more rows are touched.
Check the execution plans, estimated if the 2nd query never ends.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 29, 2008 at 7:24 am
Yeah, post the actual execution plans, I think that's where you'll see the problem displayed clearly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 29, 2008 at 12:21 pm
And please post the view definition also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply