Efektivní práce s databází v ASP

3. května 2001

•

Většina příruček, návodů a článků pro začátečníky mystifikuje čtenáře. Snaží se vzbudit dojem, že práce s databází je v ASP hračka a využívá k tomu příkladů, o jejichž správnosti by bylo možné s úspěchem pochybovat. Jak je tomu ve skutečnosti? Je opravdu databázové programování v ASP tak snadné? Zkusme si převést jeden takový zjednodušený příklad na skutečně efektivní, profesionálně vypracovaný program.

Úvodní příklad

Ať již jste se učili ASP z jakéhokoli zdroje, jistě jste narazili na podobný příklad zobrazení dat z databáze:

<%
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DSN=Osoby;uid=Ja;pwd=heslo"
sql = "SELECT * FROM osoba"
set RS = Conn.Execute(sql)
%>
<table>
  <tr>
    <th>Příjmení</th>
    <th>Jméno</th>
  </tr>
<% while not RS.EOF %>
  <tr>
    <td><%= RS("prijmeni") %></td>
    <td><%= RS("jmeno") %></td>
  </tr>
<% RS.MoveNext
  End %>
</table>
<%
RS.Close
set RS = Nothing
Conn.Close
set Conn = Nothing
%>

Podobné příklady naleznete nejen v nápovědných souborech Microsoftu, ale i v mnoha článcích, knížkách a vzorových příkladech. Nevím, zda se takto jejich autoři snaží ukázat, jak je práce s databází v ASP snadná, nebo nechtějí plést začátečníkům hlavu něčím složitějším. Co však vím je, že byste jenom velmi obtížně hledali hůře napsaný kód.

Vždyť funguje, můžete namítnout. Ano, funguje. Pokud byste ale takto programovali v ASP aplikace pro víc než pár uživatelů za den, pánbůh s vámi a s vaším serverem. Tento příklad totiž porušuje snad všechny pravidla efektivní práce s databází v ASP a nádavkem ještě několik zcela obecných programátorských zásad. Pojďme si spolu nejprve ukázat a vysvětlit jednu chybu po druhé a pak si předvedeme lepší řešení.

Nedeklarované proměnné, názvy proměnných a Option Explicit

VBScript je sice jazyk, jenž nevyžaduje explicitní deklaraci proměnných, jenže psát jakýkoli program bez předem deklarovaných proměnných je chyba, které se dopouští snad jen úplní začátečníci. Nejenom, že se vám tato chyba vymstí nepřehledným a vysoce chybovým kódem, ale navíc ještě prodlouží dobu jeho vykonávání. VBScript totiž s předem deklarovanými proměnnými pracuje rychleji.

Každý správný skript by proto měl začínat příkazem Option Explicit, který způsobí, že jakákoli nedeklarovaná proměnná bude ohlášena jako chyba. Všechny proměnné pak je samozřejmě třeba opravdu předem deklarovat, nejlépe hned na začátku skriptu, resp. každé procedury či funkce, příkazem Dim (případně Private, Public nebo Const).

V souvislosti s deklarací proměnných bych se rád zmínil i o konvencích pro jejich pojmenovávání. Jména proměnných sice nemají vliv na výkon, avšak patří k dobrému stylu naznačit v nich typ (v případě VBScriptu přesněji subtyp jediného typu Variant) ukládaných dat. Vhodné předpony (str, int, dbl, atd.) doporučuje přímo Microsoft v dokumentaci k VBScriptu.

Používání procedur a funkcí

Další častá chyba začátečníků je, že celý skript napíší do jednoho bloku, bez členění na procedury a funkce. Výsledkem je opět velmi nepřehledný a pomalejší kód. VBScript lokální proměnné uvolní ihned po ukončení procedury, čímž se šetří paměť a navíc k nim opět přistupuje rychleji. Každý skript, i ten nejjednodušší by tedy měl obsahovat alespoň jednu proceduru. Správně strukturovaný kód se vám odmění menší chybovostí a vyšším výkonem.

Střídání ASP a HTML

Většina příkladů, které se snaží demonstrovat snadnost naučení a používání ASP, masivně využívá tzv. context switching, neboli přepínání mezi ASP a HTML pomocí <% %>. Cožpak to není krásné, jak snadno lze běžnému HTML kódu vdechnout život dynamickým obsahem? Není! Nejenom, že tento postup kód činí nepřehledným a tudíž vede k chybám, nejenom, že jde proti pozitivnímu trendu oddělování formy od obsahu, navíc je ještě výrazně pomalý.

Server totiž musí při každém přepnutí zvolit odpovídající parser a tím se zdržuje. Začněte tedy skript otvírací závorkou <% a s výjimkou dlouhých souvislých bloků čistého HTML se vyvarujte toho, ji uzavřít dříve než na konci skriptu.

Bufferování výstupu

IIS 4 se implicitně chová tak, že veškerý výstup generovaný ASP skriptem okamžitě odesílá na klienta. To je však vzhledem k charakteru přenosového protokolu opět velmi pomalé. TCP/IP totiž mnohem efektivněji přenáší méně velkých bloků dat, než více bloků malých. Oč lepší a rychlejší by tedy bylo, vygenerovat nejprve celou stránku do paměti serveru a pak ji poslat klientovi naráz.

Stačí při tom málo – na začátek skriptu vložit tento příkaz:

Response.Buffer = True

Máte-li přímou kontrolu nad serverem, můžete bufferování zapnout i pomocí Internet Services Manageru. V případě IIS 5 na Windows 2000 je již bufferování výstupu zapnuto implicitně.

Pozor! Nepokoušeje se vygenerovat stránku do paměti svépomocí tím, že ji budete postupně skládat z mnoha řetězců do jedné proměnné, kterou nakonec vypíšete jedním příkazem Response.Write. Spojování řetězců, zejména v cyklu, je totiž v ASP velmi pomalé. Mnohonásobné provedení Response.Write je tedy při zapnutém bufferování mnohem rychlejší, než mnohonásobné spojování řetězců operátorem &.

SessionState

Používáte v určitém skriptu objekt Session? Ne? Pak ale vězte, že ASP ho používá jaksi za vás. Pokud totiž podporu sessions explicitně nevypnete hned na začátku skriptu, server stejně pro objekty Session alokuje potřebnou paměť a ztratí tím cenný čas. Pokud tedy objekt Session nepotřebujete, použijte vždy na začátku každého skriptu direktivu ENABLESESSIONSTATE = False.

Ošetření chyb

Jeden z důvodů, proč je výše uvedený příklad prakticky nepoužitelný, je naprostá absence ošetření chyb. Pamatujte na pravidlo, které říká: "cokoli, co se může pokazit, se opravdu pokazí." O databázových operacích to platí dvojnásob. Nepodaří se navázat spojení s databázovým serverem, ve vstupních datech bude chyba, změní se verze databázového stroje a select, který léta fungoval, fungovat najednou přestane.

Všechny databázové operace proto musí být ošetřeny na výskyt chyby pomocí příkazu On Error Resume Next a vestavěného objektu ASP Err, případně kolekce Errors objektu Connection ADO.

Pozdní alokace a časné uvolnění zdrojů

Jakékoli zdroje, a databázové objekty obzvlášť, alokujte co nejpozději a uvolněte co nejdříve. I na průměrně zatíženém serveru má každá milisekunda připojení k databázi svou cenu. Objekty Connection a Recordset proto vytvořte a otevřete, až když je opravdu potřebujete a uvolněte je ihned (samozřejmě po uzavření), jakmile je potřebovat přestanete.

Explicitní versus implicitní vytváření objektu Connection

Ne vždy je nutné explicitně vytvářet a otevírat objekt Connection. Objekt Recordset lze většinou vytvořit a otevřít pouze s připojovacím řetězcem (connection string), např. takto:

Set rsOsoby = Server.CreateObject("ADODB.Recordset") rsOsoby.Open strSQL, strConnectionString, adOpenForwardOnly, adLockReadOnly, adCmdText

ASP v takovém případě vytvoří objekt Connection implicitně, a tedy asi o něco rychleji než vy sami ve skriptu. Zároveň se nemusíte starat o úklid. Jakmile uzavřete a zrušíte Recordset, uzavře se a zruší i implicitní Connection.

Je však několik případů, kdy implicitní vytvoření objektu Connection nejde, nebo není vhodné využít. Je to zejména tehdy, když chcete objekt Connection použít v jednom skriptu vícekrát. Při implicitní variantě by se vám vícekrát vytvořil a zrušil a to by představovalo zbytečnou režii a časovou ztrátu. Proto vždy, když v jednom skriptu chcete provést více databázových operací, vyžadující objekt Connection, nejprve ho vytvořte a otevřete explicitně, pak co nejrychleji a bezprostředně za sebou proveďte požadované operace a nakonec objekt Connection co nejrychleji zavřete a zrušte.

Vytváření spojení přes DSN

Ve výše uvedeném příkladu je v připojovacím řetězci (connection string) použito předem připravené a na serveru zaregistrované DSN (data source). Server tedy musí nejprve jít do registru příslušné DSN vyhledat a teprve pak údaje z něj použít pro připojení k databázi. Tím opět utíkají drahocenné sekundy. Proto by až na mimořádné výjimky mělo být preferováno spojení bez DSN (DSN-less connection). Připojovací řetězec pak může vypadat například takto:

"Provider=SQLOLEDB;Network Library=DBMSSOCN;Data Source=MujServer;Initial Catalog=MojeDb;User ID=Ja;Password=MojeHeslo"

Záměrně jsem použil tento příklad, protože připojení k MS-SQL Serveru přes OLEDB je další způsob jak zrychlit databázové operace oproti použití ODBC.

CursorType, LockType a CacheSize

Rychlost práce s Recordsetem významně ovlivňují i hodnoty jeho vlastností CursorType, LockType a CacheSize.

Nejlepší (z hlediska rychlosti) výsledky dává CursorType = adForwardOnly a LockType = adLockReadOnly. V obou případech se jedná o implicitní hodnoty. I tak však může být vhodné je vzhledem k možným změnám v budoucích verzích nastavovat explicitně, samozřejmě pokud nepotřebujete v konkrétním případě jiné nastavení.

Co se týče vlastnosti CacheSize, nelze obecně její optimální hodnotu stanovit, takže je třeba v konkrétním případě vždy experimentovat. Implicitní hodnota 1 je vhodná skutečně jen výjimečně. Většinou se bude optimum pohybovat někde mezi 5 a 50. Např. u často používané techniky stránkování výpisu z databáze, bude CacheSize odpovídat počtu záznamů na jednu stránku (obvykle 10 až 20).

CommandType

Recordset lze otevřít mnoha různými způsoby. U všech se však objevuje parametr, či vlastnost CommandType, jehož implicitní hodnota je adCmdUnknown. Ta sice zajistí, že se příkaz provede vždy, ovšem ADO v tom případě musí typ příkazu zjistit samo (většinou se jedná o adCmdText, nebo adCmdStoredProc), což mu nějakou dobu trvá. Proto je dobré se na implicitní hodnotu nespoléhat a vždy CommandType explicitně určit, např. takto:

Set rsOsoby = objConnection.Execute(strSQL, , adCmdText)

nebo takto:

With cmdSelectOsoby
Set .ActiveConnection = objConnection
.CommandType = adCmdText
.CommandText = "SELECT jmeno, prijmeni FROM osoba"
Set rsOsoby = .Execute
End With

SELECT *

Velmi častou chybou začátečníků je použití hvězdičky v příkazu SELECT. Opět se jedná o chybu jak z hlediska stylu – vede k horší čitelnosti a vyšší chybovosti kódu, tak z hlediska výkonu. Málokdy totiž opravdu potřebujete vybrat z příslušné tabulky (tabulek) všechny sloupce a pokud použijete hvězdičku, mnoho dat se zbytečně načítá do paměti a často dokonce přesouvá po síti z databázového serveru na internetový.

RS("jmeno_sloupce") místo RS.Fields(cislo_sloupce)

Málokterá konstrukce je vidět tak často i v jinak profesionálně napsaných skriptech jako RS("jmeno_sloupce"). A přitom se v ní nacházejí hned dvě chyby snižující výkon.

První chybou je odkazování se na prvek kolekce Fields názvem (řetězcem), namísto pořadového čísla. Jakékoli vyhledávání řetězců, a to i v interních datových strukturách ADO, je totiž samozřejmě mnohem pomalejší, než výběr prvku dle pořadí. Samozřejmou podmínkou pro číselné odkazy je ovšem explicitní výčet sloupců v příkazu SELECT, jak o tom píši výše. Používání SELECT * by se v takovém případě stalo automatickým a velmi výkonným generátorem náhodných chyb.

Druhou chybou je spoléhání na implicitní vlastnost objektu Recordset. Oproti zkrácené verzi využívající implicitní vlastnosti (property) by plná kvalifikace správně zněla takto:

nebo takto:

RS.Fields(poradove_cislo).Value

Zde je ovšem rozdíl ve výkonu skoro zanedbatelný (dle některých zdrojů i sporný), takže se jedná spíše o stylovou záležitost. Její význam však oceníte např. tehdy, až výrobce v příští verzi implicitní vlastnosti tříd změní, nebo dokonce zruší.

Mimochodem, pomalost výběru určitého prvku z kolekce dokumentují i měření, která prokázala, že čas lze významně ušetřit přenesením odkazu na prvek kolekce Fields do proměnné takto:

Set fldJmeno = RS.Fields(0)
Set fldPrijmeni = RS.Fields(1)

Místo RS.Fields(0).Value, resp. RS.Fields(1) se pak pracuje přímo s fldJmeno.Value, resp. fldPrijmeni.Value.

GetString a GetRows

To nejdůležitější nakonec. Z hlediska efektivity je v úvodním příkladu asi nejspornější to, že vůbec používá objekt Recordset k výpisu dat z databáze. Přesněji řečeno, bez Recordsetu se samozřejmě neobejdeme, jenže procházet ho celý v cyklu, ve kterém se současně posílají data na klienta je značně neefektivní. Porušuje to totiž pravidlo o včasném uvolnění zdrojů, neboť pro výpis dat již otevřený Recordset ve skutečnosti není potřeba.

Zavřít a uvolnit Recordset (a tedy i Connection) dřív, než použijeme jeho data pro výstup, nám umožňují metody GetString a GetRows. První z nich vytvoří z celého Recordsetu jediný řetězec, do kterého vloží oddělovače řádků a sloupců poslaných jako parametry. Druhá metoda pak přenese celý Recordset do dvojrozměrného pole, které rovnou i sama vytvoří. Obě metody jsou velmi rychlé a ve většině případů efektivnější než přímá práce s Recordsetem

Správné řešení

Zkusme se nyní podívat, jak by náš úvodní příklad vypadal po odstranění všech chyb a s použitím metody GetString.

<%@ LANGUAGE = "VBScript" ENABLESESSIONSTATE = False %>
<%
Option Explicit
Response.Buffer = True
Main
Sub Main
  Const strConnString = "Provider=SQLOLEDB;Network Library=DBMSSOCN;Data Source=MujServer;Initial Catalog=MojeDb;User
ID=Ja;Password=MojeHeslo"
  Dim strSQL, rsOsoby, strData
  strSQL = "SELECT jmeno, prijmeni FROM osoba"
  On Error Resume Next
  Set rsOsoby = Server.CreateObject("ADODB.Recordset")
  If Err.Number <> 0 Then
    Response.Write "<p>Chyba: " & Err.Description & </p>
    Err.Clear
  Else
    rsOsoby.Open strSQL, strConnString, adOpenForwardOnly, adLockReadOnly, adCmdText
    If Err.Number <> 0 Then
      Response.Write "<p>Chyba: " & Err.Description & </p>
      Err.Clear
    Else
      If rsOsoby.EOF Then
        Response.Write "Nenalezena žádná data."
      Else
        strData = rsOsoby.GetString(,, "</td><td>",
"</td></tr><tr><td>", "-")
      End If
      rsOsoby.Close
    End If
    Set rsOsoby = Nothing
    If strData <> "" Then
      Response.Write
"<table><tr><th>Příjmení</th><th>Jméno</th></tr>"
      Response.Write "<tr><td> " & strData & "</td></tr></table>"
    End If
  End If
End Sub ‚ Main
%>

Jak je vidět, metoda GetString je sice velmi efektivní, avšak nepřipouští žádné specifické formátování jednotlivých sloupců. Pokud např. chcete sloupec Příjmení zobrazovat tučně, nebo odlišovat liché a sudé řádky barvou pozadí, musíte sáhnout po metodě GetRows. Tentýž příklad pak bude vypadat takto:

Sub Main   Const strConnString = "Provider=SQLOLEDB;Network Library=DBMSSOCN;Data Source=MujServer;Initial Catalog=MojeDb;User
ID=Ja;Password=MojeHeslo"
  Dim strSQL, rsOsoby, varData, i
  strSQL = "SELECT jmeno, prijmeni FROM osoba"
  On Error Resume Next
  Set rsOsoby = Server.CreateObject("ADODB.Recordset")
  If Err.Number <> 0 Then
Response.Write "<p>Chyba: " & Err.Description & </p>
    Err.Clear
  Else
    rsOsoby.Open strSQL, strConnString, adOpenForwardOnly, adLockReadOnly, adCmdText
    If Err.Number <> 0 Then
      Response.Write "<p>Chyba: " & Err.Description & </p>
      Err.Clear
    Else
      If rsOsoby.EOF Then
        Response.Write "Nenalezena žádná data."
      Else
        varData = rsOsoby.GetRows
      End If
      rsOsoby.Close
    End If
    Set rsOsoby = Nothing
    If IsArray(varData) Then
      Response.Write
"<table><tr><th>Příjmení</th><th>Jméno</th></tr>"
      For i = LBound(varData, 2) to UBound(varData, 2)
        Response.Write "<tr>"
        Response.Write "<td><strong>" & varData(1, i) & "</strong></td>"
        Response.Write "<td>" & varData(0, i) & "</td>"
        Response.Write "</tr>"
      Next
      Response.Write "</table>"
    End If
  End If
End Sub ‚ Main

Závěr

Hlavním cílem tohoto článku bylo naznačit, že ne vše je vždy tak jednoduché, jak bývá prezentováno. Chcete-li dosáhnout určitého výsledku libovolnou cestou, může to být snadné. Chcete-li ale dosáhnout požadované funkčnosti opravdu efektivně a na profesionální úrovni, budete potřebovat o dost víc než příklady z příruček pro začátečníky.

Pokud je pro vás v tomto článku mnoho nových informací, pak vězte, že v něm minimálně jednou tolik důležitých informací chybí. V zájmu uchování rozumného rozsahu jsem zcela pominul mj. odpojené (disconnected) Recordsety, zapouzdření databázových operací do tříd, procedur a funkcí, optimalizaci databázových struktur a dotazů, využití COM objektů pro realizaci aplikační obchodní logiky, kešování výstupních dat a předgenerování statických stránek atd. Jistě by stálo za to, publikovat i konkrétní výsledky některých zátěžových testů.

Jenže to vše není téma pro jediný článek. Proto doufám, že článek alespoň mnohé z vás inspiruje k vyhledání dalších informací, třebas s pomocí připojeného seznamu zdrojů.

A zcela na závěr ještě jedna rada. V případě programování málo která zásada platí zcela obecně. Vždy berte v úvahu váš konkrétní projekt a jeho specifika. Snažte se nalézt nejlogičtější a nejjednodušší řešení vedoucí k cíli, při zachování přehlednosti a snadné modifikovatelnosti kódu. Co se týče výkonu, vždy pečlivě otestuje několik variant a vyberte tu nejrychlejší. Nezapomeňte, že u internetových aplikací je každá uspořená milisekunda dobrá, neboť se pak ve skutečném provozu násobí stovkami až stovkami tisíc uživatelů.

Použité zdroje

Přeji vám hezký den.