Vítejte u poměrně hodně technicky zaměřeného článku – o tom jak optimalizovat a urychlit složité SQL selecty.
Na optimalizaci často přichází řada až nakonec, až když je vlastně pozdě, je ale dobré na rychlost myslet už na začátku.
Následující rady, a postupy jsou myslím použitelné mnohem šířeji než jen pro konkrétní databázi. Minimálně se budu snažit, o to aby to bylo použitelné jak pro MSSQL, tak pro Oracle, myslím však, že většina z nich najde uplatnění v jakékoliv oblasti práce s daty.
Základní rady
Začneme trochu obecněji
- Vždy na začátku pište kód co nejjednodušeji – rozpadněte si logicky co chcete udělat s daty, a to napište do SQL kódu
- Snažte se používat joiny
- Snažte se nepoužívat složité join podmínky (k tomuto se dostaneme později)
- Snažte se nepoužívat vnořené selecty – tyto selecty jednak výrazně zhoršují přehlednost (nahraďte je pomocí with nebo temp tabulkou) a jednak nejsou na výkon to nejlepší. Výjimku tvoří vyselektování proměných – a ty následně správně ohintovat (například pomoci /*+materialize */, nebo využití ve where podmínce pomocí column_name in (select … )
- Pokud je kód zbytečně dlouhý tak, že už se v něm nevyznáte, rozhodně ho rozdělte na více částí, klidně i na více view. Toto sice nevyřeší rychlost přímo, ale rozhodně to vyřeší rychlost úprav.
- Veškeré hacky oproti základnímu jednoduchému původnímu logickému návrhu komentujte!!
- Většina optimalizace je často vedena metodou pokus-omyl, přizpůsobte tomu i práci – vytvořte si menší objekty které můžete lehce a rychle selectovat, klidně i temp tabulky které ve výsledku nepoužijete. (Mimochodem pokud jako zdroj máte například nějaké view – nebo jen select, který sám o sobě běží dlouho, a nic s tím nemůžete udělat – zmaterializujte si ho pro ladění do tabulky, kterou pak vyměníte za původní zdroj, případně si z něj zmaterializujte jen pár řádek. Jen to nezapomeňte vrátit zpět )
- Používejte common sense (ano, většinou je problém tam, kde je nejvíce dat, atp.)
Execution plan
Exekuční plán by měl být první místo, kam se podívat, pokud začneme řešit performance problém v SQL. Zde už začíná celkem alchymie. Co ale v exekučním plánu hledat? Pokud používáte MSSQL Management Studio první nápovědu mužete hledat přímo v hlavičce – občas vám rovnou napoví kam například dát index (a rovnou vygeneruje i kód kterým ho vytvoříte). A musím říci, že většinou tyto nápovědy zafungovaly velmi dobře – minimálně označí označí místo, kde může být problém.
Na co se tedy zaměřit?
- Cost
- Neested loops
První věcí, na kterou se zaměřte jsou řádky s největším cost, a případně na ty kde objevíte nečekaně vysokou cost. Tato hodnota obvykle kopíruje velikosti zdrojových tabulek, ale pokud z tabulek vybíráte jen pár řádek neměla by být příliš závratná. Zvláště, pokud z dané tabulky vybíráte jen jednu, nebo pár řádků, a hodnota cost je vysoká, rozhodně materializujte.
Další velmi důležitou věcí jsou neested loops. Zvláště v kombinaci s větším počtem řádků jsou smrtící. Vždy se snažte všechny neested loop odstranit! Znamenají to, že tento join/podmínka se bude vyhodnocovat pro všechny řádky cílové tabulky. Někdy stačí přidat index, případně hint (na vytvoření “virtuálního” indexu), někdy je ale potřeba přepsat celý join. Velmi často se totiž objevuje všude tam, kde do join podmínky použijete jakoukoliv funkci. Funkce se obvykle vyhodnocuje předáním hodnoty – jednak se musí vzít právě tato hodnota sloupce (nelze tedy předávat hash/index) a ovykle není známo jaký výsledek funkce pro různé vstupní hodnoty bude mít – tudíž se musí vyhodnotit pro všechny řádky. Jeden z příkladů:
to_char( date_col, ‘YYYY-MM-DD’ ) = ‘2015-01-01’
je mnohem lepší převézt na:
date_col = to_date(‘2015-01-01’, ‘YYYY-MM-DD’)
Může se to zdát ojedinělé, ale opravdu velmi často je právě použití funkce v join/where podmínkách velmi náročné – první výraz se musí vyhodnocovat pro každou řádku, ten druhý jen jednou a může jít přímo na daný sloupec (a pokud je zde navíc index….). Mimochodem indexy se někdy hodí i na sloupce, přes které vyhledáváte Ale ne vždy jsou indexy spásou a tím nejlepším řešením – zabírají poměrně dost místa, a jejich údržba taky není zadarmo, někdy stačí hint)
Stejně tak složitější logiku podmínky, především pokud obsahují různé OR výrazy, zkuste přepsat do podoby s CASE WHEN klauzulí a AND podmínkami. (toto možná rozvedu příště)
Materializace
Materializaci jsme již nakousli výše. Osobně mám právě materializaci velmi rád. Často stačí komplikované selecty rozdělit na dvě části. Vyzkoušejte různá rozdělení, mě osobně dobře funguje vzít tu nejsložitější logiku – ať už jde o joiny, případně omezení/where podmínky a materializovat ji – třeba materializovat jen indexovaný sloupec klíče základní tabulky – ušetří to paměť. Zvláště v oblastech DWH, kdy selectujete z fact tabulky, a potřebujete poměrně složitou logikou vybrat konkrétní řádky a k nim najoinovat všechny potřebné dimenze. Zkuste nejdříve vyselectovat jen primární klíče řádků faktové tabulky řádků které vás zajímají a ty materializujte. Případně s nimi cizí klíče do dimenzí přes které jste dělali výběr. Až pak připojte ostatní slupečky a různé navěšené dimenze.
Další věcí co se hodí materializovat jsou jakékoliv trochu složitější selecty, které vracejí třeba jen pár řádků. Dejte si je do with klauzule a zmaterializujte.
Postup, aneb jak na to
Jak se ale do té materializace pustit? Jak jsem psal dříve, prvním bodem je exekuční plán. Dalším bodem může být rozdělení selectu na jednotlivé části. Takový univerzální postup může být:
- Zakomentujte všechny selectované sloupce a místo nich tam dejte jen count(*)
- Postupně zakomentovávejte jednotlivé/případně i více joinovaných tabulek a where podmínek
- Najděte ty joiny/where podmínky které trvají nejdéle – resp. najděte si co největší rozsah joinů a where podmínek který funguje rychle.
- Tuto část si zkuste materializovat a až následně připojit problematický join – vždy zjistěte v čem je problém – jestli v tom, že se joinuje moc dat, případně v které ‘ON’ nebo ‘WHERE’ podmínce
- zkuste různé hinty – materialize, use_hash, use_nl – z exekučního plánu by mělo být poznat který se má použít, ale metoda pokus-omyl zde funguje.
- zkuste přepsat join/where podmínky (OR -> case when, funkce na správnou stranu podmínky, …)
Joiny a datové typy
Jedno takové hezke pravidlo – nejlépe a nejrychleji se joinují INT hodnoty. Jednou jsem měl jednoduchý select, kdy jsem pro řadu záznamů podle jejich IP adresy dohledával lokaci podle rozsahu IP adres. Tedy něco jako:
select * from DATA d join IP_LOCAITONS l on d.IP_COL between l.IP_FROM to l.IP_TO
Výsledek – neuvěřitelně pomalé (opravdu jsem byl velmi překvapený jak pomalé to bylo). Řešením bylo převézt si IP adresy jak ve zdrojové tabulce tak v tabulce lokací do INT formátu, oindexovat, a teprve poté zkusit join. (mimochodem zde lze použít další hack – že použijete jen jednu část podmínky např. IP_COL_INT >= IP_FROM_INT a vzít jen první řádek.)
Hacky a zajímavosti
Někdy se v Oracle objeví zajímavé chování – někdy stačí vyměnit INNER JOIN za LEFT OUTER JOIN s tím že do WHERE podmínky napíšete podmínku na dohledání i pravé části. Najednou je z nedobíhajícího selectu stačení času na 10 sec
V průběhu času, ještě doplním další hacky a zajímavosti.
Závěr
Závěrem bych rád zopakoval některé věci: neested loops, materializace, pokus-omyl a hlavně vždy přehledný kód! Přeji hezké ladění.
