Process stalls on tiny CTE

Question

PRINT '-------------------------------TEMP--------------------------------------------'

SELECT DISTINCT ZipCode 
INTO #ZipCodes
FROM tb_PROD_ADDRESS_RANGE par
WHERE par.DeletedFlag = 0

SELECT
      FileStatusId          
     ,FileStatusReasonId    
    FROM tb_RECORD_IMPORT as import
    LEFT JOIN #ZipCodes AS pr ON import.ZIP_CODE = pr.ZipCode

PRINT '-------------------------------CTE--------------------------------------------'

;WITH ZipCodes AS (
    SELECT DISTINCT ZipCode 
    FROM tb_PROD_ADDRESS_RANGE par
    WHERE par.DeletedFlag = 0
)
SELECT 
        FileStatusId        
        ,FileStatusReasonId 
    FROM tb_RECORD_IMPORT as import
    LEFT JOIN ZipCodes AS pr ON import.ZIP_CODE = pr.ZipCode

ABOVE is the actual sql from the actual procedure.

I've got a proc that is lagging in all environments, and it's lagging on a CTE, and the process runs better with a temp table.

It's part of a larger proc, but it lags on this spot, regardless where it may be moved in the process.

What I don't understand is why this would be the case. The target table isn't THAT big and the CTE equates to 910 records.

When I look at the impact of this chunk of code, the CTE seems like it should perform better. But when running as part of the bigger package/procedure, it's the spot where things grind to a stand still.

I don't think the problem is the CTE but something else in the internal guts. I've had people suggest parameter sniffing, but there are no parameters!.

Statistics are up-to-date. Are there problems with SSIS hanging on to memory or something that would be impacting this?

***********************************EPILOGUE********************************************************* Work took over, and since this problem had been 'fixed' and bigger fires were burning there was a delay...

Before getting the answer about the best method to share the xml for the execution plan, I had gotten as far as generating the execution plan as recommended by the good people in the comments...

and that had the answer although I'm still puzzled why the difference is reverse of the test below, but that's why it's important to test sql code within the context of being used...

The execution plan for the temp table solution is a typical plan with some seeks and parallelization happening. many objects, not sure what everything means yet. There is even a suggested index.

However, The CTE solution plan is very easy to grasp. It is one action, 100%. A full table scan.

I suspect the real problem isn't even the full table scan in an of itself, but the fact that this chunk of code is sandwiched around a lot of other code that is tying up resources.

-------------------------------TEMP--------------------------------------------
 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 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 'tb_PROD_ADDRESS_RANGE'. Scan count 1, logical reads 4215, 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 = 141 ms,  elapsed time = 143 ms.

(910 row(s) affected)

(206949 row(s) affected)
Table '#ZipCodes___________________________________________________________________________________________________________00000000017C'. Scan count 17, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tb_RECORD_IMPORT'. Scan count 17, logical reads 22157, 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 = 1822 ms,  elapsed time = 1378 ms.
-------------------------------CTE--------------------------------------------

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(206949 row(s) affected)
Table 'tb_RECORD_IMPORT'. Scan count 1, logical reads 20027, physical reads 0, read-ahead reads 202, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 140 ms,  elapsed time = 1121 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

sorry, forgot to post that in... added the sql to the top. overflow doesn't seem to like parsing the PRINT command. :( — discosammy, Oct 03 '14 at 18:46
please post the *actual* query plan generated by the server to a service like dropbox, gist.github, box.com, or some other commonly-known file sharing location (*not* some warez-hosting place). — swasheck, Oct 03 '14 at 19:31
... also, please note that the CTE is not a full intermediate materialization (see Martin's comment below). it is evaluated at query execution time and would look similar to a subquery. — swasheck, Oct 03 '14 at 19:40
I'm confused. According to what's printed, the CPU time and the elapsed time are *bigger* for the temp table. The CTE seems to win on both accounts. Am I misreading something? — Gordon Linoff, Oct 03 '14 at 20:52
Gordon, that is EXACTLY what inspired this post. The CTE seems to perform great on an island, but once it's incorporated into the proc, things grind to a halt. — discosammy, Oct 03 '14 at 21:44

score 0 · Accepted Answer · answered Oct 10 '14 at 22:23

***********************************EPILOGUE********************************************************* Work took over, and since this problem had been 'fixed' and bigger fires were burning there was a delay...

Before getting the answer about the best method to share the xml for the execution plan, I had gotten as far as generating the execution plan as recommended by the good people in the comments...

and that had the answer although I'm still puzzled why the difference is reverse of the test below, but that's why it's important to test sql code within the context of being used...

The execution plan for the temp table solution is a typical plan with some seeks and parallelization happening. many objects, not sure what everything means yet. There is even a suggested index.

However, The CTE solution plan is very easy to grasp. It is one action, 100%. A full table scan.

I suspect the real problem isn't even the full table scan in an of itself, but the fact that this chunk of code is sandwiched around a lot of other code that is tying up resources.

score -4 · Answer 2 · edited May 23 '17 at 11:49

-4

CTE's perform significantly slower and it is better to always use temp tables. for more information on this, refer to previously asked

Which are more performant, CTE or temporary tables?

edited May 23 '17 at 11:49

Community

1
1

answered Oct 03 '14 at 19:00

Kris

9
1

Do you see the stats above? They seem to show the CTE performs better. How do you explain this discrepancy then? Plus, the situation where I'm using this, by your link, is a good place to use CTE as the record set is small. – discosammy Oct 03 '14 at 19:11
9

This isn't the case as a general statement. Sometimes intermediate materialization helps. Other times it would hinder. – Martin Smith Oct 03 '14 at 19:15
I'm talking evidence. There are the same number of rows in the table inside and outside of the procedure. What phenomenon is changing the way the CTE reacts when being applied in a procedure? the temp table shows an immediate impact, so i don't see how that could be attributed to a cache. – discosammy Oct 03 '14 at 19:23
Wow you really drew the wrong conclusion from the accepted answer that you posted. – Zane Oct 03 '14 at 19:28
2

@MartinSmith are you saying that the temp table is an intermediate materialization which could either help or hinder the process? i get the sense that the question asker believes that a CTE is an IM. – swasheck Oct 03 '14 at 19:32
@swasheck. Yes I was a bit lazy as posting from mobile. What I meant to say was that "sometimes intermediate materialization into a temp table helps". Sometimes a CTE can also be materialised into a spool which might help if the issue is avoiding repeated re evaluation of a query. But even then it won't get all possible benefits of explicit materialisation such as statistics and recompiles. – Martin Smith Oct 03 '14 at 19:37
@MartinSmith thanks. i was just a bit confused and wanted to ensure i could figure out what you were saying for the sake of my own education – swasheck Oct 03 '14 at 19:41
This conversation has introduced me to the concept of immediate materialization. I'm not arguing that the CTE is the best choice. What I don't understand is how it is such an awful choice. The CTE is small (910 records) against a table of 200k. I've used CTE quite frequently in other environments on far larger record sets which is why I'm having a trouble just swallowing "temp tables are better". – discosammy Oct 03 '14 at 19:56
3

@discosammy *please* post the query plans. that will help us much more than trying to discuss things in a vacuum. – swasheck Oct 03 '14 at 20:01
the actual query plan XML? how would I share? via xml? – discosammy Oct 03 '14 at 21:25
Via pastebin or dropbox. Post the actual xmls there and share the link. – Marian Oct 04 '14 at 08:25
Ping @discosammy – what Marian said. – Andriy M Oct 04 '14 at 09:24

Process stalls on tiny CTE

2 Answers2