Diskuze o uložených procedurách vs. adhoc dotazy, která se konala v rámci diskuzní skupiny VSNET-L a která se týkala „souboje“ dvou názorů, by se v anglické podobě dala parafrázovat Shakespearovými slovy: „SP or not to SP“. A jelikož nerad nechávám věci být jen v obecné rovině a pouze na úrovni planých diskuzí, pokusil jsem se jít v tomto tématu maximálně do hloubky a shromáždit fakta, která by mohla napomoci v rozhodování. Pokusil jsem se celou problematiku rozdělit do několika bodů a najít, co nejvíce faktů. Ale nýní k samotnému tému (předem upozorňuji, že primárně se toto téma týká Microsoft SQL Serveru 2000).
Výkon uložených procedur a adhoc dotazů
Výkon je zcela jistě jeden z nejdůležitějších faktorů, který ovlivňuje výběr technologie a často lze vidět nekončící diskuze o tom, zdali uložené procedury a nebo adhoc dotazy jsou výkonnější. Abychom tohle mohli zhodnotit, musíme vědet, jak jsou dotazy zpracovávány.
Co se tedy děje v MS SQL Serveru ve chvíli, kdy se má provést libovolný T-SQL příkaz? V krátkosti to výjádříme v následujícím výčtu:
- Parser zkontroluje syntaxy a správnost příkazu a zkonveruje T-SQL příkaz do interního formátu serveru, který je znám jako sequence tree a nebo také query tree.
- Exekuční plán je následně vygenerován pro query tree a následně celý příkaz je zkompilován a optimalizován (přitom v exekučním plánu jsou zahrnuty všechny návazné události jako triggery, vyvolání „konstrainů“ atd.).
- Jakmile je exekuční plán hotov, pak každý jeho jednotlivý prvek je přesměrován na odpovídajícího správce, který jej dále optimalizuje a provádí (např. SELECT je odeslán na DML správce atd.). Co je zde úplně zásadní si uvědomit je to, že exekuční plán není pro uložené procedury ukládán na disk a je tedy dynamický (zkušení administrátoři i vývojáři obvykle proto píší tzv. „startup“ procedury, které vyvolají v odpovídající sekvenci požadované procedury při restartu SQL Serveru a tím vytvoří exekuční plány).
- Rekompilace exekučního plán. Exekuční plán se může dynamicky měnit a v případě uložených procedur je možné toto explicitně vyvolat pomocí klauzule WITH RECOMPILE pro určité specifické scénáře (jinak tyto rekompilace prakticky probíhají vždy pro adhoc dotazy, ale né úplně vždy, jak dále popíšu).
V krátkosti lze tedy proces, který se odhrává v SQL Serveru při zpracování T-SQL, zobrazit následovně:
Další obrázek pak ukazuje rozdíl mezi uloženými procedůrami a adhod dotazy:
Z tohoto schématu je vidět, že pokud se neprovedou další optimalizace, pak mohou být adhoc dotazy méně výhodné i s ohledem na jejich rekompilování.
Jednoduchý test na výkon
Pro představu můžeme implementovat triviální test, který je uveden níže.
SQL skript pro vygenerování primitivní tabulky:
if exists (select 1
from sysobjects
where id = object_id('TESTTABLE')
and type = 'U')
drop table TESTTABLE
go
create table TESTTABLE (
TABLEID integer identity,
NAME nchar(30) not null,
constraint PK_TESTTABLE primary key (TABLEID)
)
go
SQL skript pro uloženou proceduru:
CREATE PROCEDURE dbo.p_InsertTempTableRow
(
@Name nchar(30)
)
AS
SET NOCOUNT ON
INSERT INTO TestTable (Name) VALUES (@Name)
GO
Zdrojový kod aplikace:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace SampleApp
{
class Class1
{
static void Main(string[] args)
{
int cycle = 50000;
string strValue = "Some name')";
StringBuilder sb = new StringBuilder();
SqlConnection conn = new SqlConnection("Integrated Security=yes;Initial Catalog=dotnetjob;Data Source=(local)");
conn.Open();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandType = CommandType.Text;
long startLong = DateTime.Now.Ticks;
for(int i=0; i< cycle; i++)
{
// first initialization
sb.Append("INSERT INTO TestTable (Name) VALUES ('");
// set variable parameter into inline sql
sb.Append(strValue);
command.CommandText = sb.ToString();
command.ExecuteNonQuery();
sb.Remove(0, sb.Length);
}
long endLong = DateTime.Now.Ticks;
Console.WriteLine("AdHoc SQL - Ticks property:" + (endLong - startLong));
command = new SqlCommand("p_InsertTempTableRow", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Name", SqlDbType.NChar, 50);
long startLongSP = DateTime.Now.Ticks;
for(int i=0; i< cycle; i++)
{
command.Parameters["@Name"].Value = strValue;
command.ExecuteNonQuery();
}
long endLongSP = DateTime.Now.Ticks;
Console.WriteLine("Stored procedure - Ticks property:" + (endLongSP - startLongSP));
Console.WriteLine("Rozdil mezi SP a AdHoc {0}", ((endLongSP - startLongSP) - (endLong - startLong)));
conn.Close();
}
}
}
Výsledek běhu, kdy záporná hodnota značí hodnotu, o kterou se SP provedla rychleji v porovnání s adhoc příkazem.
Tyto výsledky odrážejí skutečnost, kdy SQL Server detekuje stejné dotazy v rámci každého příkazu. Z tohoto důvodu nedochází u adhoc dotazů k prvním dvou krokům (parsování a kompilace) a příkaz je prakticky okamžitě proveden a fakticky se jedná o podobný exekuční plán jako v případě uložené procedury.
Nyní změníme kód tak, aby došlo v rámci každého dotazu ke změně hodnoty sloupce nově vkládaného záznamu.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace SampleApp
{
class Class1
{
static void Main(string[] args)
{
int cycle = 50000;
string strValue = "ID#";
StringBuilder sb = new StringBuilder();
SqlConnection conn = new SqlConnection("Integrated Security=yes;Initial Catalog=dotnetjob;Data Source=(local)");
conn.Open();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandType = CommandType.Text;
long startLong = DateTime.Now.Ticks;
for(int i=0; i< cycle; i++)
{
sb.Append("INSERT INTO TestTable (Name) VALUES ('");
sb.Append(strValue);
sb.Append(i);
sb.Append("')");
command.CommandText = sb.ToString();
command.ExecuteNonQuery();
sb.Remove(0, sb.Length);
}
long endLong = DateTime.Now.Ticks;
Console.WriteLine("AdHoc SQL - Ticks property:" + (endLong - startLong));
command = new SqlCommand("p_InsertTempTableRow", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Name", SqlDbType.NChar, 50);
sb.Remove(0, sb.Length);
long startLongSP = DateTime.Now.Ticks;
for(int i=0; i< cycle; i++)
{
sb.Append(strValue);
sb.Append(i);
command.Parameters["@Name"].Value = sb.ToString();
command.ExecuteNonQuery();
sb.Remove(0, sb.Length);
}
long endLongSP = DateTime.Now.Ticks;
Console.WriteLine("Stored procedure - Ticks property:" + (endLongSP - startLongSP));
Console.WriteLine("Rozdil mezi SP a AdHoc {0}", ((endLongSP - startLongSP) - (endLong - startLong)));
conn.Close();
}
}
}
Výsledky při změně parametru pro každý dotaz
Pokud bychom mluvili o adhoc dotazech, pak je toto řešení ještě dále rozšířit o výkon a systémové prostředky, které si vyžadá další OM vrstva, kde bude ještě docházet k dalšímu snížení výkonu (to není simulováno v tomto příkladě), kdy se předpokladá přímé vkládání SQL dotazů v rámci kódu a neexistence OM vrstvy z důvodu maximálního přiblížení se k databázi a k maximálnímu výkonu.
Uložené procedury jako rozhraní
V případě diskuze o uložených procedurách se velmi často používají argumenty o tom, že jejich použití neodpovídá OOP principů (jak již naznačila diskuze, která mne vedla k tomuto článku). Osobně už jsem poměrně alergický na neustálé zmiňování OOP jako všeléku na vše a používání OOP principů bez úvahy a důsledků pro samotného klienta a uplatnění řešení a brát za axiomatické např. držení obchodní logiky pouze ve střední vrstvě může být zničující. Pokusím se uvést dva příklady:
- IT firma implementující svůj produkt, který má být založen na libovolném datovém zdroji.
- Bankovní dům, kterým má mnoho různých produktů a systémů, které se liší v technologiích, použití, ale obvykle sdílí jeden základní datový zdroj (i když může být na různých produtech, ale v základě je daná funkcionalita zapouzdřena do jednoho řešení).
První případ je typickou ukázkou na použití nezávislé DAL vrstvy (fakticky, je to model 1 : n, 1 produkt nad n databázemi). V opačném případě, pokud by byla obchodní logika držena v datové vrstvě v podobě uložených procedur, pak toto řešení bude mít přímo katastrofální důsledky na prodej daného produktu (pokud by nenastal ten předpoklad, že by na světě existovala pouze jedna databáze, pro kterou IT firma svůj produkt vytvořila a pokud by SQL implementace byla jednotná ve všech databázích). Takže řešení v podobě uložených procedur je zcela nevhodné, co do přesunu obchodní logiky do datové vrstvy a to i jen z malé části.
Ve druhém případě můžeme předpokládat, že bankovní ústav existuje již určitý delší čas a z tohoto důvodu u něj najdeme mnoho rozdílných technologií a přístupů, které byly poplatné dané době a znalostem. Kromě toho velmi zřídka uvidíme jediné monolitické řešení, které bude založené na jediné technologii a lze tedy očekávat, že bude požadováno sdílet základní funkčnost napříč legacy systémy tak i novými technologiemi (jedná se o model n : 1, pokud je standardizována databáze a nebo n : m).
Z tohoto důvodu bude v tomto případě působit katastrofálně, pokud budeme implementovat pomocí pouze jediné technologie onu OM vrstvu, která v lepším případě bude moci být využitá s určitými „bridges“, „wrappery“ a webservices i v ostatních odlišných platformách a technicky rozdílných systémech (fakticky deklarujeme vztah 1 : 1 nebo 1 : n, protože odřízneme jiné technologie, kde OM vrstva již nebude použitelná). Zde pak uložené procedury mohou působit jako základní vrstva s funkčností, kterou je možné zapouzdřit onu základní požadovanou funkcionalitu sdílenou napříč rozdílnými aplikacemi a technologiemi.
Pokud bychom se chtěli bezhlavě řídit určitými „OOP“ poučkami, tak budeme vytvářet reduntdantní kód pro jiné systémy přičem výsledek pro klienta nemusí vůbec znamenat úsporu nákladu díky znovupoužitelnosti kódu, ale naopak (v určitých případech může totéž platit i pro zmíněný databázový příklad).
V principu je tento přístup pouze abstrahováním datové vrstvy a přístup je obdobný jako v případě implementace např. prostředí .NET Framework, Java SDK nebo MFC, které jsou fakticky pouze jakýmsi objektovým zapouzdřením funkčnosti, která je v podobě základních služeb implementována operačním systémem a poskytována aplikacím v podobě Win32 API. Pokud provedeme tuto asociaci, pak můžete uložené procedury pokládat za definici jakéhosi elementárního funkčního rozhraní, které je využíváno a nebo zapouzdřeno.
Bezpečnost
Bezpečnost je jedním z argumentů, který je používán hodně často s ohledem na uložené procedury. A to z důvodu spíše empirických, kdy obvykle dochází k tomu, že pokud vývojáři chtějí využívat adhoc dotazy, pak obvykle dostanou plný přístup k datům prakticky se rovnající možnostem práce administrátora nad danou databází či požadovanou skupinou tabulek.
To je pochopitelně velmi špatný nešvar, který ale nic neříká o tom, že uložené procedury jsou v principu (ne)bezpečnější. Proto pokud se podíváme na SP a na adhoc dotazy, tak SP procedury můžeme hodnotit jako spíše snažší na správu a prehlednější na konfiguraci práv.
Je pochopitelné, že vyšší granularita práv, které je u adhoc dotazů nutná, dává jak vyšší možnosti k jemnému nastavení bezpečnosti systému, tak ale zároveň mnohonásobně vyšší pravděpodobnost chyby, která je dáná čistě jen vyšším počtem nutných nastavení a kontrol. Proto pokud toto budeme hodnotit z pohledu bezpečnostního odborníka, pak jeho snahou bude zcela jistě minimalizovat tzv. attack surface.
Takže zde je nutné takovéto rozhodnutí nechat spíše na bezpečnostním oddělení, které může pomocí „data-flow“ modelů určit možná rizika a následně propočítat pravděpodobnost chyby a případně najít vhodná řešení, jak ji minimalizovat.
Kromě toho se zcela jistě nevyhneme unit testování (i s ohledem na oprávněnost přístupu k datům), kontrola „kvalitou“ a v neposlední řadě bezpečnostnímu auditu kódu aplikace (pokud má být jistota, že aplikace neobsahuje nebezpečný kód v podobě interně implementovaného trojského kóně).
Pokud budeme implementovat přístup k databázi v podobě OM vrstvy a tato vrstva poběží ve stejném adresovém prostoru a stejném účtu jako zbývající aplikační kód, který je vyvíjen mnohdy stovkami programátorů (interních, kontraktorů nebo dalších subdodavatelů). Pak si musíme uvědomit, že tento aplikační kód může mít shodný přístup k databázi jako bezpečnostně kritická OM vrstva (pokud nebudeme separovat OM vrstvu a aplikační logiku v jiných procesech a účtech a integrovat je pomocí vzdáleného volání).
Takže kontrola a audit kódu, v případě takovýchto kritických systémů, je zcela nezbytná a velmi náročná jak finančně tak i organizačně.
Je pravda, že je možné definovat omezenou skupinu vývojářů a následně pak vytvořit aplikační vrstvy, které poskytují své služby na základě silné kryptografie a autentikace (např. v .NET na základě Windows účtů, pod kterými mohou být jednotlivé aplikační části imperzonifikovány a nebo podle „strong name“ a samozřejmě je k dispozici i mnoho dalších možností). Toto ovšem vyžaduje jak velmi hlubokou znalost bezpečnostních principů dané technologie a infrastruktury (tedy v .NET je to znalost způsobu přiřazování „permissions“ a následně pod tím běžící ověřování ve Windows) a kromě toho vede k více něž znatelnému zpomalení aplikace (např. každé volání na metodu GetConnection se musí prokázat kryptografickým klíčem/autentikačním tokenem, což znamená jak konzumaci mnoha systémových prostředků tak i velmi často opakovanou zátěž na systém – a tato redundatní kontrola je provedena ještě jednou na databázovém serveru).
Toto řešení dává možnost kontroly nad týmem a omezením zneužití případného neoprávněného přístupu k datům, kdy obchodní vrstva je zcela odstíněna od OM vrstvy použitím vestavěných bezpečnostních mechanismů. Co je ale problém je redundantnost takové funkčnosti, protože tím, že jí implementujeme v rámci základního frameworku našeho systému, tak tím neodpadá nutnost konfirovat bezpečnost v rámci databázového serveru.
Závěr
Tímto článkem jsem nechtěl říci, které řešení je lepší, ale především upozornit na to, že často jsme v zajetí modních termínů a přístupů, kdy se zaštiťujeme pojmy jako OOP, design patterns a mnoho dalších zcela notoricky známých a jsou přijímány určité axiomy IT technologií, aniž by docházelo k jejich zvážení a kritické revizi.
Proto univerzální odpověď na to, zdali preferovat SP nebo adhoc dotazy neexistuje, pokud není známo, v jakém kontextu je kladena a osobně jsem se snažil, abych ukázal některé faktory, které mají být vzaty v úvahu k nalezení co nejvíce správné odpovědi.