2

Is there a difference performance wise between these 2 examples:

WITH CTE1
    AS (
      SELECT
        Col1,
        Col2,
        Col3
      FROM dbo.Table1      
      WHERE Col3 = '1'
    )

select * from CTE1


WITH CTE1
    AS (
      SELECT
        Col1,
        Col2,
        Col3
      FROM dbo.Table1      
    )

select * from CTE1
  WHERE Col3 = '1'

We're using a CTE as a subselect within some dynamic sql and can't apply the where clause until the 'select from CTE' happens. Our actual query is much more complicated than this. But, I'm wondering if it will be more performant to not use a CTE. Will the second example actually pull all of the rows from the table and then apply filtering? Or are these functionally equivalent? We use SQL Server.

3
  • 4
    In that very simplified example, it's extremely likely that SQL Server will generate identical plans for both queries. However, if you have a more complex CTE, moving the where clause out of the CTE may have fairly drastic changes. Can you add the "real" query this is concerning?
    – Hannah Vernon
    Nov 6 '20 at 17:27
  • 2
    No need to "wonder". There are no absolutes with respect to performance issues. The only way to know for certain is to test both and evaluate the results. If we exclude recursion, a CTE is usually just syntactic sugar. If you search on that term [syntactic sugar] you will find many discussions about CTEs and performance.
    – SMor
    Nov 6 '20 at 19:34
  • As others mentioned, it just depends on the query. Moving the WHERE clause outside vs inside of a CTE can alter the execution plan and result in drastically different performance. I'm seen it happen numerous times. But in simple queries, generally it'll make no difference. Like @Smor mentioned, you just have to test and compare results and execution plans to really know, on a case by case basis. And as Pavel Nefyodov mentioned, if you notice a performance hit by using the WHERE outside the CTE, a Temp Table might be a better choice if possible.
    – J.D.
    Nov 6 '20 at 23:39
1

Both queries have the same execution plan. You can check that in SQL Server Management Studio by typing:

WITH CTE1 AS ( SELECT Col1, Col2, Col3 FROM dbo.Table1
WHERE Col3 = '1' )
SELECT * from CTE1

;WITH CTE2 AS ( SELECT Col1, Col2, Col3 FROM dbo.Table1
)
SELECT * from CTE2 WHERE Col3 = '1' 

Regarding the second part of your question: it's quite hard to say without looking into actual code and understanding what you are trying to achieve, however you may find that using temporary table may be way more efficient than CTE.

3
  • 1
    Why is there a terminator before the second WITH? Or why isn't there before the first one? Nov 6 '20 at 21:19
  • I used that code to illustrate that execution plans are the same in SSMS. Using first terminator in the code is correct, although it won't matter in SSMS. Second terminator is needed to avoid syntax error whilst displaying execution plans. Nov 6 '20 at 23:35
  • 2
    IMHO, terminators, as the name implies, should be specified at the end of each statement rather than the beginning.
    – Dan Guzman
    Nov 7 '20 at 13:48
0

I run these 2 queries against AdventureWorks2019, let's call them:

"Query 1"

WITH CTE1
    AS (
      SELECT
        FirstName,
        MiddleName,
        LastName
      FROM [Person].[Person]     
      WHERE LastName = 'Sánchez'
    )

select * from CTE1

and "Query 2"

WITH CTE1
    AS (
      SELECT
        FirstName,
        MiddleName,
        LastName
      FROM [Person].[Person]     
    )

select * from CTE1
  WHERE LastName = 'Sánchez'

Then I went to the Execution Plan, right click and Show Execution Plan XML... and I compared them on Notepad++, they are practically identical:

enter image description here

The only discrepancy is:

  • Query 1 has MaxCompileMemory="2796560"
  • Query 2 has MaxCompileMemory="2796496"

But the discrepancy is too small to say that one query is better than another. And also the the logic calculation is exactly the same:

<RelOp AvgRowSize="126" EstimateCPU="0.00016745" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="9.5" EstimatedRowsRead="9.5" LogicalOp="Index Seek" NodeId="0" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00329245" TableCardinality="19972">

I pressed the Include Client Statistics button and I did 3 run for each query:

enter image description here

As you can see here you can have more insights but once again they are quite the same.

I hope I teach you how to debug a query through SSMS. Now use the same technique with your query because your database might be different, the table might be bigger and so the result might vary.

But overall we can say that both queries are the same.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.