Po pierwsze przepraszam za opóźnienie w odpowiedzi od moich ostatnich komentarzy.
Temat pojawił się w komentarzach, że użycie rekurencyjnego CTE (odtąd rCTE) działa wystarczająco szybko z powodu małej liczby wierszy. Choć może się tak wydawać, nic nie może być dalsze od prawdy.
ZBUDUJ TALLY TALLY I FUNKCJĘ TALLY
Zanim zaczniemy testowanie, musimy zbudować fizyczny zestaw tabel z odpowiednim indeksem klastrowym i funkcją Tally w stylu Itzika Ben-Gana. Zrobimy to wszystko w TempDB, aby przypadkowo nie upuścić nikogo.
Oto kod do zbudowania Tally Table i moja obecna produkcyjna wersja cudownego kodu Itzika.
--===== Do this in a nice, safe place that everyone has
USE tempdb
;
--===== Create/Recreate a Physical Tally Table
IF OBJECT_ID('dbo.Tally','U') IS NOT NULL
DROP TABLE dbo.Tally
;
-- Note that the ISNULL makes a NOT NULL column
SELECT TOP 1000001
N = ISNULL(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,0)
INTO dbo.Tally
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally PRIMARY KEY CLUSTERED (N)
;
--===== Create/Recreate a Tally Function
IF OBJECT_ID('dbo.fnTally','IF') IS NOT NULL
DROP FUNCTION dbo.fnTally
;
GO
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Purpose:
Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Trillion.
As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.
Usage:
--===== Syntax example (Returns BIGINT)
SELECT t.N
FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;
Notes:
1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
Refer to the following URLs for how it works and introduction for how it replaces certain loops.
http://www.sqlservercentral.com/articles/T-SQL/62867/
http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type
will cause the sequence to start at 1.
3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
5. If @MaxN is negative or NULL, a "TOP" error will be returned.
6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger
number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with
that many values, you should consider using a different tool. ;-)
7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending
sort is required, use code similar to the following. Performance will decrease by about 27% but it's still
very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.
DECLARE @MaxN BIGINT;
SELECT @MaxN = 1000;
SELECT DescendingN = @MaxN-N+1
FROM dbo.fnTally(1,@MaxN);
8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
Revision History:
Rev 00 - Unknown - Jeff Moden
- Initial creation with error handling for @MaxN.
Rev 01 - 09 Feb 2013 - Jeff Moden
- Modified to start at 0 or 1.
Rev 02 - 16 May 2013 - Jeff Moden
- Removed error handling for @MaxN because of exceptional cases.
Rev 03 - 22 Apr 2015 - Jeff Moden
- Modify to handle 1 Trillion rows for experimental purposes.
**********************************************************************************************************************/
(@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1) --10E1 or 10 rows
, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --10E4 or 10 Thousand rows
,E12(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c) --10E12 or 1 Trillion rows
SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.
UNION ALL
SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E12 -- Values from 1 to @MaxN
;
GO
Nawiasem mówiąc ... zauważ, że zbudowałeś Tally Table z milionem i jednym wierszem i dodał do niego Indeks klastrowany w około sekundę. Wypróbuj TO z rCTE i zobacz, jak długo to potrwa! ;-)
ZBUDUJ NIEKTÓRE DANE TESTOWE
Potrzebujemy również danych testowych. Tak, zgadzam się, że wszystkie funkcje, które zamierzamy przetestować, w tym rCTE, działają w milisekundach lub krócej dla zaledwie 12 wierszy, ale to pułapka, w którą wpada wiele osób. Później porozmawiamy o tej pułapce, ale na razie pozwala symulować wywoływanie każdej funkcji 40 000 razy, czyli o tym, ile razy niektóre funkcje w moim sklepie są wywoływane w ciągu 8 godzin. Wyobraź sobie, ile razy takie funkcje mogą być wywoływane w dużym sklepie detalicznym online.
Oto kod do zbudowania 40 000 wierszy z losowymi datami, z których każdy ma numer wiersza wyłącznie do celów śledzenia. Nie poświęciłem czasu, żeby robić godziny całymi godzinami, bo to nie ma znaczenia.
--===== Do this in a nice, safe place that everyone has
USE tempdb
;
--===== Create/Recreate a Test Date table
IF OBJECT_ID('dbo.TestDate','U') IS NOT NULL
DROP TABLE dbo.TestDate
;
DECLARE @StartDate DATETIME
,@EndDate DATETIME
,@Rows INT
;
SELECT @StartDate = '2010' --Inclusive
,@EndDate = '2020' --Exclusive
,@Rows = 40000 --Enough to simulate an 8 hour day where I work
;
SELECT RowNum = IDENTITY(INT,1,1)
,SomeDateTime = RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,@StartDate,@EndDate)+@StartDate
INTO dbo.TestDate
FROM dbo.fnTally(1,@Rows)
;
ZBUDUJ NIEKTÓRE FUNKCJE, ABY WYKONAĆ RZECZ GODZINY 12-GODZINNEJ
Następnie przekonwertowałem kod rCTE na funkcję i utworzyłem 3 inne funkcje. Wszystkie zostały utworzone jako wysokowydajne iTVF (funkcje cenione w tabeli wbudowanej). Zawsze możesz powiedzieć, ponieważ iTVF nigdy nie mają w sobie POCZĄTKU, jak Scalar lub mTVF (Multi-instrukcja Table Valued Functions).
Oto kod do zbudowania tych 4 funkcji ... Nazwałem je po metodzie, której używają, a nie po tym, co robią, aby ułatwić ich identyfikację.
--===== CREATE THE iTVFs
--===== Do this in a nice, safe place that everyone has
USE tempdb
;
-----------------------------------------------------------------------------------------
IF OBJECT_ID('dbo.OriginalrCTE','IF') IS NOT NULL
DROP FUNCTION dbo.OriginalrCTE
;
GO
CREATE FUNCTION dbo.OriginalrCTE
(@Date DATETIME)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH Dates AS
(
SELECT DATEPART(HOUR,DATEADD(HOUR,-1,@Date)) [Hour],
DATEADD(HOUR,-1,@Date) [Date], 1 Num
UNION ALL
SELECT DATEPART(HOUR,DATEADD(HOUR,-1,[Date])),
DATEADD(HOUR,-1,[Date]), Num+1
FROM Dates
WHERE Num <= 11
)
SELECT [Hour], [Date]
FROM Dates
GO
-----------------------------------------------------------------------------------------
IF OBJECT_ID('dbo.MicroTally','IF') IS NOT NULL
DROP FUNCTION dbo.MicroTally
;
GO
CREATE FUNCTION dbo.MicroTally
(@Date DATETIME)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT [Hour] = DATEPART(HOUR,DATEADD(HOUR,t.N,@Date))
,[DATE] = DATEADD(HOUR,t.N,@Date)
FROM (VALUES (-1),(-2),(-3),(-4),(-5),(-6),(-7),(-8),(-9),(-10),(-11),(-12))t(N)
;
GO
-----------------------------------------------------------------------------------------
IF OBJECT_ID('dbo.PhysicalTally','IF') IS NOT NULL
DROP FUNCTION dbo.PhysicalTally
;
GO
CREATE FUNCTION dbo.PhysicalTally
(@Date DATETIME)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT [Hour] = DATEPART(HOUR,DATEADD(HOUR,-t.N,@Date))
,[DATE] = DATEADD(HOUR,-t.N,@Date)
FROM dbo.Tally t
WHERE N BETWEEN 1 AND 12
;
GO
-----------------------------------------------------------------------------------------
IF OBJECT_ID('dbo.TallyFunction','IF') IS NOT NULL
DROP FUNCTION dbo.TallyFunction
;
GO
CREATE FUNCTION dbo.TallyFunction
(@Date DATETIME)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT [Hour] = DATEPART(HOUR,DATEADD(HOUR,-t.N,@Date))
,[DATE] = DATEADD(HOUR,-t.N,@Date)
FROM dbo.fnTally(1,12) t
;
GO
ZBUDUJ WIĄZKĘ TESTOWĄ, ABY TESTOWAĆ FUNKCJE
Na koniec potrzebujemy uprzęży testowej. Sprawdzam linię bazową, a następnie testuję każdą funkcję w identyczny sposób.
Oto kod uprzęży testowej ...
PRINT '--========== Baseline Select =================================';
DECLARE @Hour INT, @Date DATETIME
;
SET STATISTICS TIME,IO ON;
SELECT @Hour = RowNum
,@Date = SomeDateTime
FROM dbo.TestDate
CROSS APPLY dbo.fnTally(1,12);
SET STATISTICS TIME,IO OFF;
GO
PRINT '--========== Orginal Recursive CTE ===========================';
DECLARE @Hour INT, @Date DATETIME
;
SET STATISTICS TIME,IO ON;
SELECT @Hour = fn.[Hour]
,@Date = fn.[Date]
FROM dbo.TestDate td
CROSS APPLY dbo.OriginalrCTE(td.SomeDateTime) fn;
SET STATISTICS TIME,IO OFF;
GO
PRINT '--========== Dedicated Micro-Tally Table =====================';
DECLARE @Hour INT, @Date DATETIME
;
SET STATISTICS TIME,IO ON;
SELECT @Hour = fn.[Hour]
,@Date = fn.[Date]
FROM dbo.TestDate td
CROSS APPLY dbo.MicroTally(td.SomeDateTime) fn;
SET STATISTICS TIME,IO OFF;
GO
PRINT'--========== Physical Tally Table =============================';
DECLARE @Hour INT, @Date DATETIME
;
SET STATISTICS TIME,IO ON;
SELECT @Hour = fn.[Hour]
,@Date = fn.[Date]
FROM dbo.TestDate td
CROSS APPLY dbo.PhysicalTally(td.SomeDateTime) fn;
SET STATISTICS TIME,IO OFF;
GO
PRINT'--========== Tally Function ===================================';
DECLARE @Hour INT, @Date DATETIME
;
SET STATISTICS TIME,IO ON;
SELECT @Hour = fn.[Hour]
,@Date = fn.[Date]
FROM dbo.TestDate td
CROSS APPLY dbo.TallyFunction(td.SomeDateTime) fn;
SET STATISTICS TIME,IO OFF;
GO
W powyższej uprzęży testowej należy zauważyć, że przetaczam wszystkie dane wyjściowe do zmiennych „wyrzucanych”. Chodzi o to, aby pomiary wydajności były jak najczystsze, bez żadnych wyników wypaczania dysku lub ekranu.
SŁOWO OSTRZEŻENIE DOTYCZĄCE USTAWIONEJ STATYSTYKI
Również ostrzeżenie dla przyszłych testerów ... NIE WOLNO używać USTAWIEŃ STATYSTYKI podczas testowania funkcji skalarnych lub mTVF. Można go bezpiecznie używać tylko w funkcjach iTVF, takich jak te w tym teście. Udowodniono, że SET STATISTICS sprawia, że funkcje SCALAR działają setki razy wolniej niż w rzeczywistości bez nich. Tak, próbuję przechylić kolejny wiatrak, ale to byłby cały „szalony post o długości artykułów i nie mam na to czasu. Mam artykuł na stronie SQLServerCentral.com, który mówi o tym wszystkim, ale nie ma sensu zamieszczać tutaj linku, ponieważ ktoś może się na to zgiąć.
WYNIKI TESTU
Oto wyniki testu, gdy uruchamiam test uprzęży na moim małym laptopie i5 z 6 GB pamięci RAM.
--========== Baseline Select =================================
Table 'Worktable'. Scan count 1, logical reads 82309, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestDate'. Scan count 1, logical reads 105, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 206 ms.
--========== Orginal Recursive CTE ===========================
Table 'Worktable'. Scan count 40001, logical reads 2960000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestDate'. Scan count 1, logical reads 105, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 4258 ms, elapsed time = 4415 ms.
--========== Dedicated Micro-Tally Table =====================
Table 'Worktable'. Scan count 1, logical reads 81989, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestDate'. Scan count 1, logical reads 105, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 234 ms, elapsed time = 235 ms.
--========== Physical Tally Table =============================
Table 'Worktable'. Scan count 1, logical reads 81989, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestDate'. Scan count 1, logical reads 105, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tally'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 252 ms.
--========== Tally Function ===================================
Table 'Worktable'. Scan count 1, logical reads 81989, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestDate'. Scan count 1, logical reads 105, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 253 ms.
„WYBÓR PODSTAWY”, który wybiera tylko dane (każdy wiersz utworzony 12 razy, aby zasymulować ten sam wolumen zwrotu), pojawił się dokładnie w 1/5 sekundy. Cała reszta pojawiła się w około kwadrans. Cóż, wszystko oprócz tej cholernej funkcji rCTE. Zajęło to 4 i 1/4 sekundy lub 16 razy dłużej (1600% wolniej).
I spójrz na logiczne odczyty (pamięć IO) ... rCTE pochłonął ogromną liczbę 2 960 000 (prawie 3 MILIONY odczytów), podczas gdy inne funkcje zużyły tylko około 82 100. Oznacza to, że rCTE zużył ponad 34,3 razy więcej IO pamięci niż jakakolwiek inna funkcja.
MYŚLI ZAMKNIĘTE
Podsumujmy. Metoda rCTE do wykonania tej „małej” 12-rzędowej rzeczy wykorzystała 16 TIMES (1600%) więcej procesora (i czasu trwania) i 34,3 TIMES (3430%) więcej IO pamięci niż jakakolwiek inna funkcja.
Heh ... Wiem co myślisz. „Wielka sprawa! To tylko jedna funkcja”.
Tak, zgadzam się, ale ile masz innych funkcji? Ile masz innych miejsc poza funkcjami? A czy masz jakieś z tych, które działają z więcej niż 12 rzędami w każdym przebiegu? I czy jest jakaś szansa, że ktoś, kto szuka metody, może skopiować ten kod rCTE na coś znacznie większego?
Ok, czas być tępy. Nie ma absolutnie żadnego sensu, aby ludzie uzasadniali kod pod względem wydajności tylko z powodu rzekomej ograniczonej liczby wierszy lub użycia. Z wyjątkiem sytuacji, gdy kupujesz skrzynkę MPP za być może miliony dolarów (nie wspominając o kosztach przepisywania kodu, aby działał na takiej maszynie), nie możesz kupić maszyny, która uruchamia kod 16 razy szybciej (SSD wygrał też tego nie robię ... wszystkie te rzeczy były w wysokiej pamięci, kiedy to testowaliśmy). Wydajność jest w kodzie. Dobra wydajność jest w dobrym kodzie.
Czy potrafisz sobie wyobrazić, czy cały kod działał „tylko” 16 razy szybciej?
Nigdy nie usprawiedliwiaj złego lub kwestionowanego wydajności kodu przy małej liczbie wierszy, a nawet przy niskim zużyciu. Jeśli to zrobisz, być może będziesz musiał pożyczyć jeden z wiatraków, o który byłem oskarżony o przechylanie, aby utrzymać wystarczająco chłodne procesory i dyski. ;-)
SŁOWO W SŁOWIE „TALLY”
Tak! Zgadzam się. Semantycznie rzecz biorąc, tablica wyników zawiera liczby, a nie „liczby”. W moim oryginalnym artykule na ten temat (nie był to oryginalny artykuł na temat techniki, ale był to mój pierwszy), nazwałem go „Tally” nie ze względu na to, co zawiera, ale z powodu tego, co robi… używane do „liczenia” zamiast zapętlania i „liczenia” czegoś to „liczenie” czegoś. ;-) Nazwij to, co chcesz ... Tabela liczb, tabela liczników, tabela sekwencji, cokolwiek. Nie obchodzi mnie to. Dla mnie „Tally” ma bardziej pełne znaczenie, a będąc dobrym leniwym DBA, zawiera tylko 5 liter (2 są identyczne) zamiast 7 i łatwiej jest powiedzieć dla większości ludzi. Jest to również „liczba pojedyncza”, która jest zgodna z moją konwencją nazewnictwa dla tabel. ;-) To' jest to również, jak nazwał to artykuł, który zawierał stronę z książki z lat 60. Zawsze będę nazywał to „Tally Table” i nadal będziesz wiedział, co ja lub ktoś inny znaczy. Unikam też notacji węgierskiej jak zarazy, ale nazwałem funkcję „fnTally”, abym mógł powiedzieć „Cóż, gdybyś użył efektywnej funkcji Tally, którą ci pokazałem, nie miałbyś problemu z wydajnością”, gdyby nie była Naruszenie HR. ;-) bez faktycznego naruszenia HR. ;-) bez faktycznego naruszenia HR. ;-)
Bardziej martwi mnie to, że ludzie uczą się korzystać z niego właściwie, zamiast uciekać się do rzeczy takich jak rCTE podważające wydajność i inne formy ukrytego RBAR.