У меня вопрос производительности для общих выражений таблицы в SQL Server. В нашей команде разработчиков мы используем много цепей CTE при построении наших запросов. В настоящее время я работаю над запросом, который имел ужасную производительность. Но я узнал, что если я в середине цепи вставил все записи до этого CTE во временную таблицу, а затем продолжил, но выбрав из этой таблицы темп, я значительно улучшил производительность. Теперь я хотел бы получить некоторую помощь, чтобы понять, относится ли этот тип изменений только к этому конкретному запросу и почему два случая, которые вы увидите ниже, значительно отличаются по производительности. Или мы могли бы чрезмерно использовать CTE в нашей команде и можем ли мы повысить производительность, узнав из этого случая?
Пожалуйста, попробуйте объяснить мне, что именно происходит здесь...
Код завершен, и вы сможете запускать его на SQL Server 2008 и, возможно, в 2005 году. Одна часть закомментирована, и моя идея состоит в том, что вы можете переключать два случая, комментируя один или другой. Вы можете увидеть, где разместить комментарии к блоку, я отметил эти места с помощью --block comment here
и --end block comment here
Это медленный исполняемый файл, который является uncommented default. Вот вы:
--Declare tables to use in example.
CREATE TABLE #Preparation
(
Date DATETIME NOT NULL
,Hour INT NOT NULL
,Sales NUMERIC(9,2)
,Items INT
);
CREATE TABLE #Calendar
(
Date DATETIME NOT NULL
)
CREATE TABLE #OpenHours
(
Day INT NOT NULL,
OpenFrom TIME NOT NULL,
OpenTo TIME NOT NULL
);
--Fill tables with sample data.
INSERT INTO #OpenHours (Day, OpenFrom, OpenTo)
VALUES
(1, '10:00', '20:00'),
(2, '10:00', '20:00'),
(3, '10:00', '20:00'),
(4, '10:00', '20:00'),
(5, '10:00', '20:00'),
(6, '10:00', '20:00'),
(7, '10:00', '20:00')
DECLARE @CounterDay INT = 0, @CounterHour INT = 0, @Sales NUMERIC(9, 2), @Items INT;
WHILE @CounterDay < 365
BEGIN
SET @CounterHour = 0;
WHILE @CounterHour < 5
BEGIN
SET @Items = CAST(RAND() * 100 AS INT);
SET @Sales = CAST(RAND() * 1000 AS NUMERIC(9, 2));
IF @Items % 2 = 0
BEGIN
SET @Items = NULL;
SET @Sales = NULL;
END
INSERT INTO #Preparation (Date, Hour, Items, Sales)
VALUES (DATEADD(DAY, @CounterDay, '2011-01-01'), @CounterHour + 13, @Items, @Sales);
SET @CounterHour += 1;
END
INSERT INTO #Calendar (Date) VALUES (DATEADD(DAY, @CounterDay, '2011-01-01'));
SET @CounterDay += 1;
END
--Here the query starts.
;WITH P AS (
SELECT DATEADD(HOUR, Hour, Date) AS Hour
,Sales
,Items
FROM #Preparation
),
O AS (
SELECT DISTINCT DATEADD(HOUR, SV.number, C.Date) AS Hour
FROM #OpenHours AS O
JOIN #Calendar AS C ON O.Day = DATEPART(WEEKDAY, C.Date)
JOIN master.dbo.spt_values AS SV ON SV.number BETWEEN DATEPART(HOUR, O.OpenFrom) AND DATEPART(HOUR, O.OpenTo)
),
S AS (
SELECT O.Hour, P.Sales, P.Items
FROM O
LEFT JOIN P ON P.Hour = O.Hour
)
--block comment here case 1 (slow performing)
--With this technique it takes about 34 seconds.
,N AS (
SELECT
A.Hour
,A.Sales AS SalesOrg
,CASE WHEN COALESCE(B.Sales, C.Sales, 1) < 0
THEN 0 ELSE COALESCE(B.Sales, C.Sales, 1) END AS Sales
,A.Items AS ItemsOrg
,COALESCE(B.Items, C.Items, 1) AS Items
FROM S AS A
OUTER APPLY (SELECT TOP 1 *
FROM S
WHERE Hour <= A.Hour
AND Sales IS NOT NULL
AND DATEDIFF(DAY, Hour, A.Hour) = 0
ORDER BY Hour DESC) B
OUTER APPLY (SELECT TOP 1 *
FROM S
WHERE Sales IS NOT NULL
AND DATEDIFF(DAY, Hour, A.Hour) = 0
ORDER BY Hour) C
)
--end block comment here case 1 (slow performing)
/*--block comment here case 2 (fast performing)
--With this technique it takes about 2 seconds.
SELECT * INTO #tmpS FROM S;
WITH
N AS (
SELECT
A.Hour
,A.Sales AS SalesOrg
,CASE WHEN COALESCE(B.Sales, C.Sales, 1) < 0
THEN 0 ELSE COALESCE(B.Sales, C.Sales, 1) END AS Sales
,A.Items AS ItemsOrg
,COALESCE(B.Items, C.Items, 1) AS Items
FROM #tmpS AS A
OUTER APPLY (SELECT TOP 1 *
FROM #tmpS
WHERE Hour <= A.Hour
AND Sales IS NOT NULL
AND DATEDIFF(DAY, Hour, A.Hour) = 0
ORDER BY Hour DESC) B
OUTER APPLY (SELECT TOP 1 *
FROM #tmpS
WHERE Sales IS NOT NULL
AND DATEDIFF(DAY, Hour, A.Hour) = 0
ORDER BY Hour) C
)
--end block comment here case 2 (fast performing)*/
SELECT * FROM N ORDER BY Hour
IF OBJECT_ID('tempdb..#tmpS') IS NOT NULL DROP TABLE #tmpS;
DROP TABLE #Preparation;
DROP TABLE #Calendar;
DROP TABLE #OpenHours;
Если вы хотите попробовать и понять, что я делаю на последнем шаге, у меня есть вопрос SO здесь.
Для меня случай 1 занимает около 34 секунд, а случай 2 занимает около 2 секунд. Разница в том, что я сохраняю результат из S в временной таблице в случае 2, в случае 1 я использую S непосредственно в следующем CTE.