W swoim pytaniu szczegółowo opisujesz niektóre przygotowane testy, w których „udowadniasz”, że opcja dodawania jest szybsza niż porównywanie odrębnych kolumn. Podejrzewam, że twoja metodologia testów może być wadliwa na kilka sposobów, o czym wspominali @gbn i @srutzky.
Po pierwsze, musisz upewnić się, że nie testujesz SQL Server Management Studio (lub innego używanego klienta). Na przykład, jeśli korzystasz SELECT *z tabeli zawierającej 3 miliony wierszy, najczęściej testujesz zdolność SSMS do pobierania wierszy z SQL Server i renderowania ich na ekranie. O wiele lepiej jest użyć czegoś takiego, SELECT COUNT(1)co neguje potrzebę ciągnięcia milionów wierszy w sieci i renderowania ich na ekranie.
Po drugie, musisz pamiętać o pamięci podręcznej danych programu SQL Server. Zazwyczaj testujemy szybkość odczytu danych z pamięci i przetwarzania tych danych z zimnej pamięci podręcznej (tzn. Bufory SQL Server są puste). Czasami sensowne jest przeprowadzanie wszystkich testów przy użyciu ciepłej pamięci podręcznej, ale należy do nich podchodzić wyraźnie.
W przypadku testu pamięci podręcznej na zimno należy uruchomić CHECKPOINTi DBCC DROPCLEANBUFFERSprzed każdym uruchomieniem testu.
Dla testu, o który pytałeś w swoim pytaniu, stworzyłem następujące łóżko testowe:
IF COALESCE(OBJECT_ID('tempdb..#SomeTest'), 0) <> 0
BEGIN
DROP TABLE #SomeTest;
END
CREATE TABLE #SomeTest
(
TestID INT NOT NULL
PRIMARY KEY
IDENTITY(1,1)
, A INT NOT NULL
, B FLOAT NOT NULL
, C MONEY NOT NULL
, D BIGINT NOT NULL
);
INSERT INTO #SomeTest (A, B, C, D)
SELECT o1.object_id, o2.object_id, o3.object_id, o4.object_id
FROM sys.objects o1
, sys.objects o2
, sys.objects o3
, sys.objects o4;
SELECT COUNT(1)
FROM #SomeTest;
Zwraca liczbę 260 144 641 na moim komputerze.
Aby przetestować metodę „dodawania”, uruchamiam:
CHECKPOINT 5;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS IO, TIME ON;
GO
SELECT COUNT(1)
FROM #SomeTest st
WHERE (st.A + st.B + st.C + st.D) = 0;
GO
SET STATISTICS IO, TIME OFF;
Karta wiadomości pokazuje:
Tabela „#SomeTest”. Liczba skanów 3, odczyty logiczne 1322661, odczyty fizyczne 0, odczyty odczytu z wyprzedzeniem 1313877, odczyty logiczne odczytywania 0, odczyty fizyczne odczytywania 0, odczyty odczytywania odczytywania 0.
Czasy wykonania programu SQL Server: czas procesora = 49047 ms, czas, który upłynął = 173451 ms.
Dla testu „kolumn dyskretnych”:
CHECKPOINT 5;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS IO, TIME ON;
GO
SELECT COUNT(1)
FROM #SomeTest st
WHERE st.A = 0
AND st.B = 0
AND st.C = 0
AND st.D = 0;
GO
SET STATISTICS IO, TIME OFF;
ponownie z zakładki wiadomości:
Tabela „#SomeTest”. Liczba skanów 3, logiczne odczyty 1322661, fizyczne odczyty 0, odczyt z wyprzedzeniem 1322661, logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty 0.
Czasy wykonania programu SQL Server: czas procesora = 8938 ms, czas, który upłynął = 162581 ms.
Z powyższych statystyk widać drugi wariant, z dyskretnymi kolumnami w porównaniu do 0, czas, który upłynął, jest o około 10 sekund krótszy, a czas procesora jest około 6 razy krótszy. Długie czasy trwania powyższych testów wynikają głównie z odczytu wielu wierszy z dysku. Jeśli zmniejszysz liczbę wierszy do 3 milionów, proporcje pozostaną mniej więcej takie same, ale czasy, które upłynęły, zauważalnie spadają, ponieważ dyskowe operacje we / wy mają znacznie mniejszy wpływ.
Za pomocą metody „dodawania”:
Tabela „#SomeTest”. Liczba skanów 3, logiczne odczyty 15255, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.
Czasy wykonania programu SQL Server: czas procesora = 499 ms, czas, który upłynął = 256 ms.
Dzięki metodzie „dyskretnych kolumn”:
Tabela „#SomeTest”. Liczba skanów 3, logiczne odczyty 15255, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.
Czasy wykonania programu SQL Server: czas procesora = 94 ms, czas, który upłynął = 53 ms.
Co zrobi naprawdę dużą różnicę w tym teście? Odpowiedni indeks, taki jak:
CREATE INDEX IX_SomeTest ON #SomeTest(A, B, C, D);
Metoda „dodawania”:
Tabela „#SomeTest”. Liczba skanów 3, logiczne odczyty 14235, fizyczne odczyty 0, odczytywanie z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.
Czasy wykonania SQL Server: czas procesora = 546 ms, czas, który upłynął = 314 ms.
Metoda „dyskretnych kolumn”:
Tabela „#SomeTest”. Liczba skanów 1, logiczne odczyty 3, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.
Czasy wykonania programu SQL Server: czas procesora = 0 ms, czas, który upłynął = 0 ms.
Plan wykonania każdego zapytania (z powyższym indeksem na miejscu) jest dość wymowny.
Metoda „dodawania”, która musi wykonać skanowanie całego indeksu:
a metoda „dyskretnych kolumn”, która może wyszukiwać do pierwszego wiersza indeksu, w którym wiodąca kolumna indeksu A, wynosi zero:
