Databases
StackOverflow2013
Postsdbo
POT-SQL bad performance with CTE
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POT-SQL bad performance with CTE
    primarykey
    data
    text
    <p>I have a performance question about Common table expressions in SQL Server. In our developer team we use a lot of chaining CTEs when building our queries. I am currently working on a query which had terrible performance. But I found out that if I in the middle of the chain inserted all the records up to that CTE in a temporary table instead and then continued but selecting from that temp table I improved performance significantly. Now I would like to get some help to understand if this type of change only applies to this specific query and why the two cases you will see below differ so much in performance. Or could we possibly overuse CTEs in our team and can we gain performance generally by learning from this case?</p> <p>Please try to explain to me exactly what is happening here...</p> <p>The code is complete and you will be able to run it on SQL Server 2008 and probably 2005 too. One part is commented out and my idea is that you can switch the two cases by comment out one or the other. You can see where to put your block comments, I have marked these places with <code>--block comment here</code> and <code>--end block comment here</code></p> <p>It is the slow performing case that is uncommented default. Here you are:</p> <pre><code>--Declare tables to use in example. CREATE TABLE #Preparation ( Date DATETIME NOT NULL ,Hour INT NOT NULL ,Sales NUMERIC(9,2) ,Items INT ); CREATE TABLE #Calendar ( Date DATETIME NOT NULL ) CREATE TABLE #OpenHours ( Day INT NOT NULL, OpenFrom TIME NOT NULL, OpenTo TIME NOT NULL ); --Fill tables with sample data. INSERT INTO #OpenHours (Day, OpenFrom, OpenTo) VALUES (1, '10:00', '20:00'), (2, '10:00', '20:00'), (3, '10:00', '20:00'), (4, '10:00', '20:00'), (5, '10:00', '20:00'), (6, '10:00', '20:00'), (7, '10:00', '20:00') DECLARE @CounterDay INT = 0, @CounterHour INT = 0, @Sales NUMERIC(9, 2), @Items INT; WHILE @CounterDay &lt; 365 BEGIN SET @CounterHour = 0; WHILE @CounterHour &lt; 5 BEGIN SET @Items = CAST(RAND() * 100 AS INT); SET @Sales = CAST(RAND() * 1000 AS NUMERIC(9, 2)); IF @Items % 2 = 0 BEGIN SET @Items = NULL; SET @Sales = NULL; END INSERT INTO #Preparation (Date, Hour, Items, Sales) VALUES (DATEADD(DAY, @CounterDay, '2011-01-01'), @CounterHour + 13, @Items, @Sales); SET @CounterHour += 1; END INSERT INTO #Calendar (Date) VALUES (DATEADD(DAY, @CounterDay, '2011-01-01')); SET @CounterDay += 1; END --Here the query starts. ;WITH P AS ( SELECT DATEADD(HOUR, Hour, Date) AS Hour ,Sales ,Items FROM #Preparation ), O AS ( SELECT DISTINCT DATEADD(HOUR, SV.number, C.Date) AS Hour FROM #OpenHours AS O JOIN #Calendar AS C ON O.Day = DATEPART(WEEKDAY, C.Date) JOIN master.dbo.spt_values AS SV ON SV.number BETWEEN DATEPART(HOUR, O.OpenFrom) AND DATEPART(HOUR, O.OpenTo) ), S AS ( SELECT O.Hour, P.Sales, P.Items FROM O LEFT JOIN P ON P.Hour = O.Hour ) --block comment here case 1 (slow performing) --With this technique it takes about 34 seconds. ,N AS ( SELECT A.Hour ,A.Sales AS SalesOrg ,CASE WHEN COALESCE(B.Sales, C.Sales, 1) &lt; 0 THEN 0 ELSE COALESCE(B.Sales, C.Sales, 1) END AS Sales ,A.Items AS ItemsOrg ,COALESCE(B.Items, C.Items, 1) AS Items FROM S AS A OUTER APPLY (SELECT TOP 1 * FROM S WHERE Hour &lt;= A.Hour AND Sales IS NOT NULL AND DATEDIFF(DAY, Hour, A.Hour) = 0 ORDER BY Hour DESC) B OUTER APPLY (SELECT TOP 1 * FROM S WHERE Sales IS NOT NULL AND DATEDIFF(DAY, Hour, A.Hour) = 0 ORDER BY Hour) C ) --end block comment here case 1 (slow performing) /*--block comment here case 2 (fast performing) --With this technique it takes about 2 seconds. SELECT * INTO #tmpS FROM S; WITH N AS ( SELECT A.Hour ,A.Sales AS SalesOrg ,CASE WHEN COALESCE(B.Sales, C.Sales, 1) &lt; 0 THEN 0 ELSE COALESCE(B.Sales, C.Sales, 1) END AS Sales ,A.Items AS ItemsOrg ,COALESCE(B.Items, C.Items, 1) AS Items FROM #tmpS AS A OUTER APPLY (SELECT TOP 1 * FROM #tmpS WHERE Hour &lt;= A.Hour AND Sales IS NOT NULL AND DATEDIFF(DAY, Hour, A.Hour) = 0 ORDER BY Hour DESC) B OUTER APPLY (SELECT TOP 1 * FROM #tmpS WHERE Sales IS NOT NULL AND DATEDIFF(DAY, Hour, A.Hour) = 0 ORDER BY Hour) C ) --end block comment here case 2 (fast performing)*/ SELECT * FROM N ORDER BY Hour IF OBJECT_ID('tempdb..#tmpS') IS NOT NULL DROP TABLE #tmpS; DROP TABLE #Preparation; DROP TABLE #Calendar; DROP TABLE #OpenHours; </code></pre> <p>If you would like to try and understand what I am doing in the last step I have a SO question about it <a href="https://stackoverflow.com/questions/10654508/t-sql-query-update-null-values">here</a>.</p> <p>For me case 1 takes about 34 seconds and case 2 takes about 2 seconds. The difference is that I store the result from S in a temp table in case 2, in case 1 I use S in my next CTE directly.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USJohn
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POT-SQL bad performance with CTE
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POT-SQL bad performance with CTE
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POT-SQL bad performance with CTE
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 For runnable code. I suggest running them both and pasting the execution plan XML into SQL Sentry Plan Explorer. The reason for the difference will then be quite evident. It ends up scanning `#Preparation` 20,000 times in one part of the plan and 10,000 times in another part for an example.
      singulars
      1. POT-SQL bad performance with CTE
      1. USMartin Smith
    2. COThanks. I installed SQL Sentry Plan Explorer. I am still learning SQL Server and I am not able to read execution plans, neither in Sentry Plan. But the answer is that one cannot say anything about if its best with CTE or temp table in certain scenarios without investigating the execution plans? Where do you see 20,000 and 10,000 times? I can't find it. Is it possible to say something about why one part is scanning #Preparation 20,000 times by referring to the code?
      singulars
      1. POT-SQL bad performance with CTE
      1. USJohn
    3. COHave you seen this? "SQL 2005 CTE vs TEMP table Performance when used in joins of other tables" http://stackoverflow.com/questions/1531835/sql-2005-cte-vs-temp-table-performance-when-used-in-joins-of-other-tables
      singulars
      1. POT-SQL bad performance with CTE
      1. USJV.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload