Příklady optimalizace SQL dotazů

Popis problému

Máme tabulku StoreCards se sloupcem X_Znaky100 typu (N)VARCHAR(100), který je indexován. I přesto dotazy nad tímto sloupcem mohou být pomalé, pokud se používají transformační funkce jako UPPER, SUBSTRING nebo TRIM, které brání využití indexu.

Chybné konstrukce

-- Firebird
SELECT ID, X_Znaky100 FROM StoreCards WHERE UPPER(X_Znaky100) LIKE 'VELKÁ%';
SELECT ID, X_Znaky100 FROM StoreCards WHERE SubString(X_Znaky100 FROM 1 FOR 5) = 'VELKÁ';

-- MSSQL
SELECT ID, X_Znaky100 FROM StoreCards WHERE SubString(X_Znaky100, 1, 5) = 'VELKÁ';

-- Oracle
SELECT ID, X_Znaky100 FROM StoreCards WHERE SubStr(X_Znaky100, 1, 5) = 'VELKÁ';

-- TRIM (obecně)
SELECT ID, X_Znaky100 FROM StoreCards WHERE TRIM(X_Znaky100) = 'TEXT';

Důsledky

Index nad sloupcem X_Znaky100 není využit, dojde ke skenování celé tabulky (PLAN NATURAL). Dotaz trvá jednotky vteřin podle velikosti tabulky.

Optimální řešení

Pokud je databáze nastavena na porovnání bez rozlišení velikosti písmen (CASE INSENSITIVE), není nutné používat UPPER. Pro textové porovnání používejte přímé porovnání nebo LIKE bez funkčních transformací.

SELECT ID, X_Znaky100 FROM StoreCards WHERE X_Znaky100 LIKE 'VeLkÁ%';
SELECT ID, X_Znaky100 FROM StoreCards WHERE X_Znaky100 = 'teXT';

Popis problému

Dotazy nad neindexovanými sloupci (např. ACCDATE$DATE) způsobují skenování celé tabulky a mohou trvat i několik vteřin.

SELECT A.ID FROM ISSUEDINVOICES A
WHERE A.ACCDATE$DATE > 45828;

Optimální řešení

Využit jiný sloupec, který je indexován, nebo doplnit index do databáze.

SELECT A.ID FROM ISSUEDINVOICES A
WHERE A.DOCDATE$DATE > 45828;

Popis problému

Použití subdotazů ve WHERE má na Firebirdu negativní vliv na optimalizační plán, což vede k neefektivním JOINům.

SELECT DISTINCT II.OrdNumber FROM ISSUEDINVOICES II
JOIN ISSUEDINVOICES2 II2 ON II.ID = II2.Parent_ID
WHERE II2.StoreCard_ID IN (
		SELECT ID FROM StoreCards WHERE Name LIKE 'Trubka%'
	);

Optimální řešení

Převrátit dotaz tak, aby výchozí tabulka byla co nejvíce omezená pomocí indexu, a provést JOIN až poté.

SELECT DISTINCT II.OrdNumber FROM StoreCards SC
JOIN ISSUEDINVOICES2 II2 ON II2.StoreCard_ID = SC.ID
JOIN ISSUEDINVOICES II ON II.ID = II2.PARENT_ID
WHERE SC.Name LIKE 'Trubka';

I když tento problém nenastává na databázích Oracle a MSSQL, lze vždy doporučit postupovat od nejvíce omezující tabulky k ostatním.

Popis problému

Použití výrazů typu sloupec1 || '-' || sloupec2 || '/' || sloupec3 v WHERE podmínce znemožňuje databázi využít indexy nad jednotlivými sloupci, i když by pro hledání šlo použít více indexů. Výraz s operátorem || (konkatenace) nutí databázi počítat hodnotu za běhu pro každý řádek, což zásadně zpomaluje dotaz. Výsledek je sken celé tabulky nebo suboptimální JOIN plán.

Chybná konstrukce

-- doba trvání i jednotky sekund
SELECT A.ID
FROM ReceivedOrders A
LEFT JOIN Periods P ON P.id = A.Period_ID
LEFT JOIN DocQueues DQ ON DQ.ID = A.DocQueue_ID
WHERE DQ.Code || '-' || A.OrdNumber || '/' || P.Code = 'SL-15/2025'
-- PLAN JOIN (JOIN (A NATURAL, P INDEX (PERIODSPK)), DQ INDEX (DOCQUEUESPK))

Důsledky

Databáze není schopna použít indexy na DQ.Code, A.OrdNumber ani P.Code. Výsledkem je pomalý dotaz - trvání v řádu sekund podle velikosti tabulky.

Optimální řešení

Porovnávat jednotlivé sloupce zvlášť, čímž databáze využije dostupné indexy a dotaz je řádově rychlejší.

-- doba trvání jednotky milisekund
SELECT A.ID
FROM ReceivedOrders A
LEFT JOIN Periods P ON P.id = A.Period_ID
LEFT JOIN DocQueues DQ ON DQ.ID = A.DocQueue_ID
WHERE DQ.Code = 'SL' AND A.OrdNumber = 15 AND P.Code = '2025'
-- PLAN JOIN (JOIN (A INDEX (RECEIVEDORDERS_ON), P INDEX (PERIODSPK)), DQ INDEX (DOCQUEUESPK))

Poznámka

Obecně platí, že skládání podmínek přes || (nebo CONCAT) je v SQL téměř vždy neefektivní z pohledu výkonu. Pokud potřebujete složený identifikátor (např. pro zobrazení), skládejte jej až v aplikaci nebo ve výsledku dotazu, ale ne v podmínce WHERE.

Popis problému

Na databázi Firebird má optimalizátor potíže správně naplánovat dotaz, který v části WHERE obsahuje IN (SELECT ...) nad cizím klíčem s malým počtem výsledků. Typicky vzniká neefektivní plán s NATURAL skenem hlavní tabulky a dotaz je zbytečně pomalý.

Chybná konstrukce

SELECT
    SUM(
        FV.LOCALAMOUNT + FV.LOCALPAIDCREDITAMOUNT - FV.LOCALPAIDAMOUNT - FV.LOCALCREDITAMOUNT
    ) AS FV_NEZAPLACENO
FROM
    ISSUEDINVOICES FV
WHERE
    (
        FV.LOCALAMOUNT + FV.LOCALPAIDCREDITAMOUNT - FV.LOCALPAIDAMOUNT - FV.LOCALCREDITAMOUNT
    ) > 0
    AND (
        FV.FIRM_ID = '9900000101'
        OR FV.FIRM_ID IN (
            SELECT ID FROM FIRMS WHERE FIRM_ID = '9900000101'
        )
    )
-- PLAN (FIRMS INDEX (FIRMSPK)), PLAN (FV NATURAL)

Důsledky

Takto konstruovaný dotaz často trvá stovky ms i na malých datech.

Optimální řešení

Načtěte nejprve relevantní ID firem a poté proveďte JOIN s tabulkou faktur. Firebird pak využije indexy a dotaz je řádově rychlejší (jednotky ms):

SELECT
    SUM(
        FV.LOCALAMOUNT + FV.LOCALPAIDCREDITAMOUNT - FV.LOCALPAIDAMOUNT - FV.LOCALCREDITAMOUNT
    ) AS FV_NEZAPLACENO
FROM FIRMS F
JOIN ISSUEDINVOICES FV ON F.ID = FV.FIRM_ID
WHERE
    (
        FV.LOCALAMOUNT + FV.LOCALPAIDCREDITAMOUNT - FV.LOCALPAIDAMOUNT - FV.LOCALCREDITAMOUNT
    ) > 0
    AND (
        F.ID = '9900000101'
        OR F.FIRM_ID = '9900000101'
    )
-- PLAN JOIN (F INDEX (FIRMSPK, FIRMSFIRMFK), FV INDEX (ISSUEDINVOICESFIRMFK))

Poznámka

Tento problém se týká zejména databáze Firebird – optimalizátor zde neumí rozpoznat, že IN (SELECT ...) vrací konstantní a malý počet hodnot a plánuje dotaz zcela neefektivně. Na databázích MSSQL a Oracle problém obvykle nevzniká. Vždy preferujte JOIN před vnořeným IN v případě cizích klíčů.