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