Oto kilka metod, które możesz porównać. Najpierw skonfigurujmy tabelę z danymi pozorowanymi. Wypełniam to losowymi danymi z sys.all_columns. Cóż, to trochę losowe - upewniam się, że daty są ciągłe (co jest tak naprawdę ważne tylko dla jednej z odpowiedzi).
CREATE TABLE dbo.Hits(Day SMALLDATETIME, CustomerID INT);
CREATE CLUSTERED INDEX x ON dbo.Hits([Day]);
INSERT dbo.Hits SELECT TOP (5000) DATEADD(DAY, r, '20120501'),
COALESCE(ASCII(SUBSTRING(name, s, 1)), 86)
FROM (SELECT name, r = ROW_NUMBER() OVER (ORDER BY name)/10,
s = CONVERT(INT, RIGHT(CONVERT(VARCHAR(20), [object_id]), 1))
FROM sys.all_columns) AS x;
SELECT
Earliest_Day = MIN([Day]),
Latest_Day = MAX([Day]),
Unique_Days = DATEDIFF(DAY, MIN([Day]), MAX([Day])) + 1,
Total_Rows = COUNT(*)
FROM dbo.Hits;
Wyniki:
Earliest_Day Latest_Day Unique_Days Total_Days
------------------- ------------------- ----------- ----------
2012-05-01 00:00:00 2013-09-13 00:00:00 501 5000
Dane wyglądają tak (5000 wierszy) - ale będą wyglądać nieco inaczej w zależności od wersji i wersji #:
Day CustomerID
------------------- ---
2012-05-01 00:00:00 95
2012-05-01 00:00:00 97
2012-05-01 00:00:00 97
2012-05-01 00:00:00 117
2012-05-01 00:00:00 100
...
2012-05-02 00:00:00 110
2012-05-02 00:00:00 110
2012-05-02 00:00:00 95
...
Wyniki sumy bieżącej powinny wyglądać tak (501 wierszy):
Day c rt
------------------- -- --
2012-05-01 00:00:00 6 6
2012-05-02 00:00:00 5 11
2012-05-03 00:00:00 4 15
2012-05-04 00:00:00 7 22
2012-05-05 00:00:00 6 28
...
Metody, które zamierzam porównać to:
- „self-join” - purystyczne podejście oparte na zestawie
- „rekurencyjne CTE z datami” - zależy to od ciągłych dat (bez przerw)
- „rekurencyjne CTE z numerem_wiersza” - podobne do powyższego, ale wolniejsze, zależne od ROW_NUMBER
- „rekurencyjne CTE ze stołem #temp” - skradzione z odpowiedzi Mikaela, jak sugerowano
- „dziwaczna aktualizacja”, która choć nie jest obsługiwana i nie obiecuje określonego zachowania, wydaje się dość popularna
- "kursor"
- SQL Server 2012 przy użyciu nowej funkcji okienkowania
dołączyć do siebie
W ten sposób ludzie każą ci to robić, gdy ostrzegają, abyś trzymał się z dala od kursorów, ponieważ „oparte na zestawie jest zawsze szybsze”. W niektórych ostatnich eksperymentach odkryłem, że kursor wyprzedza to rozwiązanie.
;WITH g AS
(
SELECT [Day], c = COUNT(DISTINCT CustomerID)
FROM dbo.Hits
GROUP BY [Day]
)
SELECT g.[Day], g.c, rt = SUM(g2.c)
FROM g INNER JOIN g AS g2
ON g.[Day] >= g2.[Day]
GROUP BY g.[Day], g.c
ORDER BY g.[Day];
rekurencyjne cte z datami
Przypomnienie - zależy to od ciągłych dat (bez przerw), do 10000 poziomów rekurencji oraz znajomości daty początkowej interesującego Cię zakresu (aby ustawić kotwicę). Oczywiście można dynamicznie ustawić kotwicę za pomocą podzapytania, ale chciałem, aby wszystko było proste.
;WITH g AS
(
SELECT [Day], c = COUNT(DISTINCT CustomerID)
FROM dbo.Hits
GROUP BY [Day]
), x AS
(
SELECT [Day], c, rt = c
FROM g
WHERE [Day] = '20120501'
UNION ALL
SELECT g.[Day], g.c, x.rt + g.c
FROM x INNER JOIN g
ON g.[Day] = DATEADD(DAY, 1, x.[Day])
)
SELECT [Day], c, rt
FROM x
ORDER BY [Day]
OPTION (MAXRECURSION 10000);
rekurencyjne cte z numer_wiersza
Obliczenie numer_wiersza jest tutaj nieco drogie. Ponownie obsługuje to maksymalny poziom rekurencji wynoszący 10000, ale nie trzeba przypisywać kotwicy.
;WITH g AS
(
SELECT [Day], rn = ROW_NUMBER() OVER (ORDER BY DAY),
c = COUNT(DISTINCT CustomerID)
FROM dbo.Hits
GROUP BY [Day]
), x AS
(
SELECT [Day], rn, c, rt = c
FROM g
WHERE rn = 1
UNION ALL
SELECT g.[Day], g.rn, g.c, x.rt + g.c
FROM x INNER JOIN g
ON g.rn = x.rn + 1
)
SELECT [Day], c, rt
FROM x
ORDER BY [Day]
OPTION (MAXRECURSION 10000);
rekurencyjny cte z tabelą temp
Kradzież z odpowiedzi Mikaela, zgodnie z sugestią, aby uwzględnić to w testach.
CREATE TABLE #Hits
(
rn INT PRIMARY KEY,
c INT,
[Day] SMALLDATETIME
);
INSERT INTO #Hits (rn, c, Day)
SELECT ROW_NUMBER() OVER (ORDER BY DAY),
COUNT(DISTINCT CustomerID),
[Day]
FROM dbo.Hits
GROUP BY [Day];
WITH x AS
(
SELECT [Day], rn, c, rt = c
FROM #Hits as c
WHERE rn = 1
UNION ALL
SELECT g.[Day], g.rn, g.c, x.rt + g.c
FROM x INNER JOIN #Hits as g
ON g.rn = x.rn + 1
)
SELECT [Day], c, rt
FROM x
ORDER BY [Day]
OPTION (MAXRECURSION 10000);
DROP TABLE #Hits;
dziwna aktualizacja
Ponownie włączam to tylko dla kompletności; Ja osobiście nie polegałbym na tym rozwiązaniu, ponieważ, jak wspomniałem w innej odpowiedzi, nie gwarantuje się, że ta metoda w ogóle zadziała i może całkowicie zepsuć się w przyszłej wersji SQL Server. (Dokładam wszelkich starań, aby zmusić program SQL Server do wykonania żądanej kolejności, korzystając ze wskazówek dotyczących wyboru indeksu).
CREATE TABLE #x([Day] SMALLDATETIME, c INT, rt INT);
CREATE UNIQUE CLUSTERED INDEX x ON #x([Day]);
INSERT #x([Day], c)
SELECT [Day], c = COUNT(DISTINCT CustomerID)
FROM dbo.Hits
GROUP BY [Day]
ORDER BY [Day];
DECLARE @rt1 INT;
SET @rt1 = 0;
UPDATE #x
SET @rt1 = rt = @rt1 + c
FROM #x WITH (INDEX = x);
SELECT [Day], c, rt FROM #x ORDER BY [Day];
DROP TABLE #x;
kursor
„Uwaga, są tu kursory! Kursory są złe! Powinieneś unikać kursorów za wszelką cenę!” Nie, to nie ja mówię, to tylko rzeczy, które dużo słyszę. Wbrew powszechnej opinii kursory są odpowiednie.
CREATE TABLE #x2([Day] SMALLDATETIME, c INT, rt INT);
CREATE UNIQUE CLUSTERED INDEX x ON #x2([Day]);
INSERT #x2([Day], c)
SELECT [Day], COUNT(DISTINCT CustomerID)
FROM dbo.Hits
GROUP BY [Day]
ORDER BY [Day];
DECLARE @rt2 INT, @d SMALLDATETIME, @c INT;
SET @rt2 = 0;
DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT [Day], c FROM #x2 ORDER BY [Day];
OPEN c;
FETCH NEXT FROM c INTO @d, @c;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @rt2 = @rt2 + @c;
UPDATE #x2 SET rt = @rt2 WHERE [Day] = @d;
FETCH NEXT FROM c INTO @d, @c;
END
SELECT [Day], c, rt FROM #x2 ORDER BY [Day];
DROP TABLE #x2;
SQL Server 2012
Jeśli korzystasz z najnowszej wersji programu SQL Server, ulepszenia funkcji okienkowania pozwalają nam łatwo obliczyć sumy bieżące bez wykładniczego kosztu samozłączenia (suma obliczana jest w jednym przebiegu), złożoność CTE (w tym wymaganie ciągłych wierszy dla lepszej wydajności CTE), nieobsługiwana dziwaczna aktualizacja i zabroniony kursor. Wystarczy uważać na różnicę pomiędzy użyciem RANGEi ROWS, lub nie określając w ogóle - tylko ROWSunika się szpulę na dysku, która będzie utrudniać wydajność znacznie inaczej.
;WITH g AS
(
SELECT [Day], c = COUNT(DISTINCT CustomerID)
FROM dbo.Hits
GROUP BY [Day]
)
SELECT g.[Day], c,
rt = SUM(c) OVER (ORDER BY [Day] ROWS UNBOUNDED PRECEDING)
FROM g
ORDER BY g.[Day];
porównania wydajności
Podjąłem każde podejście i zapakowałem je w partię, używając:
SELECT SYSUTCDATETIME();
GO
DBCC DROPCLEANBUFFERS;DBCC FREEPROCCACHE;
-- query here
GO 10
SELECT SYSUTCDATETIME();
Oto wyniki całkowitego czasu trwania w milisekundach (pamiętaj, że dotyczy to również poleceń DBCC za każdym razem):
method run 1 run 2
----------------------------- -------- --------
self-join 1296 ms 1357 ms -- "supported" non-SQL 2012 winner
recursive cte with dates 1655 ms 1516 ms
recursive cte with row_number 19747 ms 19630 ms
recursive cte with #temp table 1624 ms 1329 ms
quirky update 880 ms 1030 ms -- non-SQL 2012 winner
cursor 1962 ms 1850 ms
SQL Server 2012 847 ms 917 ms -- winner if SQL 2012 available
Zrobiłem to ponownie bez poleceń DBCC:
method run 1 run 2
----------------------------- -------- --------
self-join 1272 ms 1309 ms -- "supported" non-SQL 2012 winner
recursive cte with dates 1247 ms 1593 ms
recursive cte with row_number 18646 ms 18803 ms
recursive cte with #temp table 1340 ms 1564 ms
quirky update 1024 ms 1116 ms -- non-SQL 2012 winner
cursor 1969 ms 1835 ms
SQL Server 2012 600 ms 569 ms -- winner if SQL 2012 available
Usuwając zarówno DBCC, jak i pętle, mierząc tylko jedną surową iterację:
method run 1 run 2
----------------------------- -------- --------
self-join 313 ms 242 ms
recursive cte with dates 217 ms 217 ms
recursive cte with row_number 2114 ms 1976 ms
recursive cte with #temp table 83 ms 116 ms -- "supported" non-SQL 2012 winner
quirky update 86 ms 85 ms -- non-SQL 2012 winner
cursor 1060 ms 983 ms
SQL Server 2012 68 ms 40 ms -- winner if SQL 2012 available
Na koniec pomnożyłem liczbę wierszy w tabeli źródłowej przez 10 (zmieniając górę na 50000 i dodając kolejną tabelę jako połączenie krzyżowe). Rezultaty tego, jedna iteracja bez poleceń DBCC (po prostu w interesie czasu):
method run 1 run 2
----------------------------- -------- --------
self-join 2401 ms 2520 ms
recursive cte with dates 442 ms 473 ms
recursive cte with row_number 144548 ms 147716 ms
recursive cte with #temp table 245 ms 236 ms -- "supported" non-SQL 2012 winner
quirky update 150 ms 148 ms -- non-SQL 2012 winner
cursor 1453 ms 1395 ms
SQL Server 2012 131 ms 133 ms -- winner
Zmierzyłem tylko czas trwania - pozostawię to jako ćwiczenie dla czytelnika, aby porównać te podejścia na ich danych, porównując inne metryki, które mogą być ważne (lub mogą różnić się w zależności od schematu / danych). Zanim wyciągniesz wnioski z tej odpowiedzi, od ciebie zależy sprawdzenie jej danych i schematu ... wyniki te prawie na pewno zmienią się, gdy liczba wierszy będzie wyższa.
próbny
Dodałem sqlfiddle . Wyniki:
wniosek
W moich testach wybór byłby następujący:
- Metoda SQL Server 2012, jeśli mam dostępny SQL Server 2012.
- Jeśli SQL Server 2012 nie jest dostępny, a moje daty są ciągłe, wybrałbym metodę rekurencyjną cte z datami.
- Jeśli ani 1., ani 2. nie mają zastosowania, wybrałbym samosprzężenie nad dziwaczną aktualizacją, nawet jeśli wydajność była bliska, tylko dlatego, że zachowanie jest udokumentowane i zagwarantowane. Mniej martwię się o przyszłą kompatybilność, ponieważ mam nadzieję, że jeśli dziwna aktualizacja się zepsuje, nastąpi to po przekonwertowaniu całego mojego kodu na 1. :-)
Ale ponownie powinieneś przetestować je pod kątem swojego schematu i danych. Ponieważ był to wymyślony test ze stosunkowo małą liczbą rzędów, równie dobrze może to być pierd na wietrze. Przeprowadziłem inne testy z różnymi schematami i liczbą wierszy, a heurystyka wydajności była całkiem inna ... dlatego zadałem tyle pytań uzupełniających do twojego pierwotnego pytania.
AKTUALIZACJA
Napisałem o tym więcej na blogu:
Najlepsze podejścia do uruchamiania sum - zaktualizowane do SQL Server 2012
Dayklucz jest, a czy wartości są ciągłe?