Obecné praktiky práce s DB

Lubomír AndrleLubomir.andrle@unicorn.eu

Osnova

• Datová vrstva z pohledu programátora• Typické úlohy a problémy• SQL tipy• Integrace pomocí DB

Přehled vrstev systému

Datová vrstva• Služby pro ukládání a načítání dat, dotazování• Většinou reprezentovaná relačním databázovým systémem

– Oracle– MS SQL– MySQL– …

• Typické objekty– Tabulky– Pohledy– Uložené procedury

• Časté využití O-R mapování pro přístup k datům z business vrstvy– Mapování objektové struktury aplikace na relační strukturu databáze

• Objektové databáze

Datová vrstva v N-vrstvé aplikaci I

Datová vrstva v N-vrstvé aplikaci II

• Zajišťuje persistenci dat• Zprostředkovává jednotný přístup k datům• Typově jde obvykle o OLTP databázi rozšířenou

o dávkové zpracování• Nejčastější implementace– Relační databáze– Soubory– Objektové databáze

Přístup k datům I.

• Probíhá pomocí dotazovacích jazyků– SQL (Standard Query Language) pro relační databáze– OQL (Object Query Language) pro objektové databáze– Nativním prostředky dané platformy (např. COM objekty)

• SQL– Standardizováno ANSI normami– Neustále se vyvíjí– Jednotlivé relační databáze

• Nepokrývají standardy na 100%• Obsahují specifická rozšíření• Implementace jednotlivých elementů (např. transakce)

nemusí být shodná

Přístup k datům II.

• Standardizovaná rozhraní (ovladače)– Definují API mezi obchodní logikou a databází– Unifikují jednotlivá API databázových systémů– Případně softwarově simulují chybějící funkce

Přístup k datům III.

• Nejběžnější API– ODBC (standard C)– JDBC (Java)– ADO (.NET)– OLE DB• Microsoft specifické řešení postavené na COM

objektech• Podporuje i nerelační databáze (např. MS Excel)• Dosud podporováno zejména v MS SQL

TYPICKÉ ÚLOHY A PROBLÉMY

Konkurenční přístup k datům I.

• Změny prováděné v databázi probíhají v transakcích• Tyto transakce splňují tzv. ACID pravidla

– Atomic– Consistent– Isolation– Durability

• Přístup k datům je interně řízen pomocí mechanismu zámků

• Implementace těchto pravidel v jednotlivých databázích není 100% shodné

Konkurenční přístup k datům II.

• Malé srovnání Oracle a MS SQL

Optimalizace výkonu - indexy

• Nástroj pro optimalizaci rychlosti přístupu k datům• Databáze obvykle automaticky vytvoří index

– Pod primárním klíčem– Pod alternativním klíčem

• Indexy pod cizími klíči– Vhodné téměř ve všech případech, kromě odkazů na číselníkové

tabulky o několika málo záznamech– Je nutné definovat manuálně

• Databáze nabízejí specializované typy indexů kromě standardního B-Tree – např. clustered, bitmap, full-text

Historická data• Jak řešit historická data v tabulkách?• Je nutné začít již u datového modelu

– Historické záznamy musí být identifikovatelné (např. datum vytvoření)– Musí být možné udělat konzistentní časový řez daty

• Možná řešení– Pravidelný přesun do historických tabulek– Nastavením historizačního flagu (vhodné u menších tabulek)– „Historické“ záznamy lze skrýt před BL pomocí pohledů

• Implementace partitioningu – Pomůže eliminovat potřebu historických tabulek– Nezachrání chybný návrh, stále je potřeba „partition key“– Oracle nyní podporuje automatické vytváření partition

SQL TIPY

Databázové triggery

• Kdy ano?– DB scope– Pro vytváření speciálních auditních záznamů (autonomními

transakcemi)

• Pro účely komplexní validace– Vylepšený check

• Proč jinde ne?– Skryté před vývojáři BL– Štěpí BL do více vrstev– Obtížně debugovatelné

Funkce ve „where“ klauzuli SQL

• Obecně jde o „špatný nápad“– Nepředvídatelný počet provedení (závisí na prováděcím

plánu).– Performance dopad– Může vést na skryté implicitní konverze datových typů– V N-vrstvé architektuře lze funkci obvykle vyhodnotit v BL

• Pokud přeci jen není vyhnutí– Funkce by měla být deterministická– Neprovádí změny stavu db

Výčty v IN klauzuly dotazu• Jak řešit dlouhé výčty hodnot v IN operátoru?

– Příklad: Select kódUC from tPráva where role in (role1, role2, role3, …, roleN)

• Nevýhody– Snížený výkon (každá varianta dotazu je originál)– Vede na velmi podobné dotazy (a tím pádem i hash kódy do SQL

cache)– Dotaz je nutné pokaždé dynamicky skládat

• Možná řešení– Batch operace– Oracle podporuje předávání seznamů (polí)– Použití vnořeného dotazu může být efektivnější

Select kódUC from tPráva where role in (select role from tPřiřazeníRolí where uživatel=uživ1)

Načtení hodnoty atributu do proměnné

• Oracle– Příkaz: select attr into var from table where podmínky

• Podmínce vyhovuje 0 záznamů: výjimka NO_DATA_FOUND• Podmínce vyhovuje více než 1 záznam: výjimka TOO_MANY_ROWS

• MS SQL– Příkaz: select @var = attr from table where podmínky

• Podmínce vyhovuje 0 záznamů: vrátí hodnotu NULL• Podmínce vyhovuje více než 1 záznam: vrátí hodnotu posledního

záznamu!• Počet nalezených záznamů nutné vyhodnocovat přes proměnnou

@@ROWCOUNT ihned po provedení SQL dotazu.

• Jde o naprosto odlišné chování při stejné operaci!!!

SQL dotazy – filtrování a vyhledávání

• Co je špatně na tomto kódu?// načte hlášky z loguDataSet ds = execute(„select * from LOG_TABLE“)// vyfiltruje jen chybové hlášky return extractErrors(ds);

• Filtrujte a vyhledávejte výhradně na datové vrstvě– Ulehčíte paměti i procesoru

Taky nezapomeňte …• Řazení dat pomocí „order by“ je ovlivněno regionálním

nastavením databáze a session, která operaci provádí• Oracle podporuje operátor „connect by“ pro procházení

stromové struktury• Dávkové zpracování v jedné transakci může trvat dlouho

– V Oracle pouze držíte zámky (celkem levné)– V MS SQL držíte serverové zdroje, může dojít k eskalaci zámků a

deadlockům• Dynamický datový model (vytváření tabulek online) může být

možným řešením, ale nejspíš narazíte na odpor DBA

INTEGRACE POMOCÍ DB

Integrace na databázové úrovni• Základní principy

– Oddělení konzumenta od interních datových struktur– Každý konzument má obvykle svoje vlastní db objekty tvořící rozhraní– Rozhraní používají pokud možno pouze základní datové typy– Rozhraní konzument by mělo být snadno odpojitelné

• Možné implementace– Pomocí databázových pohledů / snapshotů– S využitím speciálních tabulek– Za pomoci nativních prostředků databáze (např. Oracle Streams)

• Nutné vyřešit synchronizace přístupů k rozhraní– Čtení vs zápis (obvykle nebude transakční)– Lze implementovat specializovanou řídící tabulkou

Sdílená databáze

• Základní princip - přístup do „cizí“ DB pro data• Přístup přes DB link (A) nebo dedikovaný data

source (B)

• Dávková i online integrace• Synchronní i asynchronní

Integrace na DB úrovni - příklad

Sdílená databáze – „online“ integrace

• „Online“ integrace – přístup na živá data– Volání uložené procedury/funkce– Přístup do „online“ tabulek

• Na co si dát pozor– Nutná definice formálního rozhraní pro integraci – Menší závislost na změnách vnitřních tabulek systému– DB zámky na živých datech mohou blokovat online zpracování– Řízení zátěže

• Jemné omezení: povoleny pouze konkrétní operace (přístup přes DB funkci)• Hrubé omezení: profily,...

• Často „quick hack“ integrace – Nevyžaduje žádné složité budování infrastruktury– Problematický pattern z pohledu mj. škálovatelnosti a údržby … mnohde

zakázané

Sdílená databáze – „offline“ integrace

• „Offline“ integrace– Data jsou připravena v dedikovaných tabulkách – Přenos dat (celé nebo části) do cílového systému nebo

pouze dotazování nad daty– Použití

• Reporting - přímá podpora v reportovacích nástrojích (Cognos)• Přenos dat do centralizovaného úložiště (ODS)

• Na co si dát pozor– Stejné jako u online integrace– Zamykání není většinou tak kritické

Q & A