Query performs poorly unless a temp table is used

1
votes

The following query takes about 1 minute to run, and has the following IO statistics:

SELECT T.RGN, T.CD, T.FUND_CD, T.TRDT, SUM(T2.UNITS) AS TotalUnits
FROM dbo.TRANS AS T
JOIN dbo.TRANS AS T2 ON T2.RGN=T.RGN AND T2.CD=T.CD AND T2.FUND_CD=T.FUND_CD AND T2.TRDT<=T.TRDT
JOIN TASK_REQUESTS AS T3 ON T3.CD=T.CD AND T3.RGN=T.RGN AND T3.TASK = 'UPDATE_MEM_BAL'
GROUP BY T.RGN, T.CD, T.FUND_CD, T.TRDT

(4447 row(s) affected) Table 'TRANSACTIONS'. Scan count 5977, logical reads 7527408, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'TASK_REQUESTS'. Scan count 1, logical reads 11, 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 = 58157 ms, elapsed time = 61437 ms.

If I instead introduce a temporary table then the query returns quickly and performs less logical reads:

CREATE TABLE #MyTable(RGN VARCHAR(20) NOT NULL, CD VARCHAR(20) NOT NULL, PRIMARY KEY([RGN],[CD]));
INSERT INTO #MyTable(RGN, CD) SELECT RGN, CD FROM TASK_REQUESTS WHERE TASK='UPDATE_MEM_BAL';

SELECT T.RGN, T.CD, T.FUND_CD, T.TRDT, SUM(T2.UNITS) AS TotalUnits
FROM dbo.TRANS AS T
JOIN dbo.TRANS AS T2 ON T2.RGN=T.RGN AND T2.CD=T.CD AND T2.FUND_CD=T.FUND_CD AND T2.TRDT<=T.TRDT
JOIN #MyTable AS T3 ON T3.CD=T.CD AND T3.RGN=T.RGN
GROUP BY T.RGN, T.CD, T.FUND_CD, T.TRDT

(4447 row(s) affected) Table 'Worktable'. Scan count 5974, logical reads 382339, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'TRANSACTIONS'. Scan count 4, logical reads 4547, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#MyTable____________________________________________________________________________________________________________000000000013'. 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 = 1420 ms, elapsed time = 1515 ms.

The interesting thing for me is that the TASK_REQUEST table is a small table (3 rows at present) and statistics are up to date on the table. Any idea why such different execution plans and execution times would be occuring? And ideally how to change things so that I don't need to use the temp table to get decent performance?

The only real difference in the execution plans is that the temp table version introduces an index spool (eager spool) operation.

3
sqlsql-servertsqlsql-server-2008
Are you clearing the buffer cache before running each query to ensure a fair comparison? – Joe Stefanelli
I've run the comparissions with both clean procedure cache and clean buffers, and also with warm cache and buffers. Makes no difference. – Paul McLoughlin
What indexes do you have on TRANS and TASK_REQUESTS? Does the primary key you're creating on #myTable match what's in TASK_REQUESTS? – Joe Stefanelli
For the temp table I've tried versions with no primary key, with a primary key, all makes no difference. There is a difference between the TASK_REQUESTS table and this temp table though: TASK_REQUESTS has an identity column as the primary key. However, there are only 3 rows in the table so I'd be surprised if this made any difference. – Paul McLoughlin

3 Answers

1
votes

You're doing a string comparison for every row. The temp table version discards that comparison. String compares are not particularly quick, and would be the first thing I'd look at as a source of additional computational cost.

0
votes

This is just curiosity, I have no particular reason to believe it will be faster but have you tried:

SELECT T.RGN, T.CD, T.FUND_CD, T.TRDT, SUM(T2.UNITS) AS TotalUnits 
FROM dbo.TRANS AS T 
JOIN dbo.TRANS AS T2 ON T2.RGN=T.RGN AND T2.CD=T.CD AND T2.FUND_CD=T.FUND_CD AND T2.TRDT<=T.TRDT 
JOIN TASK_REQUESTS AS T3 ON T3.CD=T.CD AND T3.RGN=T.RGN     
WHERE T3.TASK = 'UPDATE_MEM_BAL' 
GROUP BY T.RGN, T.CD, T.FUND_CD, T.TRDT
0
votes
;WITH MyTable AS
( 
    SELECT RGN, CD FROM TASK_REQUESTS WHERE TASK = 'UPDATE_MEM_BAL'
)
SELECT t.RGN, t.CD, t.FUND_CD, t.TRDT, SUM(t2.UNITS) [TotalUnits]
FROM dbo.TRANS t
JOIN dbo.TRANS t2 ON (t2.RGN = t.RGN 
                  AND t2.CD = t.CD 
                  AND t2.FUND_CD = t.FUND_CD 
                  AND t2.TRDT <= t.TRDT)
JOIN MyTable t3 ON (t3.CD = t.CD AND t3.RGN = t.RGN)
GROUP BY t.RGN, t.CD, t.FUND_CD, t.TRDT