Uložené procedúry v ASP – základy

Jednou z často odporúčaných možností ako skvalitniť a zefektívniť kód pracujúci s databázovými dátami v ASP aplikáciách je používať uložené procedúry (stored procedures). V súvislosti touto radou vyvstávajú pred nami tri základné otázky. Prvá, čo presne sú uložené procedúry a ako ich môžeme definovať, vytvoriť? Druhá, ako ich konkrétne môžeme použiť v ASP aplikácii? A nakoniec tretia, ktorá je snáď najdôležitejšia, prečo by sme ich mali používať, keď i bez nich to funguje? O odpovede na tieto otázky by som sa chcel pokúsiť v tomto článku.

Úvodná poznámka

Na začiatok by som chcel poznamenať, že to čo bude v ďalšom povedané o uložených procedúrach platí pre MS SQL Server 7.0 a MS SQL Server 2000. Uložené procedúry sa samozrejme dajú používať i iných databázových servroch, ako sú ORACLE, či InterBase, avšak detaily ich implementácie sa môžu pri týchto databázových systémoch líšiť.

Uložená procedúra? Čo to je?

Uložená procedúra (stored procedure) je dávka (batch) SQL príkazov trvalo uložená na disku ako pomenovaný databázový objekt (podobne ako je databázovým objektom napr. tabuľka alebo pohľad) v konkrétnej databáze na SQL serveri.

Uložené procedúry sú v mnohom podobné podprogramom resp. procedúram, ktoré poznáme z procedurálnych programovacích jazykov. Môžu mať (no samozrejme nemusia) vstupné i výstupné parametre, možno v nich používať lokálne premenné, riadiace konštrukcie ako sú cykly či podmienky, a tiež je možné volať z jednej uloženej procedúry inú uloženú procedúru. Po svojom vykonaní uložená procedúra vracia tzv. status hodnotu indikujúcu či daná uložená procedúra prebehla v poriadku. Uložené procedúry však priamo nevracajú hodnoty (ako funkcie) a tiež ich nemožno použiť priamo vo výraze.

Životný cyklus uloženej procedúry

Pokúsme sa teraz pozrieť na niečo, čo by sme mohli nazvať „životným cyklom uloženej procedúry“. Tento pohľad je dôležitý pre pochopenie toho čím sa líši uložená procedúra od bežného SQL príkazu, a prečo je jej použitie takmer vždy efektívnejšie ako použitie ekvivalentného ad-hoc SQL príkazu.

Uloženú procedúru je najprv potrebné vytvoriť v danej databáze na danom SQL servri. Pri vytvorení uloženej procedúry sa jej názov a tiež dalšie potrebné informácie o nej ukladajú do systémovej tabuľky sysobjects v danej databáze a jej zdrojový kód sa uloží do systémovej tabuľky syscomments v danej databáze. Naviac je počas vytvorenia uloženej procedúry robená kontrola syntaktickej správnosti jej SQL kódu. Pri vytváraní uloženej procedúry sa teda nič nekompiluje, a nič ďalšie okrem toho, čo bolo už spomenuté, sa neukladá v SQL servri. To, že sa priamo teraz nič nekompiluje, okrem iného umožňuje, aby sa SQL kód uloženej procedúry mohol odkazovať i na zatiaľ ešte v databáze neexistujúce objekty, tie však samozrejme musia existovať v čase vykonávania danej uloženej procedúry.

Pri požiadavke o vykonanie uloženej procedúry SQL server najprv zistí, či pre ňu neexistuje platný vykonávací plán (execution plan) vo vyrovnávacej pamäti (cache, procedure cache). Ak existuje, tak ho procesor príkazov vyberie a skontroluje, či je tento vykonávací plán ešte stále platný, a ak je všetko v poriadku, tak sa tento predpripravený vykonávací plán použije a daný SQL príkaz sa tak vykoná. V takomto prípade nie je teda potrebné opätovne kompilovať a optimalizovať daný SQL kód. Tu ešte možno stojí za to poznamenať, že vykonávacie plány sú (vo verzii MS SQL Server 7.0 a vyššie) znovupoužiteľné nielen v rámci jedného konkrétneho pripojenia (connection) ale i medzi rôznymi pripojeniami.

V prípade, že pre danú uloženú procedúru vo vyrovnávacej pamäti neexistuje vykonávací plán, prípadne ak aj existuje no je už neplatný (napr. kvôli zmene indexovania v databáze, s ktorej objektami uložená procedúra pracuje, ktorá bola urobená až po vytvorení daného vykonávacieho plánu) potom SQL server musí vykonávací plán pre danú uloženú procedúru opätovne vytvoriť, inak povedané musí jej kód znovu kompilovať a optimalizovať. Potom sa novovytvorený vykonávací plán uloží do vyrovnávacej pamäti (vykonávací plán sa teda neukladá trvalo na disk) a následne sa vykoná.

Samozrejme celý tento proces je oveľa zložitejší ako je popísané v tomto stručnom náčrte, no toto asi nie je to správne miesto na detailný popis toho ako interne pracuje procesor príkazov, optimalizátor, vyrovnávacia pamäť, či iné interné mechanizmy v MS SQL servri. Pri bežnej konkrétnej práci ani tieto detaily vedieť nepotrebujeme. Dôležité je zapamätať si z toto celého aspoň to, že každé spracovanie SQL príkazu, resp. dávky SQL príkazov, SQL servrom zahŕňa dve základné časti. Fázu získania (nájdenia vo vyrovnávacej pamäti alebo vytvorenia pomocou kompilácie a optimalizácie) vykonávacieho plánu pre daný konkrétny príkaz a vlastné vykonanie daného vykonávacieho plánu, čím sa požadovaný SQL príkaz realizuje.

Kompilácia a najmä optimalizácia môže byť pri zložitých SQL príkazoch (pri ktorých optimalizátor musí vyhodnotiť naozaj stovky možností ako daný SQL príkaz realizovať) podstatne pomalšia ako nájdenie potrebného vykonávacieho plánu vo vyrovnávacej pamäti a samotné vykonanie príkazu. Napr. pri zložitom SQL príkaze pokojne môže nastať stav, že samotné vykonanie SQL príkazu (vrátane získania jeho vykonávacieho plánu z vyrovnávacej pamäte) trvá pár milisekúnd, no jeho kompilácia a optimalizácia môže trvať i viac ako pol sekundy. Preto by malo byť našou snahou pri návrhu spôsobu akým budeme vo vyvíjanej aplikácii prístupovať k databázovým dátam priblížiť sa k ideálnemu stavu, keď daný príkaz skompilujeme a zoptimalizujeme iba raz, pri jeho prvom použití, a potom nech je použitý hoci aj desaťtisíc či miliónkrát bez nutnosti ho rekompilovať a optimalizovať pri každom jeho vykonaní.

A práve v snahe dosiahnúť takýto stav, ktorý nám vskutku môže priniesť výrazne zrýchlenie prístupu k databázovým dátam v našej aplikácii, by nám malo pomôcť použitie uložených procedúr, ktoré nám práve toto umožňujú dosiahnúť, pretože ich vykonávacie plány sa vo všeobecnosti vždy ukladajú do vyrovnávacej pamäti, na rozdiel od ad-hoc SQL príkazov, ktorých vykonávacie plány sa ukladajú do vyrovnávacej pamäti iba v špecifických prípadoch a aj to iba pri jednoduchších SQL príkazoch. Naviac ešte treba poznamenať, že používanie uložených procedúr prináša so sebou i ďalšie výhody, ktoré môžu byť v konečnom dôsledku aj dôležitejšie ako nárast výkonu aplikácie, no o tom viac neskôr.

Konkrétne a prakticky

Aby sme neskĺzli do čisto teoretickej roviny, pozrime sa teraz na praktický príklad. Majme napríklad databázovo orientovanú ASP aplikáciu „Knižnica“, a v nej kód, pomocou ktorého pristupujeme do databázy a zisťujeme ktorí užívatelia majú aktuálne požičané viac ako štyri knihy. Kód na získanie zoznamu takýchto užívateľov z databázy by mohol vyzerať napríklad takto:

Poznamenávam, že ide o príklad, takže je tu napr. úmyselne vynechané ošetrenie chýb, a v praxi by sa to prípadne dalo napísať snáď i lepšie, no pre tento príklad to postačí.

OK, teraz sa pozrime na to, ako by sme mohli vyššie uvedenú časť kódu prepísať tak aby sme namiesto ad-hoc SQL príkazu definovaného priamo v kóde ASP aplikácie použili uloženú procedúru. Naozaj nejde o nič zložité. Ak ste schopní napísať kód priamo používajúci SQL príkazy zaručene budete schopní napísať ten istý kód i pomocou uloženej procedúry.

Najprv musíme vytvoriť danú uloženú procedúru. Na vytvorenie uloženej procedúry sa v MS SQL Servri používa T-SQL príkaz „CREATE PROCEDURE“. Kompletnú syntax možno nájsť v online dokumentácii, pre tento náš príklad si vystačíme s touto zjednodušenou verziou:

Ako T-SQL_prikaz môžeme uviesť ľubovoľný T-SQL príkaz, okrem výnimiek vymenovaných v dokumentácii (napr. nie je možné v rámci vytvárať resp. meniť indexy alebo tabuľky, nie je možné použiť žiadny DBCC príkaz, …), je možné vytvárať dočasné (temporary) tabuľky, používať lokálne premenné, používať riadiace konštrukcie (cykly, podmienky, …) existujúce v T-SQL a tak realizovať aj vskutku zložité manipulácie s dátami.

No vráťme sa k nášmu príkladu, daný SQL príkaz by sme mohli implementovať ako uloženú procedúru takto:

Ako vidíte, nič zložité. SQL príkaz je presne ten istý, len je teraz implementovaný iným spôsobom, je umiestnený na inom mieste a budeme ho volať iným spôsobom. Uloženú procedúru môžeme v SQL servri vytvoriť napríklad i tak, že daný SQL (CREATE PROCEDURE) príkaz spustíme pomocou Query Analyzera (či iného ekvivalentného nástroja) nad požadovanou databázou, no asi najčastejším spôsobom je použitie Enterprise Managera. Najprv pravým tlačidlom myši klikneme na položku „Stored Procedures“ pri danej databáze, v ktorej chceme uloženú procedúru vytvoriť, a z kontextového menu, ktoré sa objaví si vyberieme položku „New Stored Procedure…“.

Do okna, ktoré sa objavilo, napíšeme kód uloženej procedúry, a uložíme ho na disk do databázy, v ktorej danú uloženú procedúru vytvárame, pomocou tlačidla „OK“, a je to hotové.

Pred uložením môžeme ešte skontrolovať syntaktickú spávnosť kódu danej uloženej procedúry (tlačidlo „Check Syntax“) resp. aj hneď nastaviť práva (tlačidlo „Permission“) na manipuláciu s ňou.

Teraz upravíme vyššie uvedenú časť ASP kódu tak, aby sme na realizáciu požadovanej databázovej operácie použili nie ad-hoc SQL príkaz ale novovytvorenú uloženú procedúru. Upravený kód v ASP aplikácii by mohol vyzerať napríklad takto:

Samozrejme, ako obvykle, ADO ponúka i ďalšie alternatívne možnosti ako volať uloženú procedúru. Použitie metódy „Execute“ ADO objektu „Connection“ je jednou z najjednoduchších metód ako to urobiť, jej nevýhodou je však to, že týmto spôsobom nemožno získať hodnoty výstupných parametrov, i keby v uloženej procedúre boli definované, a tiež takto nemožno získať z uloženej procedúry návratovú status hodnotu.

No a teraz už k sľúbenej poslednej otázke.

Prečo používať uložené procedúry?

Prečo by som mal používať uložené procedúry namiesto ad-hoc SQL príkazov písaných priamo do ASP kódu? V čom a prečo to je výhodnejšie?

Prvý argument sme už spomínali, použitie uložených procedúr je takmer vždy efektívnejšie a kód s nimi je takmer vždy rýchlejší ako analogický kód písaný pomocou ad-hoc SQL príkazov uvedených priamo v kóde. To je vážny argument, ktorý sa môže dostať do popredia nášho záujmu najmä v prípade, ak k našej aplikácii pristupuje mnoho užívateľov, ktorí robia veľmi podobné alebo také isté akcie. Najmä zmeny (UPDATE) dát realizované pomocou uložených procedúr môžu byť neporovnateľne rýchlejšie ako pri použití ADO metód nad recordsetom.

Samozrejme v niektorých prípadoch nemusí byť prínos použitia uložených procedúr, ak sa pozeráme iba na nárast výkonu, taký zreteľný. Napr. ak ide o elementárny SQL príkaz, pri ktorom doba jeho kompilácie a optimalizácie je taká malá, že je porovnateľná s dobou vyhľadania a kontroly či pre daný SQL príkaz existuje platný vykonávací plán vo vyrovnávacej pamäti (tzv. „cheap to compile queries“), vtedy MS SQL server vykonávacie plány pre takéto SQL príkazy do vyrovnávacej pamäti ani neukladá. Alebo ak to zoberieme z druhej strany, do vyrovnávacej pamäti sa ukladajú nielen vykonávacie plány uložených procedúr, ale môžu tam byť v určitých špecifických prípadoch uložené i vykonávacie plány ad-hoc SQL príkazov (ide o tzv. „other caching mechanisms“ ako sú napríklad autoparametrizácia, použitie systémovej uloženej procedúry „sp_executesql“, …) V týchto prípadoch je efekt týchto techník (ak hovoríme iba o rýchlosti kódu) asi rovnaký ako pri použití uložených procedúr, len si treba uvedomiť, že pri uložených procedúrach určuje priamo programátor, že toto je znovupoužiteľný kód, ktorého vykonávací plán by sa mal uložiť do vyrovnávacej pamäte, a pri ostatných „caching“ mechanizmoch ide o optimalizačné techniky SQL servra.

Na koniec tejto diskusie o efektívnosti použitia uložených procedúr pre úplnosť ešte poznamenajme, že efektívnosť prístupu k databázovým dátam je komplexná problematika, ktorá iste nezávisí iba od toho či na prístup k nim používame uložené procedúry alebo iné metódy. Ak hľadáme príčiny prečo je prístup k databáze v našej aplikácii pomalý, iste stojí za úvahu sa zamyslieť i nad tým, či je v poriadku návrh databázovej schémy resp. návrh použitia indexov, či HW SQL servra postačuje pre požadovanú záťaž, či je SQL Server správne nakonfigurovaný, či vhodným spôsobom používame parametre pri ADO objektoch, atď.

Uložené procedúry však pomimo zvýšenia efektívnosti majú i mnoho ďalších výhod, ktoré určite nie sú zanedbateľné. Môžeme spomenúť napríklad tieto:

• Modulárnosť. Kód uloženej procedúry (i keby bola používaná z viacerých ASP stránok vo viacerých aplikáciach) je na jednom mieste na SQL servri, teda ak v ňom potrebujeme urobiť zmenu, napr. v prípade, že sa zmenilo niečo v databáze, stačí to urobiť na jednom mieste, pričom zdrojový kód ASP stránok zostáva nezmený. Naviac najmä pri zložitých uložených procedúrach môžeme konzultovať ich tvorbu s niekým kto je expertom na SQL, prípadne so správcom SQL servra, prípadne ich môžu pre nás navrhnúť a implementovať priamo títo ľudia, a aplikačným programátorom potom stačí vedieť ako dané uložené procedúry volať a aké výsledky z nich dostanú.
• Zvýšenie bezpečnosti. Užívateľ pod accountom ktorého sa daná uložená procedúra spúšťa môže mať práva iba na jej spustenie, a nemusí mať práva priamo na prístup a modifikáciu jednotlivých databázových objektov, s ktorými daná uložená procedúra manipuluje.
• Menšie množstvo prenášaných dát po sieti. Pri použití uložených procedúr sa môže tiež nezanedbateľne znížiť množstvo prenášaných dát pri komunikácii medzi klientom a servrom počas vykonávania databázových operácii. Napr. je rozdiel poslať na server príkaz „EXEC upZoznamAktivnychUzivatelov“ ako poslať na server desať riadkový zložitý SQL príkaz.
• Vyššia kvalita kódu. Často je výhodné napr. i kvôli prehľadnosti kódu implementovať zložitú manipuláciu s dátami (zahŕňajúcu napríklad použitie dočasných tabuliek na uloženie medzivýsledkov nad ktorými následne robíme ďalšie výbery) ako uloženú procedúru, ktorá sa efektívne vykoná na strane servra a nám vráti len požadované výsledky v definovanom tvare, ako prenášať po sieti množstvo dát a následne ich spracovávať na strane klienta.
• Možnosť vrátiť i ďalšie hodnoty okrem recordsetu. Uložená procedúra nám súčasne môže vrátiť nielen recordset, ale k nemu i ďalšie hodnoty, napr. pomocou výstupných parametrov či návratovej status hodnoty, čo sa niekdy dá naozaj efektívne využiť a ušetriť tak ďalší prístup do databázy.

Aké sú nevýhody uložených procedúr?

Najmä pri zložitejších uložených procedúrach, majúcich vstupné i výstupné parametre, návratovú hodnotu, môže byť ich implementácia i volanie z ASP kódu pomerne zložité. Tiež musíme akceptovať to, že na SQL servri už nie sú iba dáta, ale tiež i určitá časť logiky, pomocou ktorej naša aplikácia s týmito dátami pracuje. Niekedy, napr. pri elementárnych SQL príkazoch, napr. jednoduchých statických SELECT príkazoch, sa môže zdať použitie uložených procedúr ako zbytočná réžia naviac. Na moderných SQL sevroch je v tomto prípade efektívnosť pri obidvoch spôsoboch prístupu prakticky rovnaká, a je jednoduchšie to priamo napísať do kódu, a netreba nič vytvárať na SQL servri.

Záver

Celkovo však možno povedať, že použitie uložených procedúr je výhodné a i ja z vlastnej skúsenosti môžem iba potvrdiť známu vetu opakovanú takmer vždy keď je niekde rozoberáná táto problematika:

„Uložené procedúry predstavujú najefektívnejší mechanizmus na vykonávanie SQL príkazov klientskou aplikáciou, preto na prístup k databázovým dátam a na manipuláciu s nimi používajte uložené procedúry vždy a všade kde je to len možné.“

Na záver by som chcel pripomenúť ešte raz, že príklad, ktorý bol použitý na ukážku je skutočne elementárny a reálne použitie uložených procedúr, pri ktorom sa naplno môže prejaviť výhodnosť ich použitia, zahŕňa najmä použitie parametrov a T-SQL programových konštrukcií v ich kóde. Ak bude o to záujem môžem napísať i pokračovanie tohoto článku venované použitiu parametrov a návratových hodnôt v uložených procedúrach. Zatiaľ ďakujem za čítanie. Akékoľvek poznámky a pripomienky sú vítané na mojej emailovej adrese alebo na adrese redakcie, prípadne v diskusii pod článkom.

Starší komentáře ke článku

Pokud máte zájem o starší komentáře k tomuto článku, naleznete je zde.