SQL-запрос, возвращающий набор вычисленных значений для каждого сайта из нескольких таблиц


Я написал ниже, и я ищу любой способ, что я мог бы улучшить его работу.

IF OBJECT_ID('tempdb..#PLUTotals') IS NOT NULL
    DROP TABLE [dbo].[#PLUTotals];
IF OBJECT_ID('tempdb..#Level4Totals') IS NOT NULL
    DROP TABLE [dbo].[#Level4Totals];
IF OBJECT_ID('tempdb..#WasteTotals') IS NOT NULL
    DROP TABLE [dbo].[#WasteTotals];
IF OBJECT_ID('tempdb..#GPTotal') IS NOT NULL
    DROP TABLE [dbo].[#GPTotal];
SELECT SiteNo
      ,CASE
           WHEN Media = 1003
           THEN isNull(Value,0)
           ELSE 0
       END AS GrossSalesValue
      ,CASE
           WHEN Media = 1001
           THEN isNull(Qty,0)
           ELSE 0
       END AS CustomerCount
      ,CASE
           WHEN Media = 5001
                AND MediaIndex IN(1,8)
           THEN isNull(Value,0)
           ELSE 0
       END AS EodDiscount
      ,CASE
           WHEN Media = 5006
           THEN isNull(Value,0)
           ELSE 0
       END AS TotalDiscount
      ,CASE
           WHEN Media = 5001
                AND MediaIndex IN(26,168,35,38,72,193,119,208,211,212,213,47)
           THEN isNull(Qty,0)
           ELSE 0
       END AS MealDeal
INTO #PLUTotals
FROM TransactionDetail
WHERE EXISTS
(
    SELECT Descendant
    FROM DescendantSites
    WHERE Parent IN(@SiteNo)
    AND Descendant = TransactionDetail.SiteNo
)
AND Date BETWEEN @SessionDateFrom AND @SessionDateTo
AND TermNo > 0
AND EXISTS
(
    SELECT MediaNo
    FROM Media
    WHERE MediaNo IN(1001,1003,5001,5006)
    AND MediaNo = TransactionDetail.Media
);
SELECT SiteNo
      ,isNull(SUM(Value),0) AS Level4Sales
INTO #Level4Totals
FROM PLUSalesExtended
WHERE [Level] + 1 = 4
      AND SessionDate BETWEEN @SessionDateFrom AND @SessionDateTo
      AND TermNo = 0
      AND PluEntryType <> 4
      AND EXISTS
(
    SELECT Descendant
    FROM DescendantSites
    WHERE Parent IN(@SiteNo)
    AND Descendant = PluSalesExtended.SiteNo
)
GROUP BY SiteNo;
SELECT CustomerCode AS SiteNo
      ,ABS(SUM(Waste_StandardCost)) AS WasteTotals
INTO #WasteTotals
FROM [CybakeRS].[dbo].utf_Waste(@SessionDateFrom,@SessionDateTo,1)
WHERE EXISTS
(
    SELECT Descendant
    FROM DescendantSites
    WHERE Parent IN(@SiteNo)
    AND Descendant = CybakeRS.dbo.utf_Waste.CustomerCode
)
AND Waste_Qty <> 0
GROUP BY CustomerCode;
SELECT PluSales.SiteNo
      ,SUM(PluSales.Value) AS SalesValue
      ,SUM(PluSales.VatValue) AS VatAmount
      ,SUM(PluSales.Cost) AS CostOfSales
INTO #GPTotal
FROM PluSales
     INNER JOIN Sessions ON sessions.siteno = plusales.siteno
                            AND sessions.sessionno = plusales.sessionno
                            AND sessions.termno = plusales.termno
                            AND sessions.type = 7
                            AND sessions.termno = 0
WHERE sessions.sessiondate BETWEEN @SessionDateFrom AND @SessionDateTo
      AND EXISTS
(
    SELECT Descendant
    FROM DescendantSites
    WHERE Parent IN(@SiteNo)
    AND Descendant = PluSales.SiteNo
)
GROUP BY PluSales.SiteNo;
SELECT CASE
           WHEN PT.SiteNo IN(70,71)
           THEN 69
           WHEN PT.SiteNo IN(40,41)
           THEN 45
           ELSE PT.SiteNo
       END AS SiteNo
      ,SUM(PT.GrossSalesValue) AS GrossSalesValue
      ,SUM(PT.CustomerCount) AS CustomerCount
      ,SUM(PT.EodDiscount) AS EodDiscount
      ,SUM(PT.TotalDiscount) AS TotalDiscount
      ,SUM(PT.MealDeal) AS MealDeal
      ,L4T.Level4Sales AS Level4Sales
      ,WT.WasteTotals AS WasteTotals
      ,SUM(PT.MealDeal) / SUM(PT.CustomerCount) AS MealDealPercent
      ,L4T.Level4Sales / (L4T.Level4Sales + SUM(PT.EodDiscount)) AS Conversion
      ,SUM(PT.GrossSalesValue) / SUM(PT.CustomerCount) AS AveSpend
     ,SUM(GPT.SalesValue) AS GPTSales
     ,SUM(GPT.CostOfSales) AS GPTCosts
     ,SUM(GPT.VatAmount) AS GPTVatAmount
FROM #PLUTotals AS PT
     LEFT JOIN #Level4Totals AS L4T ON PT.SiteNo = L4T.SiteNo
     LEFT JOIN #WasteTotals AS WT ON PT.SiteNo = WT.SiteNo
     LEFT JOIN #GPTotal AS GPT ON PT.SiteNo = GPT.SiteNo
GROUP BY CASE
             WHEN PT.SiteNo IN(70,71)
             THEN 69
             WHEN PT.SiteNo IN(40,41)
             THEN 45
             ELSE PT.SiteNo
         END
        ,L4T.Level4Sales
        ,WT.WasteTotals;
DROP TABLE [dbo].[#PLUTotals];
DROP TABLE [dbo].[#Level4Totals];
DROP TABLE [dbo].[#WasteTotals];
DROP TABLE [dbo].[#GPTotal];

Код выполняет 5 запросов. 1-4 prefetchs запросов все необходимые данные и сохраняет результаты в свою временную таблицу. Запрос 5 затем объединяет все временные таблицы, чтобы дать мне необходимый результат. У нас есть доказательства, что индекс может существенно улучшить производительность, но это может легко быть реализована в решение клиентов на данный момент. Любые советы о том, как улучшить это будет принята с благодарностью. Существуют ли явные ямы падает, что я падаю в?



127
0
задан 28 марта 2018 в 02:03 Источник Поделиться
Комментарии
1 ответ

Проблема с оптимизацией запросов, что вы серьезно затруднено из-за структуры базы данных. Потому что у нас назначено несколько значений в столбце Qty и Value которая требует CASE заявление для разбора, чтобы получить осмысленные агрегации, это становится проблематичным для исполнения.

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

1) у вас уже есть EXISTS запрос для предварительной фильтрации TransactionDetails стол только MediaID вы заботитесь о, и это хорошо. Впрочем, я не удивлюсь, если двигатель в конечном итоге все равно делать сканирование таблицы, потому что есть слишком много логических фильтров. Один из возможных вариантов-сделать индивидуальный подзапроса, так что каждый агрегации стоять на своих собственных, и дать ему поработать, чтобы построить план выполнения, который нацелен на конкретные MediaId и другим критериям.

2) здание на #1, я бы исследовать, может ли это быть улучшено с помощью отфильтрованных индексов. Используя первый CASE в качестве примера, мы можем создать новый индекс, как так....

CREATE INDEX IX_TransactionDetail_Filtered1
ON dbo.TransactionDetail(SiteID, Date, Value)
WHERE TermNo > 0
AND Media = 1003;

Заметим, что жестко закодированные значения, такие как TermNo и Media фильтруются, так что вы можете использовать индекс более эффективно с помощью отдельного запроса (который может быть использован в качестве подзапроса):

SELECT 
SiteNo,
isNull(Value,0) AS GrossSalesValue
FROM dbo.TransactionDetail
WHERE TermID > 0
AND Media = 1003
AND Date BETWEEN @SessionDateFrom AND @SessionDateTo
AND EXISTS ( ... sites subquery ...)

Повторяя выше 3 другие подзапросы, с их собственный отфильтрованного индекса может и не помочь, зависит от формы/объема данных.

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

3) создать индексированное представление

Если вы не хотите создавать 4 или более отфильтрованных индексов (и вы, вероятно, не хотите, что много в любом случае), то альтернативой является, чтобы увидеть, если вы можете написать вместо индексированного представления. В вашем исходном запросе, вы читаете только TransactionDetail с каким-то критериям. Тот, которые являются динамичными являются SiteNo и Dateтак, представление должны выглядеть примерно так....

CREATE VIEW dbo.vwTransactionDetail_Indexed 
WITH SCHEMABINDING AS
SELECT SiteNo
,Date
,CASE
WHEN Media = 1003
THEN isNull(Value,0)
ELSE 0
END AS GrossSalesValue
,CASE
WHEN Media = 1001
THEN isNull(Qty,0)
ELSE 0
END AS CustomerCount
,CASE
WHEN Media = 5001
AND MediaIndex IN(1,8)
THEN isNull(Value,0)
ELSE 0
END AS EodDiscount
,CASE
WHEN Media = 5006
THEN isNull(Value,0)
ELSE 0
END AS TotalDiscount
,CASE
WHEN Media = 5001
AND MediaIndex IN(26,168,35,38,72,193,119,208,211,212,213,47)
THEN isNull(Qty,0)
ELSE 0
END AS MealDeal
FROM TransactionDetail
WHERE EXISTS
(
SELECT Descendant
FROM DescendantSites
WHERE Descendant = TransactionDetail.SiteNo
)
AND TermNo > 0
AND EXISTS
(
SELECT MediaNo
FROM Media
WHERE MediaNo IN(1001,1003,5001,5006)
AND MediaNo = TransactionDetail.Media
);

Таким образом, вы можете просто отфильтровать индексированное представление для SiteNo и Date и использовать результат для вставки в #PLUTotals. Обратите внимание, что я не 100% уверен, что если EXISTS необходим для DescendantSites - если это подмножество всех возможных сайтах, то мы будем иметь это. Но если это все возможные сайты, то это даст нам ничего, поэтому он тоже должен идти.

4) поддерживать, а не сводная таблица

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

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

Заключение

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

1
ответ дан 29 марта 2018 в 12:03 Источник Поделиться