Присоединение к CTE преобразованию таблицы

Question

Часто я сталкиваюсь с такой ситуацией, когда мне нужно соединить большую таблицу с определенной трансформацией таблицы.

Я сделал пример с большим столом и меньшим столом цен.

Введите таблицу CarPrices, в которой указаны цены по марке / модели автомобиля с датами начала и окончания. Я хочу объединить все проданные автомобили с продажной ценой в таблице CarPrices по критерию SaleDate BETWEEN PriceStartingDate и PriceEndingDate, но если цены за период отсутствуют, я хочу присоединиться к самой новой найденной цене.

Я могу сделать это так, но это очень медленно:

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)

Надежный способ сделать это быстрее - это забыть о создании VIEW и создании вместо этого хранимой процедуры, где я сначала готовлю таблицу меньших цен как временную таблицу с правильным кластеризованным индексом, а затем присоединяюсь к ней. Это намного быстрее. Но я хотел бы придерживаться мнения.

Какие-нибудь мысли...?

Answer 1

Вы не можете составить "меньшую таблицу цен", так как цена зависит от даты продажи. Кроме того, почему CTE на первом месте?

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

Вы пробовали индексированные представления?

Результаты из индексированных представлений автоматически записываются на диск, чтобы вы могли быстро их получить.

    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)

Затем вы можете выбрать непосредственно из этого представления, которое будет возвращать записи с той же скоростью, что и выбор из таблицы.

С другой стороны, индексированные представления замедляют изменения базовых таблиц. Если вы беспокоитесь о стоимости вставки записей в таблицу LargeCarDataBase После создания этого представления вы можете создать индекс по столбцам CarBrand, CarModel и SaleDate, который должен ускорить вставку и обновление этой таблицы.

Подробнее об индексированных представлениях см . Статью Microsoft.