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 #28

    Piotr Szymański

    Kategoria:

    Hejka! Zapraszam na skrót z minionych dwóch tygodni, który przyswoić możecie przy ciepłej herbatce w te mroczne, szare dni. W opublikowanym przez Google 14 listopada ostrzeżeniu wskazano kilka najważniejszych rodzajów oszustw internetowych. Uwagę zwrócono między na niebezpieczne techniki ataków typu cloaking, które nabierają nowego wymiaru dzięki wykorzystaniu sztucznej inteligencji. Cloaking polega na ukrywaniu przed użytkownikiem […]

  • News #27

    Piotr Szymański

    Kategoria:

    Hejka po dłuższej przerwie! Zaczynamy świeżym tematem. Raptem kilkanaście godzin temu do użytkowników trafiła, zapowiedziana 25 lipca, funkcja SearchGPT od OpenAI, umożliwiająca, w przeciwieństwie do tradycyjnych modeli językowych, na integrację z internetem w czasie rzeczywistym. SearchGPT ma dostęp do aktualnych informacji z sieci, co pozwala na udzielanie odpowiedzi opartych na najnowszych danych. Ponadto SearchGPT dostarcza […]

  • Upscaling obrazu przy użyciu AI (feat. denoise&sharpen)

    Piotr Szymański

    Kategoria:

    Hejson! Dzisiejsza konsumpcja mediów ma to do siebie, że odbywa się na 5-6 calowym ekranie telefonu. Ma też to do siebie, że zanim zdjęcie dotrze do Ciebie, to przejdzie przez 6 konwersacji na jedynym słusznym messengerze, zatem zostanie 6-cio krotnie skompresowane. W międzyczasie, jak będziecie mieli pecha, to jakiś wujek zrobi screena, zamiast zapisać zdjęcie […]

  • Zmiana tła Waszego zdjęcia

    Piotr Szymański

    Kategoria:

    Hej! Robimy bardzo dużo zdjęć, a co za tym idzie – wiele z nich jest niechlujnych, z zabałagnionym tłem. Możemy jednak chcieć wykorzystać je do pochwalenia się naszym ryjkiem na jakimś publicznym profilu, gdyż np. naturalne, miękkie światło korzystnie eksponuje naszą facjatę. Podejścia mogą być dwa – albo zdecydujemy się na blur bądź zupełne usunięcie […]

  • News #26

    Piotr Szymański

    Kategoria:

    Strzałeczka. Nvidia przejęła OctoAI, startup specjalizujący się w optymalizacji modeli uczenia maszynowego. To już piąta akwizycja Nvidii w 2024 roku, co czyni aktualnie nam panujący rok rekordowym pod względem liczby przejęć. OctoAI, założone w 2019 roku przez Luisa Ceze, skupiło się na tworzeniu oprogramowania zwiększającego wydajność modeli uczenia maszynowego na różnych platformach sprzętowych. Oprogramowanie OctoAI […]