Kasowanie W tym wpisie przeanalizuję problem kasowania powtarzających się rekordów.

Istnieje wiele rozwiązań tego problemu. Ja postanowiłem przedstawić trzy różne rozwiązania i porównać je w różnych scenariuszach. Sprawdziłem te trzy metody w dwóch różnych wypadkach.

W pierwszym przypadku mamy tabele niezindeksowaną, która posiada dwie kolumny. Pierwsza kolumna to identyfikator, druga natomiast zawiera losową wartość liczbową. Tabela ta zawiera 100.000 rekordów, które się powtarzają. Ze względu na losowość nie wiem dokładnie, ile jest powtarzających się rekordów?. Naszym zadaniem jest je usunąć.

W drugim wypadku mamy tabelę tymczasową z 4 kolumnami. Identyfikator, Imię, Nazwisko oraz Numer. Rekordów tabeli jest tylko 11. Nie są one generowane losowo więc wynik operacji zawsze będzie taki sam, bez względu na to, z jakiej metody skorzystamy. Wiemy też jawnie, ile rekordów się powtarza, w tym przypadku chcemy skasować rekordy, które mają takie same Imie i Nazwisko oraz Numer.

Oba wypadki zawierają kolumny NOT NULL oraz posiadają identyfikator auto-inkrementalny.

Pierwszy przypadek – losowa nie po indeksowana tabela

Oto proste zapytanie SQL, które stworzy tabelę tymczasową z losową wartością w jednej kolumnie. Po każdej próbie testowania kasowania trzeba tabelę skasować i utworzyć ją na nowo.

CREATE TABLE RandomRowsTable (Id INT IDENTITY, Value INT)
GO

DECLARE @a INT
SET @a = 1
WHILE @a < 100000 BEGIN
	INSERT INTO RandomRowsTable (Value) VALUES (CONVERT(INT,RAND() * 1000))
	SET @a = @a + 1
END
GO

Dla potwierdzenia istnienia powtarzających się rekordów wykonałem proste zapytanie zawierające klauzurę Group by.

SELECT count(Value),Value FROM  dbo.[RandomRowsTable] GROUP BY Value

Jak widać powtarzających się rekordów jest dosyć dużo, ich liczba wynosi ponad 100. Nie jest to zaskakujące, ponieważ stworzyliśmy 100.000 rekordów, które powinny zawierać liczby od 0 do 1000.

image

Najprostsza metoda kasująca nie wymaga znajomość dużej znajomości SQL.

DELETE t1
FROM dbo.RandomRowsTable AS t1, dbo.RandomRowsTable AS t2
WHERE t1.value = t2.value
AND t1.id>t2.id

SELECT count(Value),Value FROM  dbo.[RandomRowsTable] GROUP BY Value

Naszej jednej tabeli dajemy dwa różne aliasy ,aby można było odnieść do niej dwa razy w tym zapytaniu. Sprawdzamy czy wartość liczbowa jest równa wartości tej samej tabeli. W trakcie tego zapytania będzie ona skanowana dwa razy. Musimy jeszcze dodać warunek związany z identyfikatorem tabeli ,aby skasować tylko rekordy wyższe od tego jednego rekordu które nie chcemy skasować.

imagePlan zapytania tak jak przewidziałem zobrazował dwa razy przeskanowanie tej samej tabeli oraz później porównanie wyników..

imageJest to najprostsza metoda i jak można się spodziewać nie jest ona tak do końca wydajna.

Druga metoda wymaga użycia słów kluczowe “Over i Partition by” . Nie są one dostępne w każdym systemie bazo danowym.

;WITH Cez	
AS
(
   SELECT ROW_NUMBER() OVER (PARTITION BY Value 
                                       ORDER BY ( SELECT 0)) RN
        /*Msg 4112, Level 15, State 1, Line 2
	The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.
	*/                                       
         FROM  dbo.RandomRowsTable
)
         
DELETE FROM Cez
WHERE  RN > 1

SELECT count(Value),Value FROM  dbo.[RandomRowsTable] GROUP BY Value

Zapytanie tej jest dużo bardziej złożone niż poprzednie. Używając słowa “With” zachowuje zawartość zapytania, które grupuje powtarzające się wartości.

Rekord z większym row_number niż 1 określa powtarzający się rekord. Kasujemy więc rekordy gdzie ta wartość większa.

imageZapytanie te powinno być optymalne, ponieważ wykonuje się tylko jedno skanowanie tabeli. Większość pracy w zapytaniu została przeznaczona na sortowanie (85%). Tabela nie posiada żadnego indeksu więc naturalnie proces sortowania 100.000 rekordów jest dosyć bolesny w tym wypadku.

image

Ze statystki wynika ,że te zapytanie jest gorsze od poprzedniego ,ale jestem przekonany ,że umieszczenie indeksu dla kolumny “Value” przyspieszyłoby te zapytanie kilkukrotnie. Sprawiając ,że te rozwiązanie byłoby zwycięskie. Wymaga ono też najmniejszej ilości pamięci niż pozostałe rozwiązania.

Trzecie zapytanie korzysta z mechaniki LEFT OUTER JOIN.

DELETE dbo.RandomRowsTable
FROM dbo.RandomRowsTable
LEFT OUTER JOIN (
   SELECT MIN(Id) as Id, Value
   FROM  dbo.RandomRowsTable
   GROUP BY Value
) as KeepRows ON
   dbo.RandomRowsTable.Id = KeepRows.Id
WHERE
   KeepRows.Id IS NULL
   
SELECT count(Value),Value FROM  dbo.[RandomRowsTable] GROUP BY Value

Te rozwiązanie ponownie wymaga podwójnego przeskanowania tabeli. Rozwiązanie te powinno być najbardziej niewydajne.

image

Te rozwiązanie pochłonęło najwięcej pamięci i też najwięcej czasu. Martwi mnie jednak “Estimated Operator Cost” ,który jest najniższy ze wszystkich pozostałych zapytań.

image

Zastanawiałem się także czy liczba powtarzających się rekordów ma jakiś wpływ na szybkość zapytania ,ale różnica prawie nie istnieje.

Postanowiłem przeprowadzić jeszcze jeden przypadek, gdyż indeks, losowość rekordów, jak i liczba powtarzających się kolumn może wypływać na wynik tego testu.

Drugi przypadek – tabela tymczasowa

Drugi przypadek działa na tabeli tymczasowej z niewielką liczbą rekordów są one stałe dla każdej próby sprawdzenia moich zapytań więc tutaj nie powinno być problemu ze znalezieniem zwycięscy.

CREATE TABLE #RandomTable
(
ID INT not null identity(1,1) primary key,
Name VARCHAR(2048) NOT NULL,
SurName VARCHAR(4096) NOT NULL,
Number INT NOT NULL
) 

INSERT INTO #RandomTable (Name,SurName ,Number)
SELECT 'Adam', 'Pawłock', 10 UNION ALL
SELECT 'Adam', 'Pawłock', 10 UNION ALL
SELECT 'Adam', 'Pawłock', 10 UNION ALL
SELECT 'Adam', 'Pawłock', 10 UNION ALL
SELECT 'Franko', 'Myściciel', 15 UNION ALL
SELECT 'Franko', 'Myściciel', 15 UNION ALL
SELECT 'Franko', 'Myściciel', 15 UNION ALL
SELECT 'Doman', 'Psychoworldowski', 20 UNION ALL
SELECT 'Doman', 'Psychoworldowski', 20 UNION ALL
SELECT 'Doman', 'Psychoworldowski', 20 UNION ALL
SELECT 'Zdzisław', 'BohaterGalaktyki', 30 

DELETE t1
FROM #RandomTable AS t1, #RandomTable AS t2
WHERE t1.Name = t2.Name AND t1.SurName = t2.SurName AND t1.Number = t2.Number
AND t1.id>t2.id

SELECT *
FROM   #RandomTable

DROP TABLE #RandomTable

W planie zapytania mamy “Clustered Index Scan” co oznacza ,że tabele tymczasowe domyślnie są już za indeksowane co też wpłynie na rezultaty naszych zapytań.

image

Wciąż jednak tabela jest skanowana dwa razy co wpływa negatywnie na to zapytanie ,ale jest to też nie najgorszy wynik.

image

Czas na rozwiązanie OUTER LEFT JOIN.

DELETE #RandomTable
FROM #RandomTable
LEFT OUTER JOIN (
   SELECT MIN(Id) as Id, Name,SurName ,Number
   FROM  #RandomTable
   GROUP BY Name,SurName ,Number
) as KeepRows ON
   #RandomTable.Id = KeepRows.Id
WHERE
   KeepRows.Id IS NULL

Tutaj wyraźnie widać poważny spadek wydajności, jak i niesamowicie długi plan zapytania.

image

Liczba kolumn do sprawdzenie powtarzalność ma druzgocący wpływ na wynik tego zapytania. W tym wypadku jest ono najgorszym rozwiązaniem.

image

Na koniec zostało nam rozwiązanie z OVER PARTITION.

;WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY Name,SurName ,Number 
                                       ORDER BY ( SELECT 0)) RN                                    
         FROM   #RandomTable)
DELETE FROM cte
WHERE  RN > 1

Sortowanie zostało znacznie przyspieszone dzięki temu ,że tabela tymczasowa jest po indeksowana.

image

Ostatecznie to zapytanie zostaje zwycięzcą, gdyż ma ono najmniejszy wartość w “Estimated Subtree Cost”.

Co więcej, te zapytanie nie jest w żaden sposób zależne od klucza głównego tabeli. W tym wypadu mógłby on nawet nie istnieć.

Ilość kolumn do sprawdzenie nie ma prawie żadnego negatywnego skutku na to zapytanie.

image

Nie jestem specjalistą od baz danych ,ale chciałem chociaż raz pobawić się w profesjonalistę, który umie określić które zapytanie jest bardziej wydajne ,a które mniej. Jak widać nie jest to łatwe zadanie. Dlatego mam nadzieje ,że spodobał ci się ten wpis i z chęcią przyjmę miłą sugestie na temat optymalizacji tych zapytań.