Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

The optimizer should considering materialising results of CTEs

If you refer to a CTE multiple times in a query, SQL Server will in practice compute the CTE for every appearance. This means that in some situations as a user it is better for me to materialise the result into a temp table, and then run a query on a temp table. On the other hand there are situations where this adds extra overhead, and causes the entire batch to run slower.

Below is an example with a query where a CTE/temp table is self-join three times. The idea of the query is to show the change in order amount related to the two most previous orders. In this example, there are two possible advantages with the temp table:
1) The order amount has to be computed only once.
2) By adding a clustered index on the temp table, the joins can run faster.

When you run this in Northwind, the CTE query is somewhat faster, at least on my machine. But in Northgale (http://www.sommarskog.se/dynsearch/Northgale.sql) the temp table wins with a wide margin.

DECLARE @d datetime2(3) = sysdatetime()

;WITH OrderSums AS (
SELECT OrderID, SUM(Quantity * UnitPrice) AS Amt
FROM [Order Details]
GROUP BY OrderID
), numbered_orders AS (
SELECT O.OrderID, O.CustomerID, O.OrderDate, C.CompanyName, OS.Amt,
rowno = row_number() OVER(PARTITION BY O.CustomerID ORDER BY O.OrderDate, O.OrderID)
FROM Orders O
JOIN OrderSums OS ON O.OrderID = OS.OrderID
JOIN Customers C ON O.CustomerID = C.CustomerID
)
SELECT c.CompanyName, c.OrderID, c.OrderDate, c.Amt, c.Amt - coalesce(b.Amt, 0) AS delta,
c.Amt - coalesce(a.Amt, 0) AS delta2
INTO #tmp
FROM numbered_orders c
LEFT JOIN numbered_orders b ON b.CustomerID = c.CustomerID
AND b.rowno = c.rowno - 1
LEFT JOIN numbered_orders a ON a.CustomerID = c.CustomerID
AND a.rowno = c.rowno -2
ORDER BY c.CompanyName, c.OrderID, c.OrderDate

PRINT 'All CTE took ' + ltrim(str(datediff(ms, @d, sysdatetime()))) + ' ms.'
go
DROP TABLE #tmp
go
DECLARE @d datetime2(3) = sysdatetime()

CREATE TABLE #numbered_orders (
OrderID int NOT NULL,
CustomerID nchar(5) NOT NULL,
OrderDate datetime NOT NULL,
CompanyName nvarchar(40) NOT NULL,
Amt decimal(10, 2) NOT NULL,
rowno int NOT NULL,
PRIMARY KEY CLUSTERED (CustomerID, rowno))

;WITH OrderSums AS (
SELECT OrderID, SUM(Quantity * UnitPrice) AS Amt
FROM [Order Details]
GROUP BY OrderID
)
INSERT #numbered_orders
SELECT O.OrderID, O.CustomerID, O.OrderDate, C.CompanyName, OS.Amt,
rowno = row_number() OVER(PARTITION BY O.CustomerID ORDER BY O.OrderDate, O.OrderID)
FROM Orders O
JOIN OrderSums OS ON O.OrderID = OS.OrderID
JOIN Customers C ON O.CustomerID = C.CustomerID

SELECT c.CompanyName, c.OrderID, c.OrderDate, c.Amt, c.Amt - coalesce(b.Amt, 0) AS delta,
c.Amt - coalesce(a.Amt, 0) AS delta2
INTO #tmp
FROM #numbered_orders c
LEFT JOIN #numbered_orders b ON b.CustomerID = c.CustomerID
AND b.rowno = c.rowno - 1
LEFT JOIN #numbered_orders a ON a.CustomerID = c.CustomerID
AND a.rowno = c.rowno -2
ORDER BY c.CompanyName, c.OrderID, c.OrderDate

DROP TABLE #numbered_orders

PRINT 'Temp table took ' + ltrim(str(datediff(ms, @d, sysdatetime()))) + ' ms.'
go
DROP TABLE #tmp

15 votes
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

Upvotes: 25

<=-=Jan 30 2011 2:35PM=-=>

Adam Mechanic and I have asked for hints to force materialization (218968 and 483181); however, I’d settle for auto-magic from the optimizer!

<=-=Jan 31 2011 9:30AM=-=>

Thanks for the feedback, Erland. This is a known issue and we’ll consider it for a future release. It’s not something we’ll be able to do anything about for Denali RTM though. We appreciate your suggestion!

Best regards,
Eric Hanson
Program Manager
SQL Server Query Processing

<=-=Feb 3 2011 11:26AM=-=> Thanks Michael! -Eric <=-=Jan 31 2012 10:21AM=-=>

We closed this as a duplicate. -Eric

0 comments

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base