The Data Game

About all data related stuff

Zagadka: Znajdź najdłuższe wartości tekstowe w tabeli.

Że co?

Ostatnio Brent Ozar miał bardzo ciekawy livestream odnośnie znajdowania najdłuższych wartości tekstowych w kolumnach w tabeli. W tym celu użył tabeli dbo.Users z kopii bazy StackOverflow i chciał znaleźć po dwie najdłuższe wartości z kolumn DisplayName, Location oraz WebsiteUrl. My spróbujemy zrobić to samo i porównać różne podejścia.

Testy zostaną przeprowadzone na bazie “Medium” z tego linku: >>KLIK<<

Zaczynamy!

Na start trochę informacji o środowisku:

  • SQL Server 2019 Developer Edition z ustawionym “COMPATIBILITY LEVEL” na bazie na 130 (SQL 2016) jako, że jest to obecnie najpopularniejsza wersja w użyciu produkcyjnym
  • 16 GB RAM do dyspozycji serwera, 4 rdzenie (8 wątków)
  • domyślny Cost Threshold for Parallelism
  • używam także SET STATISTICS TIME, IO ON aby mieć informację o odczytach i ich czasie

Surowe statystyki będę wrzucał na pastebin i podawał linki pod obrazkami.

Pierwsze podejście będzie bardzo toporne i niezbyt wydajne, ale zawsze jakiś start. Po prostu w WHERE użyjemy trzech różnych wariantów IN w zależności od tego jakie “topki” chcemy otrzymać:

SELECT
    *
FROM
    [dbo].[Users]
WHERE
    [Id] IN (SELECT TOP(2) [Id] FROM [dbo].[Users] ORDER BY LEN([DisplayName]) DESC)
OR
    [Id] IN (SELECT TOP(2) [Id] FROM [dbo].[Users] ORDER BY LEN([Location]) DESC)
OR
    [Id] IN (SELECT TOP(2) [Id] FROM [dbo].[Users] ORDER BY LEN([WebsiteUrl]) DESC)

Do parsowania statystyk użyję świetnej strony https://statisticsparser.com/

https://pastebin.com/pCyhrLVp

A oto plan zapytania:

Jak widać dokonaliśmy odczytu ponad 130 tysięcy stron, czytaliśmy tabelę Users trzy razy w pełni (Clustered Index Scan z prawej strony zapytania) i dodatkowo jeszcze 6 odwołań bezpośrednich do sześciu wierszy aby zaprezentować wyniki. Całość zajęła procesorowi prawie 6 sekund (5672 ms, nie licząc czasu parsowania), a w rzeczywistości minęła ponad sekunda (jakim cudem minęło mniej czasu niż czas zajęty na procesorze? Równoległość zapytania). Not good, not terrible.

Kolejne rozwiązanie to utworzenie tabeli tymczasowej, która będzie przetrzymywać tylko Id wiersza z tabeli i długości poszczególnych kolumn. Wypróbujmy!

DROP TABLE IF EXISTS #tempOne;
CREATE TABLE #tempOne ([Id] INT, [DisLen] INT, [LocLen] INT, [WebLen] INT);

INSERT INTO #tempOne ([Id],[DisLen],[LocLen],[WebLen])
SELECT
    [Id],
    LEN([DisplayName]) as [DisLen],
    LEN([Location]) as [LocLen],
    LEN([WebsiteUrl]) as [WebLen]
FROM
    [dbo].[Users];

SELECT
    *
FROM
    [dbo].[Users]
WHERE
    [Id] IN (SELECT TOP(2) [Id] FROM #tempOne ORDER BY [DisLen] DESC)
OR
    [Id] IN (SELECT TOP(2) [Id] FROM #tempOne ORDER BY [LocLen] DESC)
OR
    [Id] IN (SELECT TOP(2) [Id] FROM #tempOne ORDER BY [Weblen] DESC);
https://pastebin.com/Bmw9DREe

Ay, coś tu nie gra… Dlaczego insert do tabeli tymczasowej poszedł na jednym wątku? Trzeba to naprawić!

DROP TABLE IF EXISTS #tempOne;
CREATE TABLE #tempOne ([Id] INT, [DisLen] INT, [LocLen] INT, [WebLen] INT);

INSERT INTO #tempOne WITH (TABLOCKX) ([Id],[DisLen],[LocLen],[WebLen]) 
SELECT
    [Id],
    LEN([DisplayName]) as [DisLen],
    LEN([Location]) as [LocLen],
    LEN([WebsiteUrl]) as [WebLen]
FROM
    [dbo].[Users];

SELECT
    *
FROM
    [dbo].[Users]
WHERE
    [Id] IN (SELECT TOP(2) [Id] FROM #tempOne ORDER BY [DisLen] DESC)
OR
    [Id] IN (SELECT TOP(2) [Id] FROM #tempOne ORDER BY [LocLen] DESC)
OR
    [Id] IN (SELECT TOP(2) [Id] FROM #tempOne ORDER BY [Weblen] DESC);
https://pastebin.com/Dq332RAm

Ojoj… Czas procesora uległ znacznemu wydłużeniu, bo sięgamy już prawie 8 sekund, ale za to czas wykonania jest o wiele lepszy niż z jednowątkowym insertem, ale dalej prawie sekundę dłuższy niż poprzednie rozwiązanie. Odczyty za to spadły o ponad połowę, z 135,5 tys na 68,1 tys. To jest solidny zysk.

Ale dlaczego mamy mieć skanować stertę? Może indeksy coś pomogą? Sprawdźmy!

DROP TABLE IF EXISTS #tempOne;
CREATE TABLE #tempOne ([Id] INT PRIMARY KEY, [DisLen] INT, [LocLen] INT, [WebLen] INT,
INDEX [IX_apathy] ([DisLen]),
INDEX [IX_void] ([LocLen]),
INDEX [IX_depression] ([WebLen]));

INSERT INTO #tempOne WITH (TABLOCKX) ([Id],[DisLen],[LocLen],[WebLen]) 
SELECT
    [Id],
    LEN([DisplayName]) as [DisLen],
    LEN([Location]) as [LocLen],
    LEN([WebsiteUrl]) as [WebLen]
FROM
    [dbo].[Users];

SELECT
    *
FROM
    [dbo].[Users]
WHERE
    [Id] IN (SELECT TOP(2) [Id] FROM #tempOne ORDER BY [DisLen] DESC)
OR
    [Id] IN (SELECT TOP(2) [Id] FROM #tempOne ORDER BY [LocLen] DESC)
OR
    [Id] IN (SELECT TOP(2) [Id] FROM #tempOne ORDER BY [Weblen] DESC);
https://pastebin.com/L5EkWJGq

Auuuuuuuuuuć… Samo otrzymanie wyników końcowych było niesamowicie szybkie, ale wrzucanie danych do tabeli tymczasowej? Koszmar… PONAD 7 MLN ODCZYTÓW! Nie wracajmy do tego…

Okej, ale to były klasycznie indeksy rowstore. Co jakbyśmy użyli wspaniałego gracza jakim jest indeks typu columnstore, który pozwala na wykonywanie operacji w trybie batch, czyli na wielu wierszach na raz!

DROP TABLE IF EXISTS #tempOne;
CREATE TABLE #tempOne ([Id] INT, [DisLen] INT, [LocLen] INT, [WebLen] INT,
INDEX [IX_alcoholism] CLUSTERED COLUMNSTORE);

INSERT INTO #tempOne WITH (TABLOCKX) ([Id],[DisLen],[LocLen],[WebLen]) 
SELECT
    [Id],
    LEN([DisplayName]) as [DisLen],
    LEN([Location]) as [LocLen],
    LEN([WebsiteUrl]) as [WebLen]
FROM
    [dbo].[Users];

SELECT
    *
FROM
    [dbo].[Users]
WHERE
    [Id] IN (SELECT TOP(2) [Id] FROM #tempOne ORDER BY [DisLen] DESC)
OR
    [Id] IN (SELECT TOP(2) [Id] FROM #tempOne ORDER BY [LocLen] DESC)
OR
    [Id] IN (SELECT TOP(2) [Id] FROM #tempOne ORDER BY [Weblen] DESC);
https://pastebin.com/RtkGRMtM

Dalej nie jest różowo, głównie z uwagi na wrzucanie danych do tabeli tymczasowej, ale już samo otrzymanie wyników to niecałe 100 ms, a liczba odczytów znowu spadła nam do poziomu 45 tysięcy. Ale to dalej nie to czego oczekujemy… Przecież nasze pierwsze zapytanie, bez żadnych udziwnień, zajęło około sekundy, a tutaj mamy ponad dwie!

Czy da radę połączyć jednokrotny odczyt tabeli z otrzymaniem wyników dla trzech różnych podzbiorów?

Funkcje okna!

Wypróbujmy funkcję okna ROW_NUMBER i CTE! To musi się udać!

WITH CTE AS (
SELECT
    [Id],
    ROW_NUMBER() OVER (ORDER BY LEN([DisplayName]) DESC) as [DisLen],
    ROW_NUMBER() OVER (ORDER BY LEN([Location]) DESC) as [LocLen],
    ROW_NUMBER() OVER (ORDER BY LEN([WebsiteUrl]) DESC) as [WebLen]
FROM
    dbo.Users
)

SELECT
    b.*
FROM
    CTE a
JOIN
    [dbo].[Users] b on a.[Id] = b.[Id]
WHERE
    a.[DisLen] < 3
OR
    a.[LocLen] < 3
OR
    a.[WebLen] < 3
https://pastebin.com/TDsxfZfV
Aż mi się plan nie zmieścił w całości!

PORAŻKA! Miało być szybko! Miało być pięknie! A co dostaliśmy? 90 tys odczytów na przestrzeni 20 sekund CPU (!!!), a całość zajęła ponad 7 sekund czasu rzeczywistego! PORAŻKA!

Ale popuśćmy wodze fantazji… Co jakby połączyć tryb batch od indeksu typu columnstore z CTE? Niestety, SQL Server 2016 nie obsługuje operacji batch na danych rowstore. Ale SQL Server 2019 już tak! Zobaczmy jakby to działało! Najpierw ustawmy kompatybilność bazy na poziomie 150 (SQL Server 2019)

ALTER DATABASE [StackOverflow2013]
SET COMPATIBILITY_LEVEL = 150;
GO

I teraz jeszcze raz wypróbujmy poprzednie zapytanie, bez żadnych zmian.

https://pastebin.com/zFJ77GzV

Miodzio! Odczyty na bardzo dobrym poziomie, czas wykonania również, tabelę w całości uderzyliśmy tylko raz. Gdyby tylko dało się taki sam wynik uzyskać w wersji 2016…

Hokus pokus, czary mary!

Wróćmy do wersji 2016.

ALTER DATABASE [StackOverflow2013]
SET COMPATIBILITY_LEVEL = 130;
GO

I dokonajmy jednej zmiany w naszym zapytaniu.

DROP TABLE IF EXISTS #tmpColumnstore;
CREATE TABLE #tmpColumnstore ([A] INT, INDEX [IX_loneliness] CLUSTERED COLUMNSTORE);
INSERT INTO #tmpColumnstore VALUES (1);

;WITH CTE AS (
SELECT
    [Id],
    ROW_NUMBER() OVER (ORDER BY LEN([DisplayName]) DESC) as [DisLen],
    ROW_NUMBER() OVER (ORDER BY LEN([Location]) DESC) as [LocLen],
    ROW_NUMBER() OVER (ORDER BY LEN([WebsiteUrl]) DESC) as [WebLen]
FROM
    dbo.Users
)

SELECT
    b.*
FROM
    CTE a
JOIN
    [dbo].[Users] b on a.[Id] = b.[Id]
CROSS JOIN
    #tmpColumnstore
WHERE
    a.[DisLen] < 3
OR
    a.[LocLen] < 3
OR
    a.[WebLen] < 3;
https://pastebin.com/3Fp35ujG

Jest lepiej niż poprzednio na wersji 2016, ale dalej nie tak super jak na wersji 2019… Dwa razy bijemy w tabelę Users i jeszcze nie korzystamy z dobrodziejstwa równoległości… Peszkie.

Zostaje nam jeszcze z ciekawości sprawdzić ostatnie rozwiązanie.

DROP TABLE IF EXISTS #tmpColumnstore;
CREATE TABLE #tmpColumnstore ([A] BIGINT, INDEX [IX_loneliness] CLUSTERED COLUMNSTORE);
INSERT INTO #tmpColumnstore VALUES (1);

SELECT
    *
FROM
    [dbo].[Users]
CROSS JOIN
    #tmpColumnstore
WHERE
    [Id] IN (SELECT TOP(2) [Id] FROM [dbo].[Users] ORDER BY LEN([DisplayName]) DESC)
OR
    [Id] IN (SELECT TOP(2) [Id] FROM [dbo].[Users] ORDER BY LEN([Location]) DESC)
OR
    [Id] IN (SELECT TOP(2) [Id] FROM [dbo].[Users] ORDER BY LEN([WebsiteUrl]) DESC)
https://pastebin.com/AzJtBsJK

Dużo odczytów, dużo. Ale prędkość wykonania?

Podsumowanie

zostawiam Tobie, czytelniku.

Oki, to pa!

Zobacz także:

  • News #15

    Piotr Szymański

    Kategoria:

    Hejka naklejka! Google przeprowadza restrukturyzację swojego zespołu finansowego, kładąc większy nacisk na inwestycje w sztuczną inteligencję. Zmiany, o których poinformowała główna dyrektor finansowa Google, Ruth Porat, obejmują zwolnienia oraz przeniesienia pracowników i są odpowiedzią na spowolnienie wzrostu przychodów z reklam, jednocześnie stawiając na rosnącą rolę AI. CFO zaznaczyła, że restrukturyzacja jest częścią szerszej strategii przystosowania […]

  • News #14

    Piotr Szymański

    Kategoria:

    Cześć! Zapraszam do podsumowania minionych 2 tygodni, wiadomości, które zaciekawiły mnie podczas surfowania po sieci telekomunikacyjnej Internet. Sekretarz Sił Powietrznych USA Frank Kendall ogłosił plany lotu myśliwcem F-16 sterowanym przez sztuczną inteligencję. Próba ma odbyć się wiosną tego roku i ma na celu zaprezentowanie potencjału sztucznej inteligencji w walce powietrznej, podkreślając przejście wojska w kierunku […]

  • News #13

    Piotr Szymański

    Kategoria:

    Hejka naklejka! Świat jest podzielony. Jak zawsze, można by rzec. Wiecznie żywa reguła „dziel i rządź”, można by rzec. Świat podzielony na zwolenników sernika z rodzynkami, jak i bez rodzynków; wielbicieli sałatki jarzynowej jak i ludzi pozbawionych gustu; ludzi jeżdżących BMW, jak i masochistów, i tak dalej, i tym podobne. Przychodzę tu jednak ponad pewnymi […]

  • News #12

    Piotr Szymański

    Kategoria:

    Siemson sportowe świry! Dumni po zwycięstwie, wierni po porażce. Tym razem jednak bez wstydu, hańby i kompromitacji. Nie podejrzewam naszego sztabu szkoleniowego o klasyczną inspirację rozwiązaniem stosowanym w Liverpoolu, natomiast nie miał bym nic przeciwko. We współpracy z Google DeepMind powstał bowiem TacticAI, model predykcyjny wspomagający wysiłki trenerów. Już w 2021 potrafił zgadnąć gdzie piłkarz […]

  • News #11

    Piotr Szymański

    Kategoria:

    Hæ vinir! Dinozaury. Kto z nas nie byłby na pewnym etapie życia zafascynowany tymi uroczymi gadami. Nawet nie wiecie, ile Nas z nimi łączy! Nie tylko tak samo jesteśmy owodniowcami, ale i wyginiemy. Może nawet całkiem niedługo. Mrocznie się zrobiło, nie? Ale może zakończymy jakimś przyjemnym akcentem, więc bądźcie nastrojeni. Raport, stworzony przez Gladstone AI […]