Pchełki SQL, odcinek 5: jak nie liczyć drzew

https://xpil.eu/y7Z2Y

Dziś króciutka przypowieść o tym jak to Bazyl rekordy zliczał.

Na początek kilka kwerend, które sobie siedziały w bazie od wieków. Posłużę się uproszczeniem w postaci drzew i liści. Faktyczne pająki, którymi się muszę posługiwać, są na oko ze sto razy bardziej zawiłe.

Szczegóły dotyczące wszystkich drzew:

SELECT * FROM DRZEWO;

Szczegóły odnośnie wszystkich liści i wszystkich drzew mających liście:

SELECT *
 FROM LISC L
 JOIN DRZEWO D
 ON L.DRZEWO_ID = D.DRZEWO_ID;

Ile liści ma każde z drzew (mających liście):

SELECT 
 COUNT(*) ILE_LISCI,
 D.NAZWA_DRZEWA
FROM LISC L
JOIN DRZEWO D
 ON L.DRZEWO_ID = D.DRZEWO_ID
GROUP BY D.NAZWA_DRZEWA;

Ile jest drzew:

SELECT 
 COUNT(DISTINCT NAZWA_DRZEWA) ILE_DRZEW 
FROM
 (
 SELECT 
 COUNT(*) ILE_LISCI,
 D.NAZWA_DRZEWA
 FROM LISC L
 JOIN DRZEWO D
 ON L.DRZEWO_ID = D.DRZEWO_ID
 GROUP BY D.NAZWA_DRZEWA
 ) Q;

Czy ktoś zauważył coś nieprawidłowego w ostatnim zapytaniu?

Podpowiem: próbujemy policzyć ilość pszczół w ulu licząc nogi i dzieląc przez sześć...

Powyższy kod jest dobrym (choć bardzo uproszczonym) przykładem tego, jak można się zagalopować w budowaniu podzapytań SQL. Przecież skoro wszystkie dotychczasowe zapytania działają optymalnie, można ich używać jako podzapytań do kolejnych kwerend, prawda?

No właśnie, nieprawda. Jeżeli kolejne zapytania sa ogólniejsze od istniejących, możemy dojść do takich właśnie potworków jak powyżej.

A piszę o tym, ponieważ od trzech dni zmagam się z bardzo podobną sytuacją w pracy. Z tym, że zamiast pięciu linijek mam setki linii kodu SQL, wijącego się na wszystkie strony niczym spaghetti bolognese.

Long story short: zapytanie, które powinno zająć nie więcej niż minutę, do wczoraj wykonywało mi się w półtora godziny. Przez trzy dni optymalizowałem indeksy, typy danych, filtry... Zszedłem do czterdziestu minut. Dopiero dziś odkryłem nieoptymalne (i całkiem bezsensowne) podzapytanie generujące jakieś grube miliony rekordów, których potem w ogóle się nie używa. Wypie... wyrzuciłem je z kodu i czas wykonania spadł mi nagle do 45 sekund. Hosanna!

Wniosek: nie ufaj nikomu, nawet własnym podkwerendom.

Wniosek 2: żadna optymalizacja nie pomoże, jeżeli nie rozumiemy danych.

Wniosek 3: Gdyby to był Microsoft SQL Server, wyłapałbym to dużo szybciej. Tam bowiem plany zapytań pokazują nie tylko koszt, ale również ilość potencjalnych rekordów biorących udział w każdej operacji. A w Oracle widzę tylko koszt, co utrudnia wyłapanie takich "kwiatków" w rozległych zapytaniach.

Wniosek 4: mniej wniosków!

https://xpil.eu/y7Z2Y

2 komentarze

  1. nie "Ile liści ma każde z drzew (mających liście):" a "Ile liści ma każdy z gatunków drzew [definiowany przez nazwę]". Chyba, że jako dobry pasterz [drzew] nazywasz każde po imieniu.

    1. Jako dobry pasterz drzew, nie zaglądam innym pasterzom przez ramię jak nazywają swoje drzewa 🙂

      Oczywiście masz rację, milczące założenie było takie, że każde drzewo ma unikalne imię (nazwę).

Leave a Comment

Komentarze mile widziane.

Jeżeli chcesz do komentarza wstawić kod, użyj składni:
[code]
tutaj wstaw swój kod
[/code]

Jeżeli zrobisz literówkę lub zmienisz zdanie, możesz edytować komentarz po jego zatwierdzeniu.