The CTE as understood by a Dev :)

Since being introduced to the CTE I’ve become increasingly impressed at how well it manages to increase the speed of a query. Just recently I put it to the test to see how well it can increase the speed of a query I needed for pulling the last payment of an Order that had not been authorized. The following shows the final result.

WITH MaxPayment AS(
SELECT MAX(PaymentId) AS PaymentId
FROM dbo.Payment
WHERE IsAuthorized = 'F' AND PaymentMethodId = 116
GROUP BY OrderId),
Payments AS(
SELECT p.*
FROM dbo.Payment p
INNER JOIN MaxPayment m ON p.PaymentId = m.PaymentId)
SELECT o.OrderId, o.OrderBalance, p.IsAuthorized, p.PaymentId
FROM dbo.Orders o INNER JOIN Payments p ON p.OrderId = o.OrderId
WHERE OrderStatusId < 4000 AND PackingSlipBatchId = 0
AND o.OrderId IN (…)

This beauty uses two CTE’s in order to finally inner join with the final product. This query can pull any set of OrderId’s supplied to it in a lightning fast fashion (nanoseconds).

For those not familiar with the CTE its usefulness over subqueries becomes evident when I explain that when the CTE is generated the original schema is preserved which means tasty indexes you’ve added will be preserved unlike a subquery that essentially creates an indexless temporary table copies information into it then provides a join thereafter.

Interesting side not that I discovered when formulating this query. I wanted to know if prefiltering the information in the CTE would provide any speed increases so rather than specify OrderId IN (…) in the main body of the query I would supply it in the MaxPayment CTE. Analyzing the execution plan abruptly stopped that foolishness. The execution plans regardless of where I filtered were identical thus leading me to believe that the CTE will handle all filtering last with the main body of the query.