April 6, 2011 at 11:43 am
I have a query of 3 tables that takes nearly 2 minutes to complete if I use derived tables in a WHERE clause to filter the rows returned. The same query runs in milliseconds if I instead first dump the restricted conditions to 2 temp tables and then JOIN to those temp tables in the main query.
I'm posting some dummy code to reproduce the query.
Background/Goal. 3 tables of college courses: (1) TermCalendar containing terms, (2) SROffer containing "offerings", aka courses offered in each term, and (3) CrsEvalResponse with the student responses to end-of-semester course evaluations. Goal is to find all offerings where no student completed evaluations, but limited to those terms where at least one offering had some completed evaluations. (If all offerings in a term had no evaluation answers whatsoever, then we didn't perform evaluations that term and I'm not interested in those.)
Both queries run fine on the dummy data b/c one table in production has 125,000 rows, but the dummy data only has a few rows. So, I'm attaching 2 *.sqlplan files generated for estimated execution plans using the production data: [Derived Tables.sqlplan] and [With Temp Tables.sqlplan].
I understand that an index scan for a table this big is not good, but both versions of the query scan the index for CrsEvalResponse. In fact, the fast running version (dumping data to temp tables) scans it twice, once for each of the 2 tables.
Any help would be much appreciated. This is not production code, but I've run into problems like this before, and I'm pretty new to interpreting execution plans.
Code to create tables, generate dummy data, and run the 2 queries follows. Embedded in the comments are the results of TIME and IO stats from the queries run on the production tables, so you can see the stats generated.
Thanks,
Rich
/*
Create and populate 3 tables with test data:
TermCalendar: Term-specific info. for each semester. For testing, just create the ID.
SROffer: Term-specific offerings. If the "same" course is offered in different terms (semesters), those are valid, distinct offerings.
CrsEvalResponse: Student responses to course evaluation questions. For testing, omit the primary tables (students, questions, etc.)
Terms 2 and 3 (but not 1) have offerings with evaluations filled out:
GOAL: Find all offerings in terms 2 and 3 without evaluations (should be offerings 6 and 9)
*/
USE tempdb;
GO
IF OBJECT_ID('dbo.CrsEvalResponse','U') IS NOT NULL
DROP TABLE CrsEvalResponse;
IF OBJECT_ID('dbo.SROffer','U') IS NOT NULL
DROP TABLE SROffer;
IF OBJECT_ID('dbo.TermCalendar','U') IS NOT NULL
DROP TABLE TermCalendar;
GO
--CREATE TABLES AND INDEXES
--============================================================================================
CREATE TABLE TermCalendar
(TermCalendarID INT IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_TermCalendar] PRIMARY KEY CLUSTERED (TermCalendarID ASC)
)
GO
CREATE TABLE SROffer
(SROfferID INT IDENTITY(1,1) NOT NULL,
TermCalendarID INT NOT NULL,
Department VARCHAR(5) NOT NULL,
CourseID VARCHAR(5) NOT NULL,
CourseType VARCHAR(5) NOT NULL,
Section VARCHAR(5) NOT NULL,
CourseName VARCHAR(60) NOT NULL,
CONSTRAINT [PK_SROffer] PRIMARY KEY NONCLUSTERED ([SROfferID] ASC),
CONSTRAINT [IX_SROffer_Constraint] UNIQUE NONCLUSTERED
(
[TermCalendarID] ASC,
[Department] ASC,
[CourseID] ASC,
[CourseType] ASC,
[Section] ASC
),
CONSTRAINT FK_SROffer_TermCalendar FOREIGN KEY (TermCalendarID) REFERENCES TermCalendar (TermCalendarID)
)
GO
CREATE UNIQUE CLUSTERED INDEX [IX_SrofferClustered] ON [dbo].[SROffer]
(
[TermCalendarID] ASC,
[Department] ASC,
[CourseID] ASC,
[CourseType] ASC,
[Section] ASC,
[SROfferID] ASC
)
GO
CREATE TABLE CrsEvalResponse
(EvalRespID INT IDENTITY(1,1) NOT NULL,
SROfferID INT NOT NULL,
StudentID INT NOT NULL,
QuestionID INT NOT NULL,
NumAnswer REAL NOT NULL,
CONSTRAINT PK_CrsEvalResponse PRIMARY KEY CLUSTERED (EvalRespID ASC),
CONSTRAINT FK_CrsEvalResponse_SROffer FOREIGN KEY (SROfferID) REFERENCES SROffer (SROfferID)
)
GO
--INSERT SOME DUMMY DATA
--============================================================================================
--Terms. We'll create 3, but only terms 2 and 3 will have evaluations.
SET IDENTITY_INSERT TermCalendar ON;
INSERT INTO TermCalendar (TermCalendarID) VALUES (1);
INSERT INTO TermCalendar (TermCalendarID) VALUES (2);
INSERT INTO TermCalendar (TermCalendarID) VALUES (3);
SET IDENTITY_INSERT TermCalendar OFF;
--Offerings. We'll add offerings for 3 terms, but only terms 2 and 3 will have evals.
SET IDENTITY_INSERT SROffer ON;
INSERT INTO SROffer
(SROfferID, TermCalendarID, Department, CourseID, CourseType, Section, CourseName)
SELECT 1, 1, 'COMP', '001', 'LECT', '0', 'SQL Server Query Plans for the Clueless'
UNION ALL
SELECT 2, 1, 'BUS', '001', 'LECT', '0', 'The Economics of Pork-chop Chucking'
UNION ALL
SELECT 3, 1, 'BUS', '001', 'LECT', '1', 'The Economics of Pork-chop Chucking'--2 sections this term
UNION ALL
SELECT 4, 2, 'BUS', '001', 'LECT', '0', 'The Economics of Pork-chop Chucking'
UNION ALL
SELECT 5, 2, 'COMP', '001', 'LECT', '0', 'SQL Server Execution Plans for the Clueless'
UNION ALL
SELECT 6, 2, 'COMP', '001', 'LECT', '1', 'SQL Server Execution Plans for the Clueless'--2 sections this term
UNION ALL
SELECT 7, 3, 'LANG', '001', 'LECT', '0', 'Introductory Esperanto'
UNION ALL
SELECT 8, 3, 'LANG', '002', 'LECT', '0', 'Advanced Conversational Esperanto'
UNION ALL
SELECT 9, 3, 'LANG', '002', 'LECT', '1', 'Advanced Conversational Esperanto'
SET IDENTITY_INSERT TermCalendar OFF;
--Evaluations:
INSERT INTO CrsEvalResponse
(SROfferID, StudentID, QuestionID, NumAnswer)
SELECT 4, 123, 1, 2
UNION ALL
SELECT 4, 123, 2, 3
UNION ALL
SELECT 4, 123, 3, 5
UNION ALL
SELECT 5, 123, 1, 2
UNION ALL
SELECT 5, 123, 2, 2
UNION ALL
SELECT 5, 123, 1, 1
UNION ALL
SELECT 7, 123, 1, 2
UNION ALL
SELECT 7, 123, 2, 3
UNION ALL
SELECT 7, 123, 3, 5
UNION ALL
SELECT 8, 123, 1, 2
UNION ALL
SELECT 8, 123, 2, 2
UNION ALL
SELECT 8, 123, 1, 1
--============================================================================================
--LOCATE OFFERINGS WITHOUT ANY COURSE EVALUATIONS, BUT ONLY IN TERMS WHERE 1+ OFFERING _DID_ HAVE EVALUATIONS FILLED OUT
--Should return offerings 6 (Term 2) and 9 (Term 3), but not any offerings from Term 1 (no evaluations)
--============================================================================================
/*
USING SUBQUERIES, VERY SLOW ON PRODUCTION
--============================================================================================
Results of IO and Time stats:
(188 row(s) affected)
Table 'Worktable'. Scan count 1, logical reads 1477176, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CrsEvalResponse'. Scan count 2, logical reads 866, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SROffer'. Scan count 65, logical reads 310, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 98906 ms, elapsed time = 98999 ms.
*/
SELECT TermCalendarID, SROfferID, Department, CourseID, CourseType, Section, CourseName
FROM SROffer
WHERE SROfferID NOT IN (SELECT DISTINCT SROfferID FROM CrsEvalResponse) AND
TermCalendarID IN (SELECT DISTINCT o.TermCalendarID FROM CrsEvalResponse resp INNER JOIN SROffer o ON resp.SROfferID=o.SROfferID)
/*
USING TEMP TABLES, VERY FAST ON PRODUCTION
--============================================================================================
Results of IO and Time stats:
TOTAL EXECUTION TIME: 34 + 41 + 3 = 78 ms
SQL Server parse and compile time:
CPU time = 10 ms, elapsed time = 10 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CrsEvalResponse'. Scan count 1, logical reads 433, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 34 ms.
(412 row(s) affected)
Table 'SROffer'. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CrsEvalResponse'. Scan count 1, logical reads 433, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 41 ms.
(6 row(s) affected)
(112 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SROffer'. Scan count 6, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#tc_________________________________________________________________________________________________________________000000000F57'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#oEval______________________________________________________________________________________________________________000000000F56'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
*/
IF OBJECT_ID('tempdb.dbo.#oEval','U') IS NOT NULL
DROP TABLE #oEval;
IF OBJECT_ID('tempdb.dbo.#tc','U') IS NOT NULL
DROP TABLE #tc;
GO
SELECT DISTINCT SROfferID
INTO #oEval
FROM CrsEvalResponse
SELECT DISTINCT o.TermCalendarID
INTO #tc
FROM CrsEvalResponse resp INNER JOIN
SROffer o ON resp.SROfferID=o.SROfferID
SELECT o.TermCalendarID, o.SROfferID, o.Department, o.CourseID, o.CourseType, o.Section, o.CourseName
FROM SROffer o
WHERE o.SROfferID NOT IN (SELECT SROfferID FROM #oEval) AND
TermCalendarID IN (SELECT TermCalendarID FROM #tc)
April 6, 2011 at 12:22 pm
There's nothing in those estimated plans that pop out and bite me... but they're estimates. My guess is there's a significant difference between estimated and actual. Can you yank the actuals?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 6, 2011 at 1:12 pm
Craig Farrell (4/6/2011)
There's nothing in those estimated plans that pop out and bite me... but they're estimates. My guess is there's a significant difference between estimated and actual. Can you yank the actuals?
Craig, many thanks for a quick reply. I'm uploading 4 new SQL plans (3 for temp tables approach, 1 for derived tables) in a single zip file.
You are onto something: the actual plan for the derived table has an over-fed boa constrictor running from the lazy spool operator to the nested loops join (after scanning CrsEvalResponse index), and it has around 468 million actual rows in it (versus an estimated 479 rows). Could that be a problem?:-D
Is this the cost of the DISTINCT operator over the 125,000 rows that the table contains? It's not enough rows for a cross join to itself (that would be 125,000 squared or around 15-16 billion rows).
That doesn't tell me why that's happening though.... Any ideas?
Thanks very much,
Rich
April 6, 2011 at 1:23 pm
The first thing I'd do is eliminate DISTINCT in the sub-queries. It's completely unnecessary, and it costs CPU cycles at the very least. It is also probably the reason for the poor execution plan, because SQL Server can't estimate statistics and such on that, where it can on the indexes involved. I've seen that problem before.
Second, I'd look into turning the Where Not In and the Where In into joins, if at all possible. SQL Server will probably do that behind the scenes anyway, but it can help sometimes.
Do those, or at least get rid of the distincts, and check if that changes the execution plans and the run-time.
Another thing, beyond the plans, that I'd like to see before going further on performance tuning is the CPU time and I/O stats. Are you familiar with "SET STATISTICS TIME, IO ON" as a debug tool? If not, put that statement at the top of the query, usually right after "SET NOCOUNT ON", and the inverse (just change "ON" to "OFF") at the bottom. Copy-and-paste the messages that generates to this forum, and we can take a look at those too. That often gives even more insight than the execution plan into what needs to be optimized.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 6, 2011 at 2:02 pm
GSquared (4/6/2011)
The first thing I'd do is eliminate DISTINCT in the sub-queries. It's completely unnecessary, and it costs CPU cycles at the very least. It is also probably the reason for the poor execution plan, because SQL Server can't estimate statistics and such on that, where it can on the indexes involved. I've seen that problem before.Second, I'd look into turning the Where Not In and the Where In into joins, if at all possible. SQL Server will probably do that behind the scenes anyway, but it can help sometimes.
Do those, or at least get rid of the distincts, and check if that changes the execution plans and the run-time.
Another thing, beyond the plans, that I'd like to see before going further on performance tuning is the CPU time and I/O stats. Are you familiar with "SET STATISTICS TIME, IO ON" as a debug tool? If not, put that statement at the top of the query, usually right after "SET NOCOUNT ON", and the inverse (just change "ON" to "OFF") at the bottom. Copy-and-paste the messages that generates to this forum, and we can take a look at those too. That often gives even more insight than the execution plan into what needs to be optimized.
Thanks GSquared for your suggestions. I think I've tried all that, but let me take them in order and note what I've tried:
1. Remove DISTINCTS in subqueries: I did try that, and it made no (perceptible) difference in execution time.
2. Reconfigure subqueries as JOINs: I tried that, also without effect:
SELECT DISTINCT o.Department, o.CourseID, o.CourseType, o.Section, o.CourseName
FROM SROffer o INNER JOIN
--Terms where evals. were performed
(
SELECT tc.TermCalendarID
FROM TermCalendar tc INNER JOIN
SROffer o ON tc.TermCalendarID=o.TermCalendarID INNER JOIN
CrsEvalResponse resp ON o.SROfferID=resp.SROfferID
) term ON o.TermCalendarID=term.TermCalendarID LEFT JOIN
--Offerings evaluated (WHERE NULL)
CrsEvalResponse resp ON o.SROfferID=resp.SROfferID
WHERE resp.SROfferID IS NULL;
SQL Server parse and compile time:
CPU time = 78 ms, elapsed time = 84 ms.
(112 row(s) affected)
Table 'CrsEvalResponse'. Scan count 6, logical reads 3440, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TermCalendar'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SROffer'. Scan count 4330, logical reads 11964, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 280028 ms, elapsed time = 238855 ms.
3. TIME and IO stats: I included those in the comments of my originally posted T-SQL, but it was a long bit of code, so easy to have missed it. (I was trying to conform to SSC's best practice for getting help by posting complete testing code.) Here's the stats:
/*
USING SUBQUERIES, VERY SLOW ON PRODUCTION
--============================================================================================
Results of IO and Time stats:
(188 row(s) affected)
Table 'Worktable'. Scan count 1, logical reads 1477176, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CrsEvalResponse'. Scan count 2, logical reads 866, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SROffer'. Scan count 65, logical reads 310, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 98906 ms, elapsed time = 98999 ms.
*/
and
/*
USING TEMP TABLES, VERY FAST ON PRODUCTION
--============================================================================================
Results of IO and Time stats:
TOTAL EXECUTION TIME: 34 + 41 + 3 = 78 ms
SQL Server parse and compile time:
CPU time = 10 ms, elapsed time = 10 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CrsEvalResponse'. Scan count 1, logical reads 433, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 34 ms.
(412 row(s) affected)
Table 'SROffer'. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CrsEvalResponse'. Scan count 1, logical reads 433, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 41 ms.
(6 row(s) affected)
(112 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SROffer'. Scan count 6, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#tc_________________________________________________________________________________________________________________000000000F57'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#oEval______________________________________________________________________________________________________________000000000F56'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
*/
Thanks for looking at this,
Rich
April 6, 2011 at 2:14 pm
rmechaber (4/6/2011)
Craig Farrell (4/6/2011)
There's nothing in those estimated plans that pop out and bite me... but they're estimates. My guess is there's a significant difference between estimated and actual. Can you yank the actuals?Craig, many thanks for a quick reply. I'm uploading 4 new SQL plans (3 for temp tables approach, 1 for derived tables) in a single zip file.
You are onto something: the actual plan for the derived table has an over-fed boa constrictor running from the lazy spool operator to the nested loops join (after scanning CrsEvalResponse index), and it has around 468 million actual rows in it (versus an estimated 479 rows). Could that be a problem?:-D
Is this the cost of the DISTINCT operator over the 125,000 rows that the table contains? It's not enough rows for a cross join to itself (that would be 125,000 squared or around 15-16 billion rows).
That doesn't tell me why that's happening though.... Any ideas?
Thanks very much,
Rich
Heh, I don't want to skip what Gus mentioned, but just from the query plan I see here, that's... ugly. Your nested loop join is just not behaving itself, and it's abusing that tablespool. It's not restricting TempCalendarID in srOffer before it does the monster join. #tc is your prize here. That worktable is killing you and no, I don't know exactly why it thinks it's necessary.
It's not an uncommon result that a divide and conquer can help with execution plans. There's just a certain point where the optimizer gets confused. Think of it like an idiot savant. Oddly enough the XML shows this is a FULL optimization Level. I'd kind of expected a timeout. No missing index complaints. Interesting. Bad statistics?
Have you updated statistics lately and/or rebuilt the indexes?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 6, 2011 at 2:47 pm
I may be missing it, but I don't see all the index definitions that are being used in the execution plans.
IX_CrsEvalResponse
IX_SROffer_CrossList2
Those seem to be missing from the DDL scripts.
And you're right, I missed that you had the stats in there already. The problem is definitely all the scans and reads, which are being caused by that Nested Loops Join. I have an idea that might be relevant, but need to see index definitions before I can move forward on it at all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 6, 2011 at 5:18 pm
GSquared (4/6/2011)
I may be missing it, but I don't see all the index definitions that are being used in the execution plans.
Sorry, GSquared, in my effort to simplify and make dummy test tables, I didn't include all the indexes that the production tables have in them. I'm attaching CREATE TABLE scripts for all three tables, from production. Don't shoot me for some of the constructs or less-than-revealing index names. FK names like "FK_SROffer_Glossary" (it's actually a key to RefundGroup) drive me crazy, but this is a third-party app. and most of it is out of my control.
Does the attachment help?
Thanks to you and Craig both. I'm trying to learn how to make use of execution plans and this seemed like an exquisite place to start: from 100 sec. to 100 ms. is 3 orders of magnitude improvement!
Rich
April 7, 2011 at 7:20 am
What you've done is a technique that I use all of the time. It's called "Divide'n'Conquer". To put it in simple terms, think of a plate juggler... which is more difficult to do and requires the most attention? Keeping 20 plates spinning on the end of sticks or just one. SQL Server works in proverbially the same way.
Keep the Temp Tables and relish the extreme gain in performance.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2011 at 9:08 am
Thanks all for your help. I've fixed the problem and discovered something truly odd (to me anyway). Can someone explain this?
I followed up on Craig's comment "Your nested loop join is just not behaving itself" and looked at the execution plan. Hovering over the Nested Loops, it says the usual "For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows."
Now, if you look at the actual execution plan I posted for the query using derived tables http://www.sqlservercentral.com/Forums/Attachment8568.aspx, you'll see that the top input to the nested loop is the seek for SROfferID, which comes from a small table and returns a small number of rows (3,734). The bottom input to the nested loop is from the index scan of the large table CrsEvalResponse and contains the gargantuan 468 million rows.
"No way," says I to myself. "For each row in top input, scan the bottom input....." Can it be this easy......?
Sure enough. I simply reversed the order of the 2 WHERE clauses and voila! Execution in milliseconds!!
SELECT Department, CourseID, CourseType, Section, CourseName
FROM SROffer
WHERE TermCalendarID IN (SELECT o.TermCalendarID FROM CrsEvalResponse resp INNER JOIN SROffer o ON resp.SROfferID=o.SROfferID) AND
SROfferID NOT IN (SELECT SROfferID FROM CrsEvalResponse)
See new attached sqlplan.
Does this make any sense to anyone? As a couple of you pointed out, divide and conquer works, but all I did was switch the order of the WHERE clauses.
GSquared, I'd still like to hear what you were originally going to suggest.
Thanks again everyone. Your replies kept me digging at this.
Rich
April 7, 2011 at 3:07 pm
rmechaber (4/7/2011)
Thanks all for your help. I've fixed the problem and discovered something truly odd (to me anyway). Can someone explain this?I followed up on Craig's comment "Your nested loop join is just not behaving itself" and looked at the execution plan. Hovering over the Nested Loops, it says the usual "For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows."
Now, if you look at the actual execution plan I posted for the query using derived tables http://www.sqlservercentral.com/Forums/Attachment8568.aspx, you'll see that the top input to the nested loop is the seek for SROfferID, which comes from a small table and returns a small number of rows (3,734). The bottom input to the nested loop is from the index scan of the large table CrsEvalResponse and contains the gargantuan 468 million rows.
"No way," says I to myself. "For each row in top input, scan the bottom input....." Can it be this easy......?
Sure enough. I simply reversed the order of the 2 WHERE clauses and voila! Execution in milliseconds!!
SELECT Department, CourseID, CourseType, Section, CourseNameFROM SROffer
WHERE TermCalendarID IN (SELECT o.TermCalendarID FROM CrsEvalResponse resp INNER JOIN SROffer o ON resp.SROfferID=o.SROfferID) AND
SROfferID NOT IN (SELECT SROfferID FROM CrsEvalResponse)
See new attached sqlplan.
Does this make any sense to anyone? As a couple of you pointed out, divide and conquer works, but all I did was switch the order of the WHERE clauses.
GSquared, I'd still like to hear what you were originally going to suggest.
Thanks again everyone. Your replies kept me digging at this.
Rich
Haven't seen that actually work before without the use of the ol' FORCEORDER option. And, no... I don't use that option.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2011 at 5:15 pm
Jeff Moden (4/7/2011)
Haven't seen that actually work before without the use of the ol' FORCEORDER option. And, no... I don't use that option.
Well, if I've stumped The Mighty Jeff, I suppose I can sit tall at my workstation. 😀
But I'd really like to know why this occurs so I can figure it out next time in less than 2 days...
If nothing else, I guess I've learned that sometimes it's the best (fastest) solution to equip a query with training wheels (i.e., dump some intermediate components to temp tables and use those). Thanks again to Craig, Gus, and Jeff for speedy, thoughtful interest.
Rich
April 8, 2011 at 8:11 am
BWAA-HAAA!!! Maybe it's not a "stump" after all. Thinking back, I've always joined the tables that will return the smallest result set first so maybe I've seen it work before. It's just how I attack problems.
I do know this, though. The order isn't guaranteed or it would always work. When it doesn't, you have three choices... Use the FORCEORDER (I've never done that just because), say "Good enough" and walk away (I almost never do that, either), or use a Temp Table to "Divide'n'Conquer". The reason why I like that last method better than "FORCEORDER" is because it also makes it easier to troubleshoot large queries on piece at a time.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 8, 2011 at 1:37 pm
For years, I have read that SQL Server was not sensitive to the order of arguments in the WHERE clause.
Your results would seem to defy that statement.
Does anyone have any ideas why a reordering of the statements in the WHERE clause made a signficant difference in the elapsed time to execute the query?
LC
April 9, 2011 at 6:04 am
I've also seen instances where the order of the WHERE clause columns makes a difference and also where the order of the columns in a multi column JOIN makes a difference in speed. I've suspected one or more of four things are going on.
1.) The order helps pare the data down faster.
2.) SQL might have an internal "priority" on the indexes. From what I understand about indexes doesn't seem likely.
3.) Statistics out of date - though most times updating them doesn't seem to help.
4.) Blind luck or the SQL Gods are smiling. 🙂
I've never had time to explore the possibilities. It's the problem solved - move to the next one syndrome.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply