Optimalizace na straně serveru

DB Optimizer

Při práci s jazykem SQL se většinou soustředíme především na faktickou správnost, tedy aby jednotlivé příkazy vykonávaly to, co je od nich očekáváno a výsledkové sady obsahovaly právě jen data, která obsahovat mají. Syntaktická správnost příkazů je samozřejmostí, protože ji jednak ověřuje databázový stroj a většina vývojářů již dnes používá některý z mnoha SQL editorů, které podporují grafický návrh, inteligentní dokončování příkazů, automaticky nabízí existující databázové objekty nebo generují uložené procedury.
Co již není takovou samozřejmostí, je optimalizace SQL kódu z pohledu výkonu (doby odezvy). Přitom právě rychlost, s jakou aplikace reaguje na požadavky uživatele, často zásadně ovlivňuje uživatelský komfort.
Nejen pro vývojáře pracující s Delphi nebo C++ Builderem nabízí společnost Embarcadero nástroj DB Optimizer. K dispozici je zde nabídka tří základních scénářů, podle kterých lze při optimalizaci databázových operací postupovat.

Ladění jednotlivých SQL příkazů

Tento přístup umožňuje odstranit řadu výkonnostních problémů již v průběhu vývoje. Každý příkaz, který napíšeme nebo vygenerujeme, lze v prostředí DB Optimizeru okamžitě optimalizovat.

DB Optimizer potřebuje mít pro svou práci možnost komunikovat s příslušným databázovým strojem. Nejprve je tak třeba definovat parametry připojení. Kromě obvyklých atributů jako je adresa, port, jméno databáze nebo přístupové údaje můžeme nastavit i řadu pokročilých vlastností. Jednotlivé datové zdroje lze kategorizovat. Uživatel tak může být například okamžitě vyrozuměn o tom, že se připojil nikoliv k testovací, ale k produkční databázi.

Po připojení k DB můžeme zahájit vlastní ladění. Z hlavní nabídky otevřeme novou úlohu ("Tuning Job") a zadáme SQL pro ladění. Můžeme jej zadat přímo, načíst ze souboru nebo extrahovat z připojené databáze.

DB Optimizer načte určené příkazy do seznamu. Zkontrolujeme, že jsou zatrženy volby "Generate cases" a "Perform detail analysis". Nyní klinutím na ikonu "Run Job" zahájit vlastní analýzu.

Příkazy jsou podrobeny výkonnostní analýze a DB Optimizer navrhne možné alternativy spolu s podrobnou statistikou týkající se vypočtených nákladů na jejich zpracování. DB Optimizer tvoří jednotlivé varianty jak změnami ve vlastním SQL kódu, tak použitím různých kombinací indexů či nastavení optimalizeru za pomoci takzvaných "Hintů". Všechny možnosti jsou přehledně zobrazeny od nejrychlejší po nejpomalejší.

Hint - Způsob provedení příkazu určuje optimalizer, který je součástí databázového stroje. Velký vliv na konkrétní "strategii" mají statistiky (viz níže). Hint je parametr použitý v SQL kódu, který umožňuje serveru "vnutit" požadované chování. Tuto možnost je třeba používat velmi obezřetně.

Po tom, co si ze seznamu vybereme alternativu, s kterou chceme dále pracovat a kliknutím na tlačítko "Analysis" zobrazíme její detailní analýzu. K dispozici máme text příkazu, grafický rozbor jeho exekučního plánu včetně vlivu existujících i zatím nevytvořených indexů.

Exekuční plán - informace v textové nebo grafické podobě vysvětlující jak bude (nebo byl) realizován příkaz odesílaný databázovému stroji. Typicky obsahuje jednotlivé kroky (operace), způsob jejich provedení a počet dotčených záznamů. Podrobnější plány obsahují také informace o očekávaných nákladech (costs). Ty se týkají I/O operací, zatížení CPU, způsobu přístupu k datům (TABLE/INDEX SCAN, SEEK) či operátorů spojení (LOOP, NESTED LOOP, MERGE, HASH) a dalších charakteristik v závislosti na konkrétním typu databázového stroje.

Indexy, které by měli na rychlost zpracování pozitivní vliv lze vytvořit přímo z prostředí DB Optimizeru.

Přesnost analýzy je samozřejmě závislá na aktuálnosti dostupných statistik. Ty si jak na úrovni tabulek tak sloupců můžeme nejen zobrazit a procházet, ale přímo si také vynutit jejich aktualizaci na straně serveru.

Databázové statistiky - Jedná se o informace o povaze uložených dat. Patří sem například informace typu maximální/minimální hodnoty, denzita, selektivita, kardinalita či přehled distribuce hodnot (histogram). Metadata tohoto typu se mohou velmi dynamicky měnit a je tak třeba je udržovat co možná nejaktuálnější.

Profilování zátěže databázového serveru

V reálné světě má uživatel aplikace jen zřídka databázový server k dispozici jen sám pro sebe. V konkurenčním prostředí se mohou i příkazy, které mají při izolovaném spouštění rychlou odezvu, ukázat jako problematické. Do hry zde totiž vstupují i další vlivy jako jsou zamykání, propustnost sítě a podobně.
Profilování lze provádět během vývoje a testování aplikace, velmi užitečné ale může být i pro odhalení výkonostních problémů při jejím nasazování a provozu.

Při profilování se sleduje a ukládá řada výkonnostních charakteristik.

Výstupy lze následně filtrovat podle kritérií jako je název aplikace, jméno uživatele, typu příkazu a řady dalších.

Generování zátěže

V případě, že ladíme stávající aplikaci, postarají se o zátěž reální uživatelé. Profileru pak stačí pouze zadat časové rozmezí, v kterém má sbírat potřebné výkonnostní metriky. U testování aplikací, které nasazení do provozu teprve čeká, je třeba zátěž nějakým způsobem generovat. DBOptimizer umožňuje připravit soubory SQL příkazů a ty následně paralelně spouštět.
Nastavit lze počet paralelních vláken, požadovaný počet opakování, ale i od kdy a po jak dlouhou dobu má být zátěž generována.

Narozdíl od některých konkurenčních nástrojů není třeba mít pro každý databázový stroj jiný produkt, ale DB různých dodavatelů lze optimalizovat je jediného prostředí. DB Optimizer podporuje databázové stroje

• Oracle 9i, 10g, 11g a 12c
• Sybase ASE 12.5 – 15.7
• IBM DB2 LUW 9-9.7
• Microsoft SQL Server 2005, 2008 a 2012

View Petr Houf's profile

FastCube - Analytický reporting

FastCube2 for VCL

Spolu s novou verzí vývojových nástrojů společnosti Embarcadero získávají vývojáři jako bonus také produkt FastCube. Jedná se sadu VCL komponent, která umožňuje vývojářům doplnit své aplikace o funkce základní datové analýzy.
Na rozdíl od klasických výstupních sestav, které jsou statické, umožňuje FastCube uživatelům analyzovat závislost zkoumané veličiny na nejrůznějších faktorech jako je místo, čas atd. FastCube poskytuje intuitivní rozhraní pro dynamickou konfiguraci sestavy, rozpad ("drill down") agregovaných dat, filtrování nebo použití jednoduchých kalkulací.

Datová kostka

FastCube umí pracovat jak s daty z databázového stroje, tak s daty uloženými v souboru. Pro složitější analýzy je vhodné si data "upravit".
Struktura dat používaná běžnými provozními systémy je totiž optimalizovaná především pro zabezpečení nekonfliktního zpracování vysokého počtu obchodních operací pro často velké množství současně pracujících uživatelů.
Datová kostka (Cube) je specifická datová struktura, která naopak za cenu redundance dat zjednodušuje a také urychluje analytické "dotazování". Můžeme si ji zjednodušeně představit jako tabulku, která obsahuje každou změnu sledované metriky (faktu) a hodnotu pro každý parametr (dimenzi). Dimenze mohou být dále členěny pro práci s agregovanými údaji (hierarchie).

Výsledná datová kostka může vypadat následovně:

Demo aplikace

Na následujícím příkladu si ukážeme vytvoření jednoduchého formuláře pro analýzu dat za použití komponent FastCube. Na formulář umístíme následující komponenty:

fcxCube - udržuje v paměti datovou sadu reprezentující datovou kostku.

fcxCubeGrid - Datová mřížka pro zobrazení záznamů datové kostky.

fcxCubeGridToolbar - Nástrojová lišta pro práci s datovou kostkou. Bez jediného řádku kódu zpřístupňuje všechny základní funkce jako uložení dat do souboru, načtení dat, tvorbu řezů (Slicing), Filtrování apod. 

fcxSlice - Datová sada pro definovaný řez datovou kostkou. 

fcxSliceGrid - Specializovaná datová mřížka pro řezy. Kromě vlastního zobrazení umožňuje také vizuální návrh řezu nad definovanou datovou kostkou.

fcxSliceGridToolbar - Nástrojová lišta pro řezy datovou kostkou.

fcxDefaultSettings - Dialog pro uživatelská nastavení základních parametrů FastCube, jako je formátování dat nebo nastavení datových mřížek.

fcxDataSource - Komponenta pro nastavení datového zdroje.

fcxDBDataSet - Umožňuje namapovat datové sady definované pomocí standardních technologií pro připojení k databázím.

Nyní aplikaci "oživíme" nastavením příslušných vlastností jednotlivých komponent. Základní propojení zachycuje následující schéma. V tomto konkrétním případě byla pro připojení k DB použita technologie FireDAC (žlutě označené). FastCube ale podporuje i DBExpress, IBExpress nebo DBGo. 

Práce s aplikací

Po zkompilování aplikace (nebyl použit jediný řádek kódu!) můžeme začít s "analýzou" dat. Na ukázku práce s rozhraním FastCube se můžete podívat zde:

 

Spolu s FastCube lze použít komponenty TeeChart pro ještě lepší vizualizaci dat.

View Petr Houf's profile

Optimalizace komunikace s DB - Kurzory

Komunikaci mezi aplikací a databázovým serverem je možné do značné míry optimalizovat. Technologie FireDAC zpřístupňuje množství parametrů, které upravují způsob, jakým se požadavek zaslaný databázovému serveru bude zpracovávat.
Z hlediska odezvy jsou velmi důležitá nastavení týkající se vytvoření datové sady na serveru a její následný přenos na klientskou stanici.

Volba typu DB kurzoru

FireDAC umožňuje volbu typu kurzoru prostřednictvím parametru "CursorKind".
Parametr "CursorKind" má dopad na:
Čas potřebný pro načtení prvního záznamu výsledkové sady
Čas potřebný pro načtení kompletní výsledkové sady
Možnost současného otevření více kurzorů
Stabilitu (neměnnost) kurzoru
Nároky na systémové prostředky databázového stroje

Dostupné možnosti nastavení jsou:

• ckAutomatic - Typ kurzoru je vybrán automaticky na základě nastavení ostatních parametrů. 
• ckDefault - Obsahuje záznamy, které odpovídaly dotazu v okamžiku jeho spuštění. Načtení prvního záznamu může být pomalejší, protože se na klienta přesouvá celá výsledková sada. Celkově je ale rychlejší. 
• ckDynamic - Dynamický serverový kursor reflektuje změny způsobené aktualizacemi, které proběhly po dobu kdy je kurzor aktivní. Načtení prvního záznamu je rychlé, získání celé výsledkové sady může být pomalejší. 
• ckStatic - Statický serverový kurzor obsahuje záznamy, které odpovídaly dotazu v okamžiku jeho spuštění.
• ckForwardOnly - Jednosměrný serverový kurzor. Uvolňuje již použité záznamy, takže šetří paměť. Parametr "Unidirectional" musí být "True".

Příklad Delphi

procedure TForm1.Button1Click(Sender: TObject);
begin
  // Nastavení typu kurzoru na úrovni připojení
  FDConnection.Connected := False;
  FDConnection.FetchOptions.CursorKind := ckStatic;
  FDConnection.Connected := True;
end;

Příklad C++ Builder

void __fastcall TForm1::Button1Click(TObject *Sender)
{
  // Nastavení typu kurzoru na úrovni komponenty FDQuery
  FDQuery->Active = False;
  FDQuery->FetchOptions->CursorKind = ckAutomatic;
  FDQuery->Active = True;
}

Způsob práce s kurzorem

Parametr "Unidirectional" určuje, zda se lze v kurzoru pohybovat oběma směry, tedy dopředu i zpět. Standardně je má parametr nastavenu hodnotu "False", kdy je povolen i zpětný pohyb. Pokud je nastaven na "True", šetří se systémové zdroje (již zpracované záznamy jsou uvolněny z paměti), ale například při použití komponenty "DBGrid" vyvolá přesun na předchozí záznam chybové hlášení.

Uvolnění kurzoru

Databázový stroj udržuje kurzor dokud není datová sada uzavřena, nebo není uvolněn objekt, který je jejím správcem. Parametr "AutoClose", pokud je nastaven na "True", uvolní kurzor ihned po načtení posledního záznamu.

Pozor! Jestliže příkaz vrací více datových sad, musí být tento parametr nastaven na "False", jinak dojde k uzavření kurzoru po načtení všech záznamů první datové sady a další sady již načteny nebudou!

View Petr Houf's profile

LiveBindings IV - Pokročilejší nastavení II

Uživatelské formátování (Custom Format)

Dalším požadavkem, se kterým se můžeme setkat při propojování vizuálních komponent a dat je potřeba zobrazení dat v určitém požadovaném tvaru. Může se jednat například o prezentaci telefonních či směrovacích čísel, peněžních částek, položek typu datum a podobně.

LiveBindings Methods a Output Converters 

Uvnitř LiveBindings výrazů lze s předávanými parametry pracovat za pomoci dvou skupin vestavěných funkcí. Jedna (Output Converters) sdružuje funkce pro převod datových typů, druhá (Methods) pak především funkce pro zpracování a úpravu předávaných dat.

LiveBindings nabízí řadu vestavěných funkcí. Pokud by funkce kolidovala s funkcí či metodou používanou dotčeným objektem, lze ji deaktivovat. Dialogy pro aktivaci/deaktivaci funkcí a převodníků lze otevřít z "Inspektora Objektů". Na formuláři musí být umístěna a vybrána komponenta "BindingList".

Příklady formátování výstupu

Mějme jednoduchou aplikaci, která bude zobrazovat data z databáze. Struktura databázové tabulky je následující:

CREATE TABLE LB_DEMO2
(
  ID Integer NOT NULL,
  ZBOZI1 Varchar(20),
  ZBOZI2 Varchar(20),
  CENA Numeric(7,2),
  DATUM Date DEFAULT current_date,
  CONSTRAINT PK_LB2 PRIMARY KEY (ID)
);

Formulář pak může vypadat zhruba takto:

Formátování řetězců

Pokud bychom potřebovali běžné formátovací funkce jako je například převod na malá nebo velká písmena, stačí otevřít "Object Inspector" a do "Custom Format" zapsat příslušný předpis s využitím příslušné funkce LiveBindings.

Výraz "%s" odkazuje na zpracovávaný řetězec v původním tvaru, tak jak byl přijat od zdrojové komponenty.


V praxi může vyvstat potřeba zpracování více než jednoho řetězce. Například budeme požadovat sloučení polí "ZBOZI1" a "ZBOZI2" z naší tabulky a zobrazení výsledného řetězce v komponentě "Label".
Protože LiveBindings engine standardně umožňuje definovat pro vazbu pouze jeden "zdrojový" a jeden "cílový" objekt, musíme použít drobnou lest. V "BindingsList" vytvoříme nový "BindLink". Jako zdrojovou komponentu vybereme "BindSourceDB". Ta reprezentuje datovou sadu (tedy nadřízený objekt), v které jsou obě databázová pole definována. Tím získáme přístup k metodám, které budeme pro manipulaci s daty potřebovat. Nyní již stačí jen zapsat výraz pro sloučení získaných řetězců, např.:

UpperCase(self.FieldByName('ZBOZI1').Text)  + " " +
self.FieldByName('ZBOZI2').Text

"Self" odkazuje na zdrojový objekt a zpřístupňuje tak všechna data ze zdrojového objektu.

Formátování číselných hodnot

Pro formátování čísel můžeme použít dva přístupy. Pokud je zdrojem dat databáze, lze způsob zobrazení určit přímo pro daný sloupec. V okně "Structure" si zobrazíme pro datovou sadu (v naší aplikaci komponenta "Table") všechny sloupce.

Následně označíme sloupec "CENA", pro který hodláme změnit formátování a v okně "Object Inspector" odpovídajícím způsobem nastavíme vlastnost "DisplayFormat". Zde např. "### ###.00".

Stejného výsledku dosáhneme, pokud podobně jako u formátování řetězců nastavíme v okně "Object Inspector" vlastnost "CustomFormat". Formátování nelze nastavit přímo, ale za pomoci funkce "Format()". Pro zobrazení s přesností na dvě desetinná místa tedy například "Format('%%.2f', value)".

Úplný přehled argumentů funkce "Format()" je uveden v Embarcadero docwiki.


Pokud má být spolu s číslem zobrazen další symbol (procenta, měna, apod.), stačí pouze připojit patřičný string. V případě procent je třeba znak uvádět zdvojeně.

Format('%%.2f', value) + ' %%'
Format('%%.2f', value) + ' Kč'

Protože LiveBindings pracuje s ObjectPascalem i C++, lze v předpisu pro formátování použít jak jednoduché tak dvojité apostrofy. Akceptován tak bude zápis Format('%%.2f', value) i Format("%%.2f", value).

Datum a čas

Stejným způsobem je možné formátovat i položky typu datum či čas. Pouze místo funkce "Format()" je třeba použít funkci "FormatDateTime". Zápis formátování pro úplné zobrazení pro položku "DATUM" tak může vypadat následovně:

FormatDateTime('dd/mm/yyyy hh:nn:ss AM/PM', value)

View Petr Houf's profile

LiveBindings III - Custom Parse

V minulých příspěvcích jsem popisoval vizuální návrh propojení za pomoci LiveBindings Designeru nebo průvodce "LiveBindings Wizard". V praxi se však můžeme setkat s požadavky, které vizuálním návrhem nelze jednoduše realizovat.

Uživatelské zpracování (Custom Parse)

Custom Parse umožňuje řešit situace, kdy je třeba data předávaná mezi objekty upravit do formy, kterou je cílový objekt schopen akceptovat. Typicky se jedná o převody mezi různými datovými typy, nebo o úpravu dat do podoby odpovídající definované masce. S tímto požadavkem se můžeme často setkat například při návrhu databázových aplikací v prostředí FireMonkey.

Příklad:

Vytvořme si jednoduchou FireMonkey aplikaci, která bude sloužit k editaci záznamů databázové tabulky s následující strukturou:

CREATE TABLE LBDEMO
  (
  KONTAKT_ID Integer NOT NULL,
  PRIJMENI Varchar(50),
  ZEME Char(2),
  AKTIVNI Integer,
  CONSTRAINT PK_KONTAKT_ID PRIMARY KEY (KONTAKT_ID)
  );

Nejprve si v Delphi nebo C++ Builderu navrhneme formulář pro editaci dat (viz obrázek níže). Pole "AKTIVNÍ", které je v databázi reprezentováno jako integer, bude ve formuláři zobrazeno za pomoci komponenty "CheckBox".

Pro propojení jednotlivých komponent se zdrojem dat použijeme LiveBindings Designer (viz následující diagram).

Pokud takový projekt přeložíme, a pokusíme se změnit hodnotu zaškrtnutím nebo naopak odškrtnutím "CheckBoxu", bude zobrazena chyba. Automatická konverze převede typ boolean na string (defaultní datový typ pro všechny výrazy LiveBindings), který databázový stroj očekávající integer nedokáže zpracovat.

Řešením je zmiňovaná uživatelská konfigurace propojení. Nejprve v "LiveBindings Designeru" odstraníme nevyhovující vazbu (na symbol vazby klikneme pravým tlačítkem myši) a zvolíme příkaz "Remove Link".

Následně otevřeme "LiveBindings Wizard" a definujeme nový "BindLink". Alternativně jej můžeme vytvořit pomocí dvojkliku na komponentě "BindingsList" (otevře se okno pro editaci LiveBindings propojení), kde z nabídky vybereme volbu "New Binding => BindLink".

V okně "Object Inspektor" nastavíme jako zdrojovou komponentu datový zdroj, v tomto případě "BindSourceDB1". Vlastnost "SourceMemberName" umožňuje vybrat požadovaný sloupec tabulky, zde sloupec s názvem "AKTIVNI". Nakonec určíme komponentu pro zobrazení dat "ControlComponent". Bude jím komponenta "CheckBox1". 

Nyní je třeba doplnit výraz "ParseExpressions" pro uložení dat zpět do databáze. To můžeme provést rovněž prostřednictvím "Inspektora Objektů", nebo lépe v okně pro editaci propojení. Výhodou je možnost okamžité validace definovaných výrazů.

V seznamu propojení klikneme dvakrát na v předchozím kroku vytvořený BindLink a do zatím prázdné kolekce přidáme výraz pro "Parse":

Do pole pro "Control Expression" vložíme výraz "IfThen(Self.IsChecked, '1', '0')", který vyhodnotí stav komponenty "CheckBox1". Pokud bude zaškrtnuta (vlastnost "IsChecked" bude mít hodnotu "True"), bude výsledkem výrazu text "1", v opačném případě pak text "0". Do pole pro "Source Expression" napíšeme pouze "Text". Datový zdroj je tak informován o tom, že data obdrží jako string a do požadovaného typu (v tomto případě Integer) si je musí převést.

Vyhodnocení vytvořeného výrazu si lze ověřit za pomoci tlačítek "Eval Control" a "Eval Source". Tlačítka "Assign to Control" a "Assign to Source" pak testují vlastní přiřazení hodnoty.

Nyní můžeme aplikaci přeložit a ověřit si, že nyní již LiveBindings hodnotu zadanou prostřednictvím komponenty "CheckBox" interpretují správně a editované záznamy budou korektně uloženy do databáze.

View Petr Houf's profile