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

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

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

Войдите в таблицу 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 и вместо этого создать хранимую процедуру, где я сначала подготавливаю меньшую таблицу цен как временную таблицу с правильным кластеризованным индексом, а затем выполняю присоединение к ней. Это намного быстрее. Но я хотел бы придерживаться точки зрения.

Есть предположения...?


sql tsql sql-server-2008 common-table-expression indexed-views
person littlegreen    schedule 18.02.2011    source источник
comment
См. Ответ Quassnoi здесь. Если вы хотите, чтобы промежуточный результат материализовался и проиндексировался, вам гораздо лучше сделать это явно, используя #temp таблицы, хотя ссылка в ответе дает пример того, как это сделать с помощью руководства по плану.   -  person Martin Smith    schedule 18.02.2011
comment
Я не понимаю, как называется вид, который вы используете? Похоже, вы могли использовать CTE / View как взаимозаменяемые. От этой привычки следует отказаться, поскольку это не одно и то же.   -  person Abe Miessler    schedule 18.02.2011
comment
Что говорится в вашем плане выполнения? Это потенциально может быть решено путем добавления / изменения индексов, которые у вас есть в ваших таблицах ...   -  person Abe Miessler    schedule 18.02.2011
comment
@ Абэ. Я так понимаю, код в вопросе - это определение представления. CREATE VIEW foo AS <op's code goes here>   -  person Martin Smith    schedule 18.02.2011
comment
У вас есть какие-то показатели, с которыми можно поработать? Количество записей в LargeCarDataBase? Количество записей в CarPricesTransformation? Средняя цена за машину и т. Д.?   -  person RichardTheKiwi    schedule 21.02.2011
comment
Некоторые структуры таблиц тоже могут помочь. Похоже, вам нужен индекс для [CarBrand, CarModel, PriceStartingDate desc].   -  person RichardTheKiwi    schedule 21.02.2011
comment
@Martin - Хорошая находка с предыдущим вопросом - я добавил туда ответ. Хотя (1) могут быть другие способы решения CTE и (2) я думаю, что суть этого вопроса - это последние 30 символов запроса, но ожидание дополнительной информации.   -  person RichardTheKiwi    schedule 21.02.2011
comment
@ Абэ. Да, определение представления - это весь код, который я опубликовал, как уже догадался Мартин. Если я перейду на использование таблиц #temp, мне нужно будет переключиться на хранимые процедуры, но я потеряю гибкость представления.   -  person littlegreen    schedule 21.02.2011
comment
@ Ричард: Я просто говорю об общем случае. Я не могу опубликовать конкретный код. Вы можете предположить, что таблицы действительно большие, преобразование намного сложнее, все стандартные индексы есть, но запрос по-прежнему выполняется медленно. Например. худший сценарий.   -  person littlegreen    schedule 21.02.2011
comment
@All: извините за поздний ответ. Я не смотрел на свой компьютер на выходных.   -  person littlegreen    schedule 21.02.2011
comment
@littlegreen Открытое завершение вопроса - наименее вероятный способ получить полезный ответ. Та часть, которую вы упустили, вполне может быть самой важной частью головоломки, без которой все ответы будут бесполезны.   -  person RichardTheKiwi    schedule 21.02.2011
comment
@ Ричард: Без дальнейших комментариев.   -  person littlegreen    schedule 22.02.2011

Ответы (2)


arrow_upward
0
arrow_downward

Вы не можете составить «таблицу меньших цен», так как цена зависит от даты продажи. Кроме того, почему 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
person Stu    schedule 01.03.2011

arrow_upward
0
arrow_downward

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

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

    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.

person Steven de Salas    schedule 06.04.2011