none
Is WITH / Common Table Expression a syntactic sugar? RRS feed

  • Question

  • I tried to use WITH to factor out the shared star join portion from a sql statement as it's usually the most expensive part.

    However, examing the execution plan shows that the WITH clause is merely a syntactic suger that will internally be plugged back as derived tables where the same star join is executed repeatedly.

    Is the intermediate rowset produced by a WITH caluse ever shared during the query execution?
    Wednesday, October 4, 2006 1:19 AM

Answers

  • In SQL Server, CTEs are similar to views in that the definition or query expression is replaced inline in the query in all references. So you don't really get any results caching etc. You need to use temporary tables if you want to do that.
     
    Except for recursive CTEs, you can pretty much convert every CTE to use the query expression with derived tables and get the same results. Although it may look windy and complicated. The only simplification is that you can specify an alias to a query expression and reference it multiple times without repeating the query expression. Again this is current implementation in SQL Server.
     
    In the ANSI SQL standards, the WITH clause was mainly introduced to support recursive queries. And the results of query in the WITH clause results in a virtual table that can be used multiple times. This is different from syntax level substitution that happens now in SQL Server.
     
    I guess there is surely room for improvement if you compare with the standards. And depending on how you use CTE you can actually get confusing results if you think of it as a temporary result set that doesn't change for the duration of execution of the query. You could run the query under RCSI for example but that only protects against certain changes to base tables.
    Wednesday, October 4, 2006 10:58 PM

All replies

  • jeopardy:

    Maybe; it is certainly a gizmo that I have on my list to watch how it is used and to view how it performs.  One of the first things that I thought of with this was to replace all of my "iterator" tables with a view based on a CTE.  That ended up being in the "no way" category.  It was many times more slow than a simple table of numbers and I quickly abandoned the idea.  After that I thought maybe I could create an indexed view with the same idea.  Also, no good; I couldn't create an index on a view that used a CTE.

    When cursors were first introduced all of the developers wanted to use the new gizmo in all of their stored procedures and so cursors quickly proliferated -- and many of the stored procedures developed at that time ran poorly.  I went on several job interviews at the time and in every single interview I was asked if I knew how to use cursors.  The truth was that I knew how to use them, and in general I tried to avoid using cursors, but I still gave the expected response to the question.  That question made me want to puke.  After a few disasters I convinced management that automatic use of cursors for all applications was a bad idea -- it was a better idea to try to target cursors for appropriate situations where a record based process made more sense than a set based process.

    I approach CTEs with a similar grain of salt although I don't think it is likely that I will get to the point of loathing CTEs the way I over-reacted and loathed cursors for a long time.  What I really fear with SQL 2005 is that developers will decide to start writing all of their stored procedures and functions in either C# and Java instead of Transactl SQL and all of the sudden I will again have a boatload of record-based processes where it would be more appropriate to have set-based processes and the developers will be screaming about SQL performance issues.  But I like the new features of SQL 2005 and am liking what I see so far.

    Dave

    Wednesday, October 4, 2006 2:15 PM
    Moderator
  • CTE's are not perfect, but they are FAR from the evil of cursors.  Cursors are just bad form in T-SQL.  You can never loathe them enough :)

    But the answer to the original question is yes, it can, but it does so apparently very pessimistically.  The people I discussed it with mentioned there being hacks with using order by and top in the CTE/Derived table definition, but I would suggest that the best solution using the current method is to build a local variable temporary table if you have a small or smallish set of data that you want to materialize in this manner (presumably because of performance.)

     

    Wednesday, October 4, 2006 9:58 PM
    Moderator
  • In SQL Server, CTEs are similar to views in that the definition or query expression is replaced inline in the query in all references. So you don't really get any results caching etc. You need to use temporary tables if you want to do that.
     
    Except for recursive CTEs, you can pretty much convert every CTE to use the query expression with derived tables and get the same results. Although it may look windy and complicated. The only simplification is that you can specify an alias to a query expression and reference it multiple times without repeating the query expression. Again this is current implementation in SQL Server.
     
    In the ANSI SQL standards, the WITH clause was mainly introduced to support recursive queries. And the results of query in the WITH clause results in a virtual table that can be used multiple times. This is different from syntax level substitution that happens now in SQL Server.
     
    I guess there is surely room for improvement if you compare with the standards. And depending on how you use CTE you can actually get confusing results if you think of it as a temporary result set that doesn't change for the duration of execution of the query. You could run the query under RCSI for example but that only protects against certain changes to base tables.
    Wednesday, October 4, 2006 10:58 PM
  • There is a suggestion on the connect site that Adam Machanic made to have this sort of functionality.  If interested, please go here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=218968

     

    Friday, October 6, 2006 8:52 PM
    Moderator