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


Very slow query (over 100 sec.) runs in no time with temp tables: why?


Very slow query (over 100 sec.) runs in no time with temp tables: why?

Author
Message
Rich Mechaber
Rich Mechaber
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1555 Visits: 3665
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)


Attachments
Derived Tables.sqlplan (20 views, 15.00 KB)
With Temp Tables.sqlplan (14 views, 31.00 KB)
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8613 Visits: 7660
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 Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Rich Mechaber
Rich Mechaber
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1555 Visits: 3665
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
Attachments
Actual Execution Plans.zip (21 views, 8.00 KB)
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23583 Visits: 9730
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
Rich Mechaber
Rich Mechaber
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1555 Visits: 3665
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
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8613 Visits: 7660
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?


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23583 Visits: 9730
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
Rich Mechaber
Rich Mechaber
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1555 Visits: 3665
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
Attachments
CREATE TABLE Scripts.zip (10 views, 3.00 KB)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86528 Visits: 41098
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Rich Mechaber
Rich Mechaber
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1555 Visits: 3665
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
Attachments
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