Databases
StackOverflow2013
Postsdbo
POSQL Server CTE referred in self joins slow
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server CTE referred in self joins slow
    text
    copied!<p>I have written a table-valued UDF that starts by a CTE to return a subset of the rows from a large table. There are several joins in the CTE. A couple of inner and one left join to other tables, which don't contain a lot of rows. The CTE has a where clause that returns the rows within a date range, in order to return only the rows needed.</p> <p>I'm then referencing this CTE in 4 self left joins, in order to build subtotals using different criterias.</p> <p>The query is quite complex but here is a simplified pseudo-version of it</p> <pre><code>WITH DataCTE as ( SELECT [columns] FROM table INNER JOIN table2 ON [...] INNER JOIN table3 ON [...] LEFT JOIN table3 ON [...] ) SELECT [aggregates_columns of each subset] FROM DataCTE Main LEFT JOIN DataCTE BananasSubset ON [...] AND Product = 'Bananas' AND Quality = 100 LEFT JOIN DataCTE DamagedBananasSubset ON [...] AND Product = 'Bananas' AND Quality &lt; 20 LEFT JOIN DataCTE MangosSubset ON [...] GROUP BY [ </code></pre> <p>I have the feeling that SQL Server gets confused and calls the CTE for each self join, which seems confirmed by looking at the execution plan, although I confess not being an expert at reading those.</p> <p>I would have assumed SQL Server to be smart enough to only perform the data retrieval from the CTE only once, rather than do it several times.</p> <p>I have tried the same approach but rather than using a CTE to get the subset of the data, I used the same select query as in the CTE, but made it output to a temp table instead.</p> <p>The version referring the CTE version takes 40 seconds. The version referring the temp table takes between 1 and 2 seconds.</p> <p>Why isn't SQL Server smart enough to keep the CTE results in memory?</p> <p>I like CTEs, especially in this case as my UDF is a table-valued one, so it allowed me to keep everything in a single statement.</p> <p>To use a temp table, I would need to write a multi-statement table valued UDF, which I find a slightly less elegant solution.</p> <p>Did some of you had this kind of performance issues with CTE, and if so, how did you get them sorted?</p> <p>Thanks,</p> <p>Kharlos</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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