Joining to CTE transformation of table

Question

frequently I encounter a situation like this, where I need to join a big table to a certain transformation of a table.

I have made an example with a big table and a smaller prices table.

Enter the table CarPrices, which has prices per car brand/model with starting and ending dates. I want to join all sold cars to the sales price in the CarPrices table, on the criterium SaleDate BETWEEN PriceStartingDate and PriceEndingDate, but if there is no price for the period, I want to join to the newest price that is found.

I can accomplish it like this but it is terribly slow:

WITH CarPricesTransformation AS (
    SELECT CarBrand, CarModel, PriceStartingDate,
        CASE WHEN row_number() OVER (PARTITION BY CarBrand, CarModel, 
            ORDER BY PriceStartingDate DESC) = 1
            THEN NULL ELSE PriceEndingDate END PriceEndingDate,
        Price
    FROM CarPrices
)
SELECT SUM(Price)
FROM LargeCarDataBase C
INNER JOIN CarPricesTransformation P
ON C.CarBrand = P.CarBrand
AND C.CarModel = P.CarModel
AND C.SaleDate >= P.PriceStartingDate
AND (C.SaleDate <= P.PriceEndingDate OR P.PriceEndingDate IS NULL)

A reliable way to do it quicker is to forget about making a VIEW and creating a stored procedure instead, where I first prepare the smaller prices table as a temporary table with the correct clustered index, and then make the join to that. This is much faster. But I would like to stick with a view.

Any thoughts...?

Answer 1

You can't make a "smaller prices table" since the price depends on the sale date. Also, why the CTE in the first place?

Select
  Sum(Coalesce(ActivePrice.Price, LatestPrice.Price))
From
  LargeCarDataBase As Sales
  Left Outer Join CarPrices As ActivePrice
    On Sales.CarBrand = ActivePrice.CarBrand
    And Sales.CarModel = ActivePrice.CarModel
    And (((Sales.SaleDate >= ActivePrice.PriceStartingDate)
          And ((Sales.SaleDate <= ActivePrice.PriceEndingDate)
               Or (ActivePrice.PriceEndingDate Is Null)))
  Left Outer Join CarPrices As LatestPrice
    On Sales.CarBrand = LatestPrice.CarBrand
    And Sales.CarModel = LatestPrice.CarModel
    And LatestPrice.PriceEndingDate Is Null

Answer 2

Have you tried Indexed Views?

The results from Indexed Views are automatically commited to disk so you can retrieve them super-fast.

    CREATE VIEW [dbo].[SuperFastCarPrices] WITH SCHEMABINDING AS
    SELECT  C.CarBrand,
            C.CarModel,
            C.SaleDate,
            SUM(P.Price) AS Price
    FROM CarPrices P
    INNER JOIN LargeCarDataBase C
        ON C.CarBrand = P.CarBrand
        AND C.CarModel = P.CarModel
        AND C.SaleDate >= P.PriceStartingDate
        AND (P.PriceEndingDate IS NULL OR C.SaleDate <= P.PriceEndingDate)
    GROUP BY C.CarBrand, C.CarModel, C.SaleDate

    CREATE UNIQUE CLUSTERED INDEX [IDX_SuperFastCarPrices] 
    ON [dbo].[SuperFastCarPrices](CarBrand, CarModel, SaleDate)

You can then select directly from this view, which will return records at the same speed as selecting from a table.

There is the downside that indexed views slow down changes to the underlying tables. If you are worried about the cost of inserting records into the table LargeCarDataBase after this view has been created, you can create an index on columns CarBrand, CarModel and SaleDate which should speed up insertion and update on this table.

For more on Indexed Views see Microsoft's Article.