Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Very slow query (over 100 sec.) runs in no time with temp tables: why? Expand / Collapse
Author
Message
Posted Wednesday, April 6, 2011 11:43 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
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)



  Post Attachments 
Derived Tables.sqlplan (11 views, 15.77 KB)
With Temp Tables.sqlplan (5 views, 31.79 KB)
Post #1089463
Posted Wednesday, April 6, 2011 12:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:51 PM
Points: 6,158, Visits: 7,222
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
Post #1089492
Posted Wednesday, April 6, 2011 1:12 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
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?

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


  Post Attachments 
Actual Execution Plans.zip (11 views, 8.27 KB)
Post #1089533
Posted Wednesday, April 6, 2011 1:23 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1089541
Posted Wednesday, April 6, 2011 2:02 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
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
Post #1089568
Posted Wednesday, April 6, 2011 2:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:51 PM
Points: 6,158, Visits: 7,222
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?

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
Post #1089578
Posted Wednesday, April 6, 2011 2:47 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1089596
Posted Wednesday, April 6, 2011 5:18 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
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


  Post Attachments 
CREATE TABLE Scripts.zip (1 view, 3.56 KB)
Post #1089640
Posted Thursday, April 7, 2011 7:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1089864
Posted Thursday, April 7, 2011 9:08 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
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


  Post Attachments 
Actual_Derived Tables_WHERE Clauses Reversed.sqlplan (12 views, 20.69 KB)
Post #1089981
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse