Wydawnictwo Helion
ul. Koœciuszki 1c
44-100 Gliwice
tel. [phone]

e-mail: [e-mail]

SQL. Sztuka
programowania

Wypowiedz wojnê niewydajnym bazom danych

• Projektowanie wydajnych baz danych
• Uwzglêdnianie kontekstu dzia³ania aplikacji bazodanowych
• Poprawa szybkoœci dzia³ania Ÿle zaprojektowanych systemów

Twoje bazy danych dzia³aj¹ zbyt wolno? Pora to zmieniæ! Wraz ze wzrostem wielkoœci
korporacyjnych baz danych czas dostêpu do nich ma coraz wiêksze znaczenie. Napisanie
poprawnie dzia³aj¹cego kodu w jêzyku SQL nie jest trudne, jednak tworzenie wydajnych
aplikacji bazodanowych jest prawdziw¹ sztuk¹. Jak mo¿esz zg³êbiæ jej tajniki i staæ siê
lepszym programist¹? Zdaniem autora tej ksi¹¿ki nauka wydajnej pracy z bazami danych
przypomina poznawanie zasad prowadzenia wojny, dlatego wzorem klasycznej pozycji
„Sztuka wojny” autorstwa Sun Tzu prowadzi Ciê on przez poszczególne etapy kampanii
przeciwko nieefektywnie zaprojektowanym i napisanym aplikacjom bazodanowym.

„SQL. Sztuka programowania” to praktyczny podrêcznik, dziêki któremu szybko
poszerzysz sw¹ wiedzê w zakresie efektywnego stosowania jêzyka SQL. Nauczysz siê
dbaæ o wydajnoœæ aplikacji ju¿ na etapie ich projektowania, a tak¿e myœleæ o pracy
z bazami danych w kategoriach procesów, wykraczaj¹c poza same zapytania jêzyka
SQL. Dowiesz siê, jak poprawnie u¿ywaæ indeksów oraz jak monitorowaæ szybkoœæ
dzia³ania bazy. Poznasz standardowe scenariusze zwiêkszania wydajnoœci, które
pozwol¹ Ci zastosowaæ sprawdzone fortele we w³asnych projektach oraz w bazach
zaprojektowanych przez innych programistów.

• Projektowanie pod k¹tem wydajnoœci
• Efektywne korzystanie z baz danych w programach
• Poprawne stosowanie indeksów
• Projektowanie optymalnych zapytañ SQL
• Praca z du¿ymi zbiorami danych
• Korzystanie ze struktur drzewiastych
• Monitorowanie wydajnoœci
• Obs³uga wspó³bie¿noœci
• Radzenie sobie z niewydajnymi projektami

Poznaj praktyczne techniki poprawy wydajnoœci baz danych

Autorzy: Stéphane Faroult, Peter Robson
T³umaczenie: Marek Pêtlicki
ISBN: 978-83-246-0895-9
Tytu³ orygina³u:

The Art of SQL

Format: B5, stron: 472

S P I S T R E

¥ C I

Wst

¤p

7

1.

Plany strategiczne

15

Projektowanie baz danych pod k

 tem wydajnoĿci

2.

Prowadzenie wojny

51

Wydajne wykorzystanie baz danych

3.

Dzia

¨ania taktyczne

87

Indeksowanie

4.

Manewrowanie

113

Projektowanie zapyta

Ĭ SQL

5.

Ukszta

¨towanie terenu

151

Zrozumienie implementacji fizycznej

6.

Dziewi

¤ø zmiennych

179

Rozpoznawanie klasycznych wzorców SQL

7.

Odmiany taktyki

231

Obs

¨uga danych strategicznych

8.

Strategiczna si

¨a wojskowa

273

Rozpoznawanie trudnych sytuacji i post

¤powanie w nich

9.

Walka na wielu frontach

307

Wykorzystanie wspó

¨bieŞnoĿci

10.

Gromadzenie si

¨

337

Obs

¨uga duŞych iloĿci danych

11.

Fortele

381

Jak uratowa

ø czasy reakcji

12.

Zatrudnianie szpiegów

417

Monitorowanie wydajno

Ŀci

Ilustracje

451

O autorach

453

Skorowidz

455

R O Z D Z I A

§ D R U G I

Prowadzenie wojny

Wydajne wykorzystanie baz danych

Il existe un petit nombre de principes fondamentaux de la guerre,

dont on ne saurait s’écarter sans danger, et dont l’application au contraire

a été presque en tous temps couronnée par le succès.

Istnieje niewielka liczba fundamentalnych zagadnie

Ĭ zwi zanych z prowadzeniem wojny,

których nie wolno lekcewa

Şyø: zaprawd¤, stosowanie si¤ do nich prawie niezawodnie

prowadzi do sukcesu.

— Genera

¨ Antoine-Henri de Jomini (1779 – 1869)

Zarys sztuki wojennej

5 2

R O Z D Z I A

’ DRUGI

a

ĝdy, kto byï zaangaĝowany w proces przejĂcia projektu z fazy rozwoju

w faz

Ú produkcyjnÈ, przyzna z pewnoĂciÈ, ĝe prawie czuï tumult i wrzawÚ

bitwy. Bardzo cz

Ústo zdarza siÚ, ĝe na kilka tygodni przed sÈdnym dniem

przeprowadzone testy wydajno

Ăci ujawniajÈ smutny fakt: system nie

b

Údzie dziaïaï tak pïynnie, jak to zakïadano. Zaprasza siÚ ekspertów,

optymalizuje zapytania SQL, w kryzysowych burzach mózgów bior

È udziaï

administratorzy baz danych i systemów. W ko

ñcowym rozrachunku na

sprz

Úcie dwukrotnie bardziej kosztownym osiÈga siÚ wydajnoĂÊ jedynie

teoretycznie zbli

ĝonÈ do zakïadanej.

Cz

Ústo w zastÚpstwie dziaïañ strategicznych stosuje siÚ dziaïania taktyczne.

Strategia wymaga zastosowania architektury i modelu przystosowanych do
wymaga

ñ projektu. Podstawowych zasad stosowanych w czasie wojny jest

zaledwie kilka, ale zadziwiaj

Èco czÚsto bywajÈ one ignorowane. BïÚdy

w architekturze okazuj

È siÚ niezwykle kosztowne, a programista SQL-a

musi wkracza

Ê na pole bitwy dobrze przygotowany do walki, musi wiedzieÊ,

gdzie chce dotrze

Ê i którÈ drogÈ. W tym rozdziale przeanalizujemy

podstawowe cele zwi

ÚkszajÈce szanse na sukces w pisaniu programów

w sposób efektywny wykorzystuj

Ècych bazy danych.

Identyfikacja zapyta

ñ

Przez stulecia jedynym sposobem, w jaki genera

ï mógï ĂledziÊ losy bitwy,

by

ïa obserwacja oddziaïów na podstawie kolorów umundurowania

i niesionych przez nich proporców. Gdy jaki

Ă proces w Ărodowisku bazy

danych zu

ĝywa nadmiernÈ iloĂÊ mocy procesora, czÚsto istnieje moĝliwoĂÊ

zidentyfikowania zapytania SQL odpowiedzialnego za to zadanie.
Nierzadko jednak trudne bywa odkrycie tego, która cz

ÚĂÊ aplikacji

wywo

ïaïa problematyczne zapytanie, szczególnie w Ărodowisku, w którym

wykorzystywane s

È zapytania budowane w sposób dynamiczny. Mimo tego

ĝe wiele solidnych produktów wyposaĝonych jest w mechanizmy
monitoruj

Èce, czÚsto zdumiewajÈco trudno jest znaleěÊ odniesienie miÚdzy

zapytaniem SQL a

Ărodowiskiem, w którym ono dziaïa. Z tego powodu

dobrze jest nabra

Ê nawyku oznaczania programów i krytycznych moduïów

przez w

ïÈczanie komentarzy w kodzie SQL w taki sposób, aby ïatwo byïo

zidentyfikowa

Ê ěródïo kïopotów. Na przykïad:

/* REJESTRACJA KLIENTA */ select ...

K

P R O W A D Z E N I E W O J N Y

5 3

Tego typu komentarze identyfikuj

Èce mogÈ byÊ pomocne w Ăledzeniu

b

ïÚdnie dziaïajÈcego kodu. MogÈ byÊ równieĝ pomocne przy okreĂlaniu

obci

Èĝenia serwera bazy danych powodowanego przez kaĝdÈ korzystajÈcÈ

z niego aplikacj

Ú, szczególnie w przypadku, gdy spodziewamy siÚ, ĝe

wprowadzane zmiany mog

È spowodowaÊ zwiÚkszenie obciÈĝenia, i musimy

oszacowa

Ê, czy posiadany sprzÚt ma szansÚ sprostaÊ wiÚkszym wymaganiom.

Niektóre produkty posiadaj

È specjalizowane mechanizmy rejestrujÈce,

które pozwalaj

È uniknÈÊ komentowania kaĝdego wyraĝenia. Na przykïad

pakiet

dbms_application_info

serwera Oracle pozwala oznakowa

Ê program

za pomoc

È 48-znakowej nazwy moduïu, 32-znakowej nazwy akcji

i 64-znakowego pola informacji o kliencie. Zawarto

ĂÊ tych pól jest

kontrolowana przez twórc

Ú aplikacji. W Ărodowisku Oracle moĝna uĝyÊ

tego pakietu do

Ăledzenia tego, jaka aplikacja wykorzystuje bazÚ danych

w danej chwili, jak równie

ĝ tego, co dana aplikacja robi. Do tego sïuĝÈ

dynamiczne perspektywy

V$

, za pomoc

È których moĝna ĂledziÊ stan

pami

Úci.

Identyfikowanie wyra

ĝeñ uïatwia Ăledzenie zaleĝnoĂci obciÈĝenia.

Trwa

ïe poïÈczenia do bazy danych

Nowe po

ïÈczenie do bazy danych tworzy siÚ szybko i ïatwo, lecz ta

prostota czasem przes

ïania fakt, ĝe szybkie, cykliczne wywoïania poïÈczeñ

wi

ÈĝÈ siÚ z konkretnym, niemaïym kosztem. Dlatego poïÈczenia z bazÈ

danych warto traktowa

Ê z rozwagÈ. Konsekwencje wywoïywania wielu

cyklicznych po

ïÈczeñ, byÊ moĝe ukrytych w ramach aplikacji, mogÈ byÊ

znacz

Èce, co zademonstruje kolejny przykïad.

Jaki

Ă czas temu trafiïem na aplikacjÚ, która przetwarzaïa duĝÈ liczbÚ

niewielkich plików tekstowych o rozmiarach do stu wierszy. Ka

ĝdy wiersz

zawiera

ï dane oraz identyfikator bazy danych, do której dane te miaïy byÊ

za

ïadowane. W tym przypadku byï wykorzystywany tylko jeden serwer

bazy danych, ale prezentowana zasada obowi

Èzuje równieĝ w przypadku

setek baz danych.

5 4

R O Z D Z I A

’ DRUGI

Przetwarzanie ka

ĝdego z plików odbywaïo siÚ zgodnie z nastÚpujÈcÈ

procedur

È:

Otwarcie pliku
Aĝ do napotkania koñca pliku
Odczytaj wiersz
PoïÈcz siÚ z serwerem zdefiniowanym w treĂci
Wpisz dane
Zamknij poïÈczenie
Zamknij plik

Opisany proces dzia

ïaï zadowalajÈco z wyjÈtkiem przypadków, gdy do

za

ïadowania trafiaïa duĝa liczba niewielkich plików w odstÚpach czasu

przekraczaj

Ècych moĝliwoĂÊ ich przetworzenia przez aplikacjÚ.

To powodowa

ïo znaczny przyrost dziennika zalegïoĂci w bazie danych

(ang. backlog), który nast

Úpnie musiaï byÊ przetworzony przez bazÚ

danych, co zajmowa

ïo dodatkowy czas.

U

ĝytkownikowi bazy danych wyjaĂniïem przyczynÚ opóěnieñ, którÈ byïa

nadmierna liczba otwieranych i zamykanych po

ïÈczeñ. Jako demonstracjÚ

problemu przygotowa

ïem prosty program (napisany w C) emulujÈcy

aplikacj

Ú oraz proponowane przeze mnie rozwiÈzania. Wyniki dziaïania

demonstracji przedstawia tabela 2.1.

UWAGA

Program generuj

Ècy wyniki z tabeli 2.1 wykorzystywaï proste instrukcje

wstawiaj

Èce wiersze do tabel. Klientowi wspomniaïem równieĝ o technikach

bezpo

Ăredniego ïadowania danych do tabel, które dziaïajÈ jeszcze szybciej.

TABELA 2.1. Wyniki testu nawi

 zywania i koĬczenia po¨ czeĬ

Test

Wynik

Nawi

 zanie i zakoĬczenie po¨ czenia dla kaŞdego wstawianego

wiersza z pliku

7,4 wiersza na sekund

¤

Jedno po

¨ czenie, kaŞdy wiersz wstawiany indywidualnie

1681 wierszy na sekund

¤

Jedno po

¨ czenie, wiersze wstawiane w porcjach po 10

5914 wierszy na sekund

¤

Jedno po

¨ czenie, wiersze wstawiane w porcjach po 100

9190 wierszy na sekund

¤

Ta demonstracja pokaza

ïa, jak istotne jest, aby minimalizowaÊ liczbÚ

osobnych po

ïÈczeñ z bazÈ danych. Z tego powodu kluczowÈ rolÚ odgrywaïo

tu proste sprawdzenie, czy kolejne wprowadzenie danych ma odby

Ê siÚ do

tej samej bazy, do której po

ïÈczenie juĝ zostaïo otwarte. AnalizÚ moĝna by

P R O W A D Z E N I E W O J N Y

5 5

poprowadzi

Ê dalej, poniewaĝ liczba moĝliwych baz danych byïa oczywiĂcie

ograniczona. Teoretycznie mo

ĝna byïo osiÈgnÈÊ dalszy zysk wydajnoĂci,

wykorzystuj

Èc tablicÚ uchwytów poïÈczeñ, po jednym dla kaĝdej z moĝliwych

baz danych, i nawi

ÈzujÈc poïÈczenie dopiero wtedy, gdy bÚdzie to konieczne.

W ten sposób wykorzystywanych by

ïoby maksymalnie tyle uchwytów, ile

istnieje baz danych. Jak wida

Ê w tabeli 2.1, prosta technika pojedynczego

po

ïÈczenia (lub minimalizacji liczby nawiÈzywanych poïÈczeñ) usprawnia

wydajno

ĂÊ do dwustu razy. I to wszystko dziÚki niewielkiej modyfikacji kodu.

Oczywi

Ăcie przy tej okazji mogïem zademonstrowaÊ równieĝ znaczÈce

korzy

Ăci wynikajÈce z ograniczenia liczby wywoïañ miÚdzy aplikacjÈ

a baz

È danych, wykorzystujÈc ïadowanie danych za pomocÈ tablic.

Przez wstawianie wielu wierszy w pojedynczej operacji ca

ïe zadanie

mo

ĝna przyspieszyÊ jeszcze piÚciokrotnie. Wyniki z tabeli 2.1 pokazujÈ

przyspieszenie o tysi

Èc dwieĂcie razy w stosunku do pierwotnej

wydajno

Ăci operacji.

Sk

Èd bierze siÚ tak znaczne przyspieszenie?

Przyczyn

È pierwszego przyspieszenia jest fakt, ĝe nawiÈzanie poïÈczenia

z baz

È danych jest operacjÈ „ciÚĝkÈ”, to znaczy wykorzystujÈcÈ duĝo

zasobów systemowych.

We wci

Èĝ popularnym Ărodowisku klient-serwer nawiÈzanie poïÈczenia

to prosta operacja, co powoduje,

ĝe niewiele osób ma ĂwiadomoĂÊ

kryj

Ècego siÚ za niÈ procesu. W pierwszym etapie klient musi nawiÈzaÊ

po

ïÈczenie z moduïem nasïuchujÈcym, wchodzÈcym w skïad serwera,

po czym modu

ï nasïuchujÈcy uruchamia osobny proces lub wÈtek

serwera bazy danych albo przekazuje (bezpo

Ărednio lub poĂrednio)

ĝÈdanie do istniejÈcego, oczekujÈcego procesu serwera.

Niezale

ĝnie od liczby operacji (wywoïywania nowych procesów lub

w

Ètków i wywoïywania obsïugi zapytania) system bazy danych musi

utworzy

Ê nowe Ărodowisko dla kaĝdej sesji, dziÚki czemu bÚdzie ona

mog

ïa ĂledziÊ realizowane w niej zadania. System obsïugi bazy danych

musi sprawdzi

Ê poprawnoĂÊ hasïa dla konta, za pomocÈ którego zostaïo

nawi

Èzane poïÈczenie i utworzona nowa sesja. System obsïugi bazy

danych cz

Ústo musi równieĝ wykonaÊ kod zwiÈzany z procedurÈ

zalogowania do bazy danych (zaimplementowany w postaci wyzwalacza).
To samo dotyczy równie

ĝ kodu inicjalizacyjnego niezbÚdnego do

dzia

ïania procedur osadzonych i pakietów. Na tym tle standardowy

5 6

R O Z D Z I A

’ DRUGI

protokó

ï nawiÈzania poïÈczenia miÚdzy klientem a serwerem ma

niewielki wp

ïyw na powstaïe opóěnienia. Z powodu tej „zasoboĝernoĂci”

procesu nawi

Èzania poïÈczenia tak wielkie znaczenie z punktu widzenia

wydajno

Ăci majÈ rozmaite techniki pozwalajÈce na utrzymanie raz

nawi

Èzanego poïÈczenia z bazÈ danych, jak pule poïÈczeñ (ang.

connection pooling).

Druga przyczyna przyspieszenia wi

Èĝe siÚ ze zmniejszeniem liczby cykli

przesy

ïania danych miÚdzy aplikacjÈ a bazÈ danych (tzw. round-trips),

co, jak wida

Ê, równieĝ ma niebanalny udziaï w czasochïonnoĂci operacji.

Nawet w przypadku, gdy zosta

ïo nawiÈzane tylko jedno poïÈczenie

i jest ono wykorzystywane do realizacji wszystkich kolejnych operacji,
prze

ïÈczanie kontekstu miÚdzy programem a jÈdrem systemu obsïugi

bazy danych równie

ĝ ma wpïyw na dalsze opóěnienia. JeĂli zatem

system obs

ïugi bazy danych umoĝliwia wykorzystanie jakiegoĂ

mechanizmu

ïadowania danych wiÚkszymi porcjami (jak na przykïad

tablice), warto wzi

ÈÊ pod uwagÚ jego uĝycie. W rozwiÈzaniach, które

wykorzystuj

È rzeczywiste tablice, warto sprawdziÊ, jaki jest domyĂlny

rozmiar tablicy, i dostosowa

Ê go do potrzeb aplikacji. Kaĝdy rodzaj

operacji wykorzystuj

Ècych przetwarzanie pojedynczymi wierszami

wi

Èĝe siÚ oczywiĂcie z analogicznymi konsekwencjami, na co bÚdziemy

mieli sporo dowodów w tym rozdziale.

Po

ïÈczenia z bazami danych i przeïÈczenia kontekstu sÈ jak Chiñskie

Mury — im ich wi

Úcej, tym dïuĝej trwa przekazanie wiadomoĂci.

Strategia przed taktyk

È

To strategia definiuje taktyk

Ú, nie odwrotnie. Dobry programista nie

postrzega procesu w kategoriach drobnych kroczków, lecz z perspektywy
ostatecznego wyniku. Najefektywniejszy sposób uzyskania wyniku nie musi
wynika

Ê z procesów biznesowych, czÚsto sprawdza siÚ mniej bezpoĂrednie

podej

Ăcie. NastÚpny przykïad pokaĝe, w jaki sposób nadmierne skupienie

si

Ú na procesach proceduralnych moĝe odwróciÊ uwagÚ od najbardziej

efektywnych rozwi

Èzañ.

P R O W A D Z E N I E W O J N Y

5 7

Kilka lat temu otrzyma

ïem zapytanie z proĂbÈ, abym spróbowaï je

zoptymalizowa

Ê. „Spróbowaï” to byïo sïowo kluczowe tego kontraktu.

Wcze

Ăniej odbyïy siÚ dwa podejĂcia do tej optymalizacji, pierwsze

podejmowane przez autorów, drugie przez eksperta Oracle. Mimo tych
prób ka

ĝde wywoïanie tego zapytania trwaïo okoïo dwudziestu minut,

co zdaniem autorów by

ïo nie do przyjÚcia.

Celem tej procedury by

ïo wyliczanie iloĂci materiaïów zamawianych przez

centraln

È jednostkÚ fabryki. Obliczenia wykorzystywaïy istniejÈce zapasy

i zamówienia pochodz

Èce z róĝnych ěródeï. Dane byïy odczytywane z kilku

identycznych tabel, a nast

Úpnie agregowane w jednej tabeli zbiorczej.

Procedura sk

ïadaïa siÚ z sekwencji wyraĝeñ o nastÚpujÈcej filozofii dziaïania:

najpierw dane ze wszystkich tabel by

ïy kopiowane do tabeli zbiorczej,

nast

Úpnie wywoïywane byïo zapytanie agregujÈce dane dotyczÈce

materia

ïów, nastÚpnie z tabeli byïy usuwane nadmiarowe dane niemajÈce

znaczenia dla procedury. Ta sekwencja by

ïa powtarzana dla kaĝdej tabeli

ěródïowej. ¿adne z zapytañ SQL nie byïo szczególnie skomplikowane,
ĝadnego z nich z osobna nie moĝna teĝ byïo okreĂliÊ jako szczególnie
nieefektywnego.

Wi

ÚkszÈ czÚĂÊ dnia zajÚïo mi zrozumienie procesu, co w koñcu zaowocowaïo

postawieniem pytania: „Dlaczego procedur

Ú wykonywano w kilku etapach?”.

Wystarczy

ïoby podzapytanie z operatorem unii (

UNION

), za pomoc

È którego

mo

ĝna poïÈczyÊ wszystkie wyniki z tabel ěródïowych. NastÚpnie

pojedyncza instrukcja

SELECT

pozwoli

ïaby za jednym zamachem zapeïniÊ

tabel

Ú wynikowÈ. Róĝnica w wydajnoĂci byïa imponujÈca: z dwudziestu

minut czas wykonania zadania skróci

ï siÚ do dwudziestu sekund.

Skuteczno

ĂÊ modyfikacji byïa tak zdumiewajÈca, ĝe sporÈ czÚĂÊ czasu

zaj

Úïa mi weryfikacja, czy aby nowa procedura generuje dokïadnie takie

same wyniki jak poprzednia.

Nie by

ïy tu potrzebne nadzwyczajne umiejÚtnoĂci, wystarczyïa umiejÚtnoĂÊ

my

Ălenia poza schematami. Poprzednie próby optymalizacji procesu

zako

ñczyïy siÚ niepowodzeniem, poniewaĝ byïy za bardzo zbliĝone do

sedna problemu. Cz

Ústo warto zdobyÊ siÚ na Ăwieĝe spojrzenie, zrobiÊ

krok wstecz, aby poszerzy

Ê perspektywÚ. Warto byïo zadaÊ dwa pytania:

„Co mamy dost

Úpnego przed wykonaniem procedury?” oraz „Jakie wyniki

chcemy uzyska

Ê z procedury?”. W poïÈczeniu ze Ăwieĝym spojrzeniem

odpowiedzi na te pytania doprowadzi

ïy do tak wielkiego udoskonalenia procesu.

5 8

R O Z D Z I A

’ DRUGI

Spójrz na problem z szerszej perspektywy, zanim zag

ïÚbisz siÚ

w drobniejsze szczegó

ïy rozwiÈzania.

Najpierw definicja problemu,
potem jego rozwi

Èzanie

Brak wiedzy mo

ĝe byÊ niebezpieczny. CzÚsto zdarza siÚ, ĝe ludzie sïyszeli

lub czytali o nowych czy nietypowych technikach, w niektórych przypadkach
nawet do

ĂÊ ciekawych, które natychmiast chcieli wdroĝyÊ w ramach

rozwi

Èzywania swoich problemów. ProgramiĂci i architekci systemów

nader cz

Ústo zachwycajÈ siÚ tego typu „rozwiÈzaniami”, które w gruncie

rzeczy jedynie przyczyniaj

È siÚ do powstawania nowych problemów.

Na szczycie listy „gotowych rozwi

Èzañ” z reguïy moĝna znaleěÊ denormalizacjÚ.

Nie

Ăwiadomi praktycznych zagroĝeñ zwiÈzanych z modyfikacjÈ nadmiarowych

danych zwolennicy denormalizacji cz

Ústo sugerujÈ jÈ jako pierwsze podejĂcie

przy „optymalizacji” wydajno

Ăci. Niestety, zdarza siÚ to czÚsto na etapie

rozwoju aplikacji, gdy jeszcze nie jest za pó

ěno na zmiany projektu (lub

przynajmniej nauk

Ú konstruowania wydajnych zïÈczeñ tabel). Szczególnie

popularnym panaceum na problemy wydaje si

Ú specjalna forma denormalizacji,

jak

È jest zmaterializowana perspektywa. Zmaterializowane perspektywy

czasem okre

Ăla siÚ nazwÈ migawek (ang. snapshot). Jest to mniej

spektakularna nazwa, ale za to bardziej zbli

ĝona do nagiej prawdy o tym

zjawisku: chodzi bowiem ni mniej, ni wi

Úcej o kopie danych wykonania

w okre

Ălonym punkcie czasu. Nie chcÚ tu sugerowaÊ, ĝe nigdy nie zdarza siÚ,

ĝe od czasu do czasu, przyparty do muru, nie jestem zmuszony do zastosowania
kontrowersyjnych technik. Jak stwierdzi

ï Franz Kafka: „Logiki nie da siÚ

podwa

ĝyÊ, ale nie ma ona szans w obliczu czïowieka, który po prostu

próbuje przetrwa

Ê”.

Jednak przyt

ïaczajÈcÈ wiÚkszoĂÊ problemów daje siÚ rozwiÈzaÊ dziÚki

inteligentnemu zastosowaniu do

ĂÊ tradycyjnych technik. Warto nauczyÊ

si

Ú wyciÈgaÊ wszystko, co dobre, z takich prostych rozwiÈzañ. Gdy juĝ siÚ

je opanuje, cz

ïowiek uczy siÚ doceniaÊ ich ograniczenia. Dopiero potem

mo

ĝna próbowaÊ osÈdziÊ potencjalne zalety (o ile istniejÈ) nowych rozwiÈzañ

technicznych.

P R O W A D Z E N I E W O J N Y

5 9

Wszystkie technologiczne rozwi

Èzania sÈ zaledwie Ărodkiem do osiÈgniÚcia

celu. Wielkim zagro

ĝeniem dla niedoĂwiadczonego programisty jest pociÈg

do najnowszej technologii, który szybko zamienia si

Ú w cel dla samego

siebie. A zagro

ĝenie jest tym wiÚksze w przypadku osób ciekawych,

obdarzonych du

ĝym entuzjazmem, o technicznym zaciÚciu.

Fundamenty s

È waĝniejsze od mody: naucz siÚ podstaw fachu, zanim

zaczniesz bawi

Ê siÚ nowinkami techniki.

Stabilny schemat bazy danych

Wykorzystanie j

Úzyka DDL (ang. data definition language) do tworzenia,

modyfikowania czy usuwania obiektów bazy danych jest bardzo nagann

È

praktyk

È, która powinna zostaÊ oficjalnie zakazana. Nie ma powodu, aby

dynamicznie tworzy

Ê, modyfikowaÊ i usuwaÊ obiekty bazy. Z wyjÈtkiem

partycji, o czym wspomn

Ú w rozdziale 5., oraz tabel tymczasowych,

z zaznaczeniem,

ĝe majÈ byÊ zadeklarowane jako tymczasowe w systemie

zarz

Èdzania bazami danych (istnieje jeszcze kilka waĝnych wyjÈtków

od tej regu

ïy, o czym wspomnÚ w rozdziale 10.).

Zastosowania j

Úzyka DDL wykorzystujÈ podstawowy sïownik danych bazy.

Ten s

ïownik jest równieĝ centralnym obiektem wszystkich operacji w bazie

danych, a wykorzystanie go prowadzi do wywo

ïywania globalnych blokad,

które powoduj

È znaczne konsekwencje w przypadku wydajnoĂci bazy.

Jedyn

È dopuszczalnÈ operacjÈ DDL jest przycinanie tabeli (

TRUNCATE

),

które jest bardzo szybk

È metodÈ usuwania danych z tabeli (ale naleĝy

pami

ÚtaÊ, ĝe w przypadku tej operacji nie mamy moĝliwoĂci jej wycofania

za pomoc

È instrukcji

ROLLBACK

!).

Tworzenie, modyfikacja i usuwanie obiektów baz danych to zadania
etapu projektowego, a nie codzienne operacje wykonywane przez
aplikacj

Ú klienckÈ.

6 0

R O Z D Z I A

’ DRUGI

Operacje na rzeczywistych danych

Wielu programistów ch

Útnie posïuguje siÚ tymczasowymi tablicami

roboczymi, do których na przyk

ïad ïadujÈ dane do dalszego przetwarzania.

Tego typu podej

Ăcie z reguïy uznaje siÚ za niewïaĂciwe, poniewaĝ moĝe

ono prowadzi

Ê do zamkniÚcia percepcji w zakresie procesów biznesowych

i uniemo

ĝliwiÊ szersze spojrzenie. Naleĝy pamiÚtaÊ, ĝe tabele tymczasowe

nie daj

È moĝliwoĂci zastosowania pewnych zaawansowanych technik

dost

Úpnych w przypadku rzeczywistych tabel (czÚĂÊ z tego typu opcji

omówi

Ú w rozdziale 5.). Indeksowanie tabel tymczasowych (o ile w ogóle

jest dost

Úpne) moĝe na przykïad byÊ mniej optymalne. W wyniku tych

ogranicze

ñ zapytania wykorzystujÈce tabele tymczasowe mogÈ dziaïaÊ

mniej wydajnie od prawid

ïowo napisanych zapytañ na rzeczywistych

tabelach. Wad

È zastosowania tabel tymczasowych jest ponadto koniecznoĂÊ

wykonania dodatkowego zapytania wype

ïniajÈcego tabelÚ tymczasowÈ

danymi.

Nawet w przypadku, gdy zastosowanie tabel tymczasowych jest uzasadnione,
nie nale

ĝy nigdy wykorzystywaÊ w tym charakterze rzeczywistych tabel

udaj

Ècych tabele tymczasowe, szczególnie gdy liczba zapisywanych w nich

wierszy jest du

ĝa. Jeden z problemów leĝy tu w mechanizmie gromadzenia

statystyk: je

Ăli statystyki dotyczÈce tabel nie sÈ gromadzone w czasie

rzeczywistym, to system zarz

Èdzania bazÈ danych wykorzystuje do tego

chwile mniejszego obci

Èĝenia. Natura tabel roboczych polega na tym, ĝe

z regu

ïy w takich przestojach bywajÈ puste, co powoduje, ĝe optymalizator

uzyskuje zupe

ïnie bïÚdne informacje. W efekcie system podejmuje bïÚdne

decyzje w wyniku nieodpowiednich planów wykonawczych przygotowywanych
przez optymalizator zapyta

ñ, co prowadzi bezpoĂrednio do obniĝonej

wydajno

Ăci. JeĂli ktoĂ jest naprawdÚ zmuszony do uĝycia tabel

tymczasowych, powinien u

ĝywaÊ do tego celu tabel, które system

zarz

Èdzania bazami danych jest w stanie zidentyfikowaÊ jako tymczasowe.

Wykorzystanie tabel tymczasowych oznacza przepychanie danych do
mniej optymalnego zasobu.

P R O W A D Z E N I E W O J N Y

6 1

Przetwarzanie zbiorów w SQL-u

SQL przetwarza dane w postaci zbiorów. W przypadku operacji
modyfikacji (

UPDATE

) lub usuwania danych (pod warunkiem,

ĝe te

modyfikacje nie s

È dokonywane na caïych tabelach) uĝytkownik musi

zdefiniowa

Ê zbiór wierszy, których dana modyfikacja dotyczy. W ten

sposób definiuje si

Ú pewnÈ granularnoĂÊ procesu, który moĝna okreĂliÊ

jako zgrubn

È, jeĂli zmiany dotyczÈ wiÚkszej liczby wierszy, lub drobnÈ,

gdy przetwarzamy jedynie kilka wierszy.

Ka

ĝda próba modyfikacji duĝej liczby wierszy tabeli, ale maïymi porcjami

jest z regu

ïy zïym pomysïem i zwykle okazuje siÚ bardzo niewydajna.

Takie podej

Ăcie jest dopuszczalne jedynie w przypadku, gdy na bazie danych

b

ÚdÈ dokonywane bardzo rozlegïe zmiany, które na czas transakcji mogÈ

spowodowa

Ê tymczasowe zajÚcie bardzo duĝej iloĂci zasobów oraz zajmujÈ

bardzo du

ĝo czasu w przypadku wycofania transakcji (

ROLLBACK

). Istniej

È

równie

ĝ opinie, ĝe w przypadku modyfikacji duĝych porcji danych naleĝy

w ramach kodu DML (ang. data manipulation code) umieszcza

Ê co jakiĂ

czas instrukcje zatwierdzaj

Èce zmiany (

COMMIT

). Tego typu podej

Ăcie moĝe

jednak nie sprawdzi

Ê siÚ w przypadku, gdy dane sÈ ïadowane z pliku

i procedura zostanie przerwana w trakcie. Z czysto praktycznego punktu
widzenia cz

Ústo o wiele proĂciej i szybciej jest wznowiÊ proces od poczÈtku,

ni

ĝ próbowaÊ zlokalizowaÊ miejsce w danych wejĂciowych, do którego

zosta

ïy juĝ zaïadowane, i od niego wznawiaÊ proces.

Natomiast bior

Èc pod uwagÚ rozmiar loga transakcji (ang. transaction log)

wykorzystywanego do wycofania zmian transakcji, równie

ĝ istniejÈ opinie,

ĝe fizyczna implementacja bazy danych powinna byÊ przygotowana
do przyjmowania zmian wykonywanych przez aplikacj

Ú, a aplikacja nie

powinna by

Ê zmuszona do omijania ograniczeñ fizycznej implementacji.

Je

Ăli wymagana iloĂÊ zasobów niezbÚdnych do zapisania loga transakcji jest

rzeczywi

Ăcie bardzo duĝa, zapewne naleĝy zastanowiÊ siÚ, czy czÚstotliwoĂÊ

dokonywania tego typu zmian jest odpowiednia do konstrukcji bazy.
Mo

ĝe siÚ bowiem okazaÊ, ĝe zmiana strategii z miesiÚcznych, gigantycznych

aktualizacji danych na kilkakrotnie mniejsze, tygodniowe, albo zupe

ïnie

niewielkie, dzienne modyfikacje spowoduje,

ĝe problem zupeïnie

przestanie istnie

Ê.

6 2

R O Z D Z I A

’ DRUGI

Pracowite zapytania SQL

SQL nie jest j

Úzykiem proceduralnym. ChoÊ w zapytaniach SQL istnieje

mo

ĝliwoĂÊ zastosowania logiki jÚzyków proceduralnych, naleĝy traktowaÊ

je z rozwag

È. Problemy z rozróĝnieniem logiki proceduralnej od

przetwarzania deklaratywnego najlepiej wida

Ê w przypadkach, gdy ktoĂ

próbuje wydoby

Ê dane z bazy, dokonaÊ na nich modyfikacji, po czym

ponownie zapisa

Ê w bazie. Gdy program lub procedura dziaïajÈca

w ramach programu otrzyma dane wej

Ăciowe, czÚsto zdarza siÚ, ĝe sÈ one

wykorzystywane do odczytu innych danych z bazy, po czym nast

Úpuje

p

Útla lub inna forma logiki funkcyjnej (z reguïy pÚtla if...then...else)

zastosowana do wydobywania kolejnych danych z bazy. W wi

ÚkszoĂci

przypadków jest to efekt g

ïÚboko zakorzenionych nawyków lub sïabej

znajomo

Ăci SQL-a w poïÈczeniu z niewolniczym oddaniem w stosunku do

specyfikacji funkcjonalnej. Wiele stosunkowo skomplikowanych operacji
mo

ĝna wykonaÊ za pomocÈ pojedynczego zapytania w SQL-u. JeĂli

u

ĝytkownik poda wartoĂÊ, czÚsto moĝna podaÊ interesujÈcy go wynik bez

konieczno

Ăci rozbijania logiki na poszczególne instrukcje o niewielkim

zwi

Èzku z wynikiem koñcowym.

Istniej

È dwa gïówne powody, aby unikaÊ stosowania logiki proceduralnej

w SQL-u:

Ka

ĝdy dostÚp do bazy danych wiÈĝe siÚ z operacjami na wielu róĝnych

warstwach programowych, w

ïÈczajÈc w to operacje sieciowe.

Nawet w przypadku, gdy sie

Ê nie jest wykorzystywana, wchodzÈ w grÚ

operacje wymiany danych mi

Údzy procesami. WiÚcej operacji dostÚpu

oznacza wi

Úcej wywoïañ funkcji, wiÚcej przepustowoĂci, a co siÚ z tym

wi

Èĝe, dïuĝsze oczekiwanie na odpowiedě. Gdy tego typu wywoïania sÈ

cz

Ústo powtarzane, opóěnienia stajÈ siÚ wyraěnie zauwaĝalne.

„Proceduralny” znaczy,

ĝe wydajnoĂÊ i utrzymanie zaleĝÈ od programu,

nie od bazy danych.

Wi

ÚkszoĂÊ systemów baz danych do wykonywania operacji, jak na przykïad

z

ïÈczenia, wykorzystuje zaawansowane algorytmy przeksztaïcajÈce

zapytania w ich inne formy tak, aby wykonywa

ïy siÚ w wydajniejszy

sposób. Optymalizatory oparte na koszcie (ang. cost-based optimizers,
CBO) to skomplikowane modu

ïy, które pokonaïy dïugÈ drogÚ. Na

pocz

Ètku swojego istnienia mechanizmy tego typu byïy praktycznie

P R O W A D Z E N I E W O J N Y

6 3

bezu

ĝyteczne, ale z czasem nabraïy dojrzaïoĂci i obecnie dajÈ doskonaïe

wyniki. Dobry mechanizm CBO bywa bardzo skuteczny w znajdowaniu
najbardziej odpowiedniego planu wykonania. Jednak

ĝe CBO analizuje

ka

ĝde zapytanie SQL, nic ponad to. WrzucajÈc jak najwiÚkszÈ liczbÚ

operacji w pojedyncze wyra

ĝenie, przerzucamy na bazÚ danych

odpowiedzialno

ĂÊ za znalezienie najbardziej optymalnego wykonania.

Dzi

Úki temu program moĝe wykorzystaÊ równieĝ przyszïe usprawnienia

w dzia

ïaniu silnika systemu zarzÈdzania bazÈ danych. W ten sposób

równie

ĝ przyszïe usprawnienia aplikacji sÈ czÚĂciowo przerzucane

na dostawc

Ú bazy danych.

Jak to zwykle bywa, równie

ĝ od zasady unikania logiki proceduralnej

istniej

È dobrze uzasadnione wyjÈtki. Dotyczy to sytuacji, gdy tego typu

podej

Ăcie pozwala na znaczÈce przyspieszenie w uzyskiwaniu wyników.

Rozbudowane, monstrualne zapytania SQL nie zawsze stanowi

È wzorzec

wydajno

Ăci. Jednak naleĝy pamiÚtaÊ, ĝe proceduralny kod sklejajÈcy

kolejno wykonywane zapytania SQL operuj

Èce na tych samych danych

(tabelach i wierszach) to dobry materia

ï na pojedyncze zapytanie SQL.

Mechanizm CBO analizuje pojedyncze zapytanie skonstruowane w zgodzie
z regu

ïami modelu relacyjnego jako jednÈ caïoĂÊ i jest w stanie opracowaÊ

efektywny sposób jego wykonania.

Jak najwi

Úcej pracy zrzucaj na optymalizator zapytañ, aby skorzystaÊ

z jego mo

ĝliwoĂci.

Maksymalne wykorzystanie dost

Úpu

do bazy danych

Gdy planujemy wizyt

Ú w wielu sklepach, w pierwszym kroku musimy

zdecydowa

Ê siÚ na to, co chcemy kupiÊ w kaĝdym z nich. DziÚki temu

mo

ĝna zaplanowaÊ trasÚ podróĝy i zminimalizowaÊ koniecznoĂÊ

przechodzenia mi

Údzy sklepami tam i z powrotem. Odwiedzamy pierwszy

sklep, dokonujemy zakupów, po czym odwiedzamy kolejny sklep.
To zdrowy rozs

Èdek, a jednak zasada obowiÈzujÈca w tym przykïadzie

wydaje si

Ú obca wielu praktycznym zastosowaniom baz danych.

6 4

R O Z D Z I A

’ DRUGI

Gdy z pojedynczej tabeli chcemy odczyta

Ê kilka róĝnych informacji, nawet

niezbyt powi

Èzanych (co rzeczywiĂcie wydaje siÚ doĂÊ powszechnym

przypadkiem), nieoptymalne jest nawi

Èzywanie wielu poïÈczeñ, po jednym

dla odczytania ka

ĝdej porcji danych. Na przykïad nie naleĝy odczytywaÊ

pojedynczych kolumn, je

Ăli potrzebujemy wartoĂci z kilku z nich. Naleĝy

do tego wykorzysta

Ê pojedynczÈ operacjÚ. Niestety, dobre praktyki

programowania zorientowanego obiektowo z zasady pojedynczego odczytu
warto

Ăci atrybutów zrobiïy zaletÚ, nie wadÚ. Nie wolno jednak myliÊ metod

obiektowych z przetwarzaniem danych w bazach. Mieszanie tych poj

ÚÊ

nale

ĝy do najpowaĝniejszych bïÚdów, tabel nie wolno Ălepo traktowaÊ

jako klas, a kolumn jako atrybutów.

Ka

ĝdÈ wizytÚ w bazie danych wykorzystuj do wykonania jak

najwi

Úkszej iloĂci pracy.

Zbli

ĝenie do jÈdra systemu DBMS

Im bli

ĝej jÈdra systemu zarzÈdzania bazÈ danych moĝe dziaïaÊ kod, tym

b

Údzie dziaïaï wydajniej. To wïaĂnie sedno siïy baz danych. Na przykïad

niektóre systemy zarz

Èdzania bazami danych pozwalajÈ na rozszerzanie

swoich mo

ĝliwoĂci za pomocÈ nowych funkcji, które moĝna pisaÊ

w niskopoziomowych j

Úzykach programowania, jak na przykïad C. JÚzyki

niskiego poziomu operuj

Èce na wskaěnikach majÈ istotnÈ cechÚ negatywnÈ:

je

Ăli w procedurze zostanie popeïniony bïÈd, moĝna doprowadziÊ

do uszkodzenia danych w pami

Úci. Problem byïby powaĝny juĝ w przypadku,

gdyby z programu korzysta

ï tylko jeden uĝytkownik. Kïopot z bazami

danych polega jednak na tym,

ĝe mogÈ obsïugiwaÊ duĝÈ liczbÚ uĝytkowników.

W przypadku uszkodzenia danych w pami

Úci, moĝna uszkodziÊ dane

innego, zupe

ïnie „niewinnego” programu. W praktyce bywa tak, ĝe solidne

systemy zarz

Èdzania bazami danych wykonujÈ kod w pewnej izolacji

(technika okre

Ălana mianem piaskownicy, ang. sandbox), dziÚki czemu

w przypadku awarii procesu nie poci

Èga on za sobÈ caïej bazy danych.

Przyk

ïadem jest tu system Oracle, który do wymiany danych miÚdzy

procesami a baz

È danych wykorzystuje specjalny mechanizm komunikacji.

Ten proces jest podobny do

ïÈczy miÚdzy bazami danych pracujÈcych na

ró

ĝnych serwerach. JeĂli zysk uzyskany z wydajnoĂci zewnÚtrznych funkcji

P R O W A D Z E N I E W O J N Y

6 5

w C w stosunku do osadzonych procedur PL/SQL rekompensuje koszt
skonstruowania zewn

Útrznego Ărodowiska i przeïÈczeñ kontekstu, warto

wykorzysta

Ê funkcje zewnÚtrzne. Jednak nie warto ich stosowaÊ w przypadku,

gdy maj

È byÊ wykorzystywane do wywoïywania pojedynczo dla kaĝdego

wiersza tabeli. Jak to zwykle bywa, decyzja jest kwesti

È równowagi

i znajomo

Ăci wszystkich konsekwencji stosowania róĝnych strategii

rozwi

Èzywania tego samego problemu.

Je

Ăli funkcje majÈ byÊ jednak wykorzystane, warto w pierwszej kolejnoĂci

rozwa

ĝyÊ wykorzystanie funkcji standardowych, dostÚpnych w mechanizmie

zarz

Èdzania bazÈ danych. Nie chodzi tu wyïÈcznie o kwestiÚ unikania

„ponownego wynajdowania ko

ïa”, lecz przede wszystkim o to, ĝe funkcje

wbudowane dzia

ïajÈ znacznie bliĝej jÈdra systemu zarzÈdzania bazÈ danych

ni

ĝ zewnÚtrzny kod, a w zwiÈzku z tym sÈ bardziej wydajne.

Oto prosty przyk

ïad wykorzystania kodu SQL w bazie Oracle, za pomocÈ

którego zademonstruj

Ú wydajnoĂÊ uzyskanÈ dziÚki zastosowaniu funkcji

wbudowanych. Za

ïóĝmy, ĝe mamy dane tekstowe wprowadzone rÚcznie

przez u

ĝytkownika i ĝe te dane zawierajÈ zbÚdne ciÈgi znaku spacji.

Potrzebna jest nam funkcja, która zast

Èpi takie ciÈgi wielu znaków spacji

pojedynczym znakiem. Przyjmijmy,

ĝe nie bÚdziemy korzystaÊ z obsïugi

wyra

ĝeñ regularnych, dostÚpnej w Oracle Database 10g, a zamiast tego

napiszemy w

ïasnÈ funkcjÚ:

create or replace function squeeze1(p_string in varchar2)
return varchar2
is
v_string varchar2(512) := '';
c_char char(1);
n_len number := length(p_string);
i binary_integer := 1;
j binary_integer;
begin
while (i <= n_len)
loop
c_char := substr(p_string, i, 1);
v_string := v_string || c_char;
if (c_char = ' ')
then
j := i + 1;
while (substr(p_string || 'X', j, 1) = ' ')
loop
j := j + 1;

6 6

R O Z D Z I A

’ DRUGI

end loop;
i := j;
else
i := i + 1;
end if;
end loop;
return v_string;
end;
/

Uwaga na marginesie: na ko

ñcu ciÈgu znaków dopisywany jest znak

X

, aby

unikn

ÈÊ porównania wartoĂci

j

z d

ïugoĂciÈ tego ciÈgu.

Istniej

È róĝne metody eliminacji ciÈgów spacji, w których moĝna wykorzystaÊ

funkcje udost

Úpniane w ramach bazy Oracle. Oto jedna z alternatyw:

create or replace function squeeze2(p_string in varchar2)
return varchar2
is
v_string varchar2(512) := p_string;
i binary_integer := l;
begin
i := instr(v_string, ' ');
while (i > 0)
loop
v_string := substr(v_string, 1, i)
|| ltrim(substr(v_string, i + l));
i := instr(v_string, ' ');
end loop;
return v_string;
end;
/

Trzecia z metod mo

ĝe byÊ nastÚpujÈca:

create or replace function squeeze3(p_string in varchar2)
return varchar2
is
v_string varchar2(512) := p_string;
len1 number;
len2 number;
begin
len1 := length(p_string);
v_string := replace(p_string, ' ', ' ');
len2 := length(v_string);
while (len2 < len1)
loop
len1 := len2;

P R O W A D Z E N I E W O J N Y

6 7

v_string := replace(v_string, ' ',' ');
len2 := length(v_string);
end loop;
return v_string;
end;
/

Gdy te trzy alternatywne metody zostan

È przetestowane na prostym

przyk

ïadzie, kaĝda, zgodnie z oczekiwaniem, da dokïadnie takie same

wyniki i nie b

Údzie znaczÈcej róĝnicy w wydajnoĂci:

SQL> select squeeze1('azeryt hgfrdt r')
2 from dual
3 /
azeryt hgfrdt r

Elapsed: 00:00:00.00
SQL> select squeeze2('azeryt hgfrdt r')
2 from dual
3 /
azeryt hgfrdt r

Elapsed: 00:00:00.01
SQL> select squeeze3('azeryt hgfrdt r')
2 from dual
3 /
azeryt hgfrdt r

Elapsed: 00:00:00.00

Za

ïóĝmy jednak, ĝe operacja oczyszczania ciÈgów znaków z wielokrotnych

spacji b

Údzie wywoïywana tysiÈce razy dziennie. Poniĝszy kod moĝna

zastosowa

Ê do zaïadowania bazy danych danymi testowymi, za pomocÈ

których mo

ĝna w nieco bardziej realistycznych warunkach przetestowaÊ

wydajno

ĂÊ trzech przedstawionych wyĝej funkcji oczyszczajÈcych ciÈgi

znaków z wielokrotnych spacji:

create table squeezable(random_text varchar2(50))
/

declare
i binary_integer;
j binary_integer;
k binary_integer;
v_string varchar2(5O);

6 8

R O Z D Z I A

’ DRUGI

begin
for i in 1 .. 10000
loop
j := dbms_random.value(1, 100);
v_string := dbms_random.string('U', 50);
while (j < length(v_string))
loop
k := dbms_random.value(l, 3);
v_string := substr(substr(v_string, 1, j) || rpad(' ', k)
|| substr(v_string, j + 1), 1, 50);
j := dbms_random.value(i, 100);
end loop;
insert into squeezable values(v_string);
end loop;
commit;
end;
/

Ten skrypt tworzy tabel

Ú testowÈ zïoĝonÈ z dziesiÚciu tysiÚcy wierszy

(to do

ĂÊ niewiele, biorÈc pod uwagÚ ĂredniÈ liczbÚ wywoïañ zapytañ SQL).

Test wywo

ïuje siÚ nastÚpujÈco:

select squeeze_func(random_text)
from squeezable;

Gdy wywo

ïywaïem ten test, wyïÈczyïem wyĂwietlanie wyników na

ekranie. Dzi

Úki temu upewniïem siÚ, ĝe czasy dziaïania algorytmów

oczyszczaj

Ècych wielokrotne spacje nie sÈ zafaïszowane przez czas

niezb

Údny na wyĂwietlenie wyników. Testy byïy wywoïane wielokrotnie,

aby upewni

Ê siÚ, ĝe nie wystÈpiï efekt przyspieszenia wykonania dziÚki

zbuforowaniu danych.

Czasy dzia

ïania tych algorytmów prezentuje tabela 2.2.

TABELA 2.2. Czas wykonania funkcji oczyszczaj

 cych ci gi spacji na danych testowych (10 000 wierszy)

Funkcja

Mechanizm

Czas wykonania

squeeze1

PL/SQL p

¤tla po elementach ci gu znaków

0,86 sekund

squeeze2

instr() + ltrim()

0,48 sekund

squeeze3

replace()

wywo

¨ana w p¤tli

0,39 sekund

Cho

Ê wszystkie z tych funkcji wykonujÈ siÚ dziesiÚÊ tysiÚcy razy w czasie

poni

ĝej jednej sekundy,

squeeze2()

jest 1,8 razy szybsza od

squeeze1()

,

a

squeeze3()

jest ponad 2,2 razy szybsza. Jak to si

Ú dzieje? Po prostu

P R O W A D Z E N I E W O J N Y

6 9

PL/SQL nie jest tak „blisko j

Èdra”, jak funkcja SQL-a. Róĝnica wydajnoĂci

mo

ĝe wyglÈdaÊ na niewielkÈ w przypadku sporadycznego wywoïywania

funkcji, lecz w programie wsadowym lub na obci

Èĝonym serwerze OLTP

ró

ĝnica moĝe juĝ byÊ powaĝna.

Kod uwielbia j

Èdro SQL-a — im jest bliĝej, tym jest gorÚtszy.

Robi

Ê tylko to, co niezbÚdne

Programi

Ăci czÚsto wykorzystujÈ instrukcjÚ

count(*)

do implementacji

testu istnienia. Z regu

ïy dochodzi do tego w celu implementacji

nast

ÚpujÈcej procedury:

JeĂli istniejÈ wiersze speïniajÈce warunek
Wykonaj na nich dziaïanie

Powy

ĝszy schemat jest implementowany za pomocÈ nastÚpujÈcego kodu:

select count(*)
into counter
from tabela
where <warunek>
if (counter > 0) then

Oczywi

Ăcie w 90% tego typu wywoïania instrukcji

count(*)

s

È zupeïnie

zb

Údne, dotyczy to równieĝ powyĝszego przykïadu. JeĂli jakieĂ dziaïanie

musi by

Ê wykonane na podzbiorze wierszy tabeli, dlaczego go po prostu nie

wykona

Ê od razu? JeĂli nie zostanie zmodyfikowany ani jeden wiersz, jaki

w tym problem? Nikomu nie stanie si

Ú ĝadna krzywda. Ponadto w sytuacji,

gdy operacja wykonywana w bazie danych sk

ïada siÚ z wielu zapytañ,

po wywo

ïaniu pierwszego z nich liczbÚ zmodyfikowanych wierszy moĝna

odczyta

Ê ze zmiennej systemowej (

@@ROWCOUNT

w Transact-SQL,

SOL%ROWCOUNT

w PL/SQL itp.), specjalnego pola SQL Communication Area (SQLCA)
w przypadku wykorzystania osadzonego SQL (embedded SQL) lub
za po

Ărednictwem specjalizowanych API, jak na przykïad funkcji

mysql_affected_rows()

j

Úzyka PHP. Liczba przetworzonych wierszy jest

czasem zwracana z funkcji, która wykonuje operacj

Ú w bazie danych, jak

metoda

executellpdate()

biblioteki JDBC. Zliczanie wierszy bardzo cz

Ústo

7 0

R O Z D Z I A

’ DRUGI

nie s

ïuĝy niczemu oprócz zwiÚkszenia iloĂci pracy, jakÈ musi wykonaÊ baza,

poniewa

ĝ wiÈĝe siÚ ono z dwukrotnym przetworzeniem (a raczej: odczytaniem,

a potem przetworzeniem) tych samych danych.

Ponadto nie nale

ĝy zapominaÊ, ĝe jeĂli naszym celem jest aktualizacja

lub wstawienie wierszy (cz

Ústy przypadek: wiersze sÈ zliczane po to, by

stwierdzi

Ê istnienie klucza), niektóre systemy zarzÈdzania bazami danych

oferuj

È specjalne instrukcje (jak MERGE w Oracle 9i Database), które

dzia

ïajÈ bardziej wydajnie niĝ w przypadku zastosowania osobnych zapytañ

zliczaj

Ècych.

Nie ma potrzeby, aby jawnie kodowa

Ê to, co baza danych wykonuje

w sposób niejawny.

Instrukcje SQL-a
odwzorowuj

È logikÚ biznesowÈ

Wi

ÚkszoĂÊ systemów baz danych udostÚpnia mechanizmy monitorujÈce,

za pomoc

È których moĝna sprawdzaÊ stan wykonywanych aktualnie

instrukcji, a nawet

ĂledziÊ liczbÚ ich wywoïañ. Przy okazji moĝna

u

ĂwiadomiÊ sobie liczbÚ przetwarzanych jednoczeĂnie „jednostek

biznesowych”: zamówie

ñ lub innych zgïoszeñ, klientów z wystawionymi

fakturami lub dowolnych innych zdarze

ñ istotnych z punktu widzenia

biznesowego. Mo

ĝna zweryfikowaÊ, czy istnieje sensowne (a nawet

absolutnie precyzyjne) prze

ïoĝenie miÚdzy dwoma klasami aktywnoĂci.

Innymi s

ïowy: czy dla zadanej liczby klientów liczba odwoïañ do bazy

danych za ka

ĝdym razem jest taka sama? JeĂli zapytanie do tabeli klientów

jest wywo

ïywane dwadzieĂcia razy czÚĂciej, niĝ wskazywaïaby na to liczba

przetwarzanych klientów, to z pewno

ĂciÈ wskazuje na jakiĂ bïÈd. Taka

sytuacja sugerowa

ïaby, ĝe zamiast jednorazowego odczytu danych z tabeli,

program dokonuje du

ĝej iloĂci zbÚdnych odczytów tych samych danych

z tabeli.

Nale

ĝy sprawdziÊ, czy dziaïania w bazie danych sÈ spójne z realizowanymi

funkcjami biznesowymi aplikacji.

P R O W A D Z E N I E W O J N Y

7 1

Programowanie logiki w zapytaniach

Istnieje kilka sposobów implementacji logiki biznesowej w aplikacji
wykorzystuj

Ècej bazÚ danych. CzÚĂÊ logiki proceduralnej moĝna

zaimplementowa

Ê w ramach instrukcji SQL-a (choÊ ze swej natury SQL

mówi o tym, co nale

ĝy zrobiÊ, a nie jak). Nawet w przypadku dobrej

integracji SQL-a w innych j

Úzykach programowania zaleca siÚ, aby jak

najwi

Úcej logiki biznesowej ujmowaÊ w SQL-u. Taka strategia pozwala

na uzyskanie wy

ĝszej wydajnoĂci przetwarzania danych niĝ w przypadku

implementacji logiki w aplikacji. J

Úzyki proceduralne to takie, w których

mo

ĝna definiowaÊ iteracje (pÚtle) oraz stosowaÊ logikÚ warunkowÈ

(konstrukcje if...then...else). SQL nie potrzebuje p

Útli, poniewaĝ ze swojej

natury operuje na zbiorach danych. Potrzebuje jedynie mo

ĝliwoĂci

okre

Ălania warunków wykonania okreĂlonych dziaïañ.

Logika warunkowa wymaga obs

ïugi dwóch elementów: IF i ELSE. Obsïuga

IF w SQL-u to prosta sprawa: warunek

WHERE

zapewnia dok

ïadnie takÈ

semantyk

Ú. Natomiast z obsïugÈ logiki ELSE jest pewien problem. Na

przyk

ïad mamy za zadanie pobraÊ z tabeli zbiór wierszy, po czym wykonaÊ

ró

ĝne typy operacji, w zaleĝnoĂci od typów zbiorów. Fragment tej logiki

mo

ĝna zasymulowaÊ z uĝyciem wyraĝenia

CASE

(Oracle od dawna obs

ïuguje

odpowiednik tej operacji w postaci funkcji

decode()

1

). Mi

Údzy innymi

mo

ĝna modyfikowaÊ w locie wartoĂci zwracane w ramach zbioru wynikowego

w zale

ĝnoĂci od speïnienia okreĂlonych warunków. W pseudokodzie moĝna

to zapisa

Ê nastÚpujÈco

2

:

CASE
WHEN warunek THEN <zwrócenie okreĂlonej wartoĂci>
WHEN warunek THEN <zwrócenie innej wartoĂci>
WHEN warunek THEN <zwrócenie jeszcze innej wartoĂci>
ELSE <wartoĂÊ domyĂlna>
END

Porównywanie warto

Ăci liczbowych i dat to operacje intuicyjne. W przypadku

ci

Ègów znaków mogÈ byÊ przydatne funkcje znakowe, jak

greatest()

czy

least()

znane z Oracle, czy

strcmp()

z MySQL-a. Czasem te

ĝ bywa

1

Funkcja

decode()

jest nieco bardziej „surowa” w stosunku do konstrukcji

CASE

.

Do uzyskania tych samych efektów mo

ĝe byÊ konieczne wykorzystanie dodatkowych

funkcji, na przyk

ïad

sign()

.

2

Istniej

È dwa warianty konstrukcji

CASE

, przedstawiona wersja jest bardziej zaawansowana.

7 2

R O Z D Z I A

’ DRUGI

mo

ĝliwe zastosowanie pewnej formy logiki w instrukcjach za pomocÈ

wielokrotnych i logicznych operacji wstawiania do tabel oraz za pomoc

È

wstawiania

ïÈczÈcego

3

(

merge insert

). Nie nale

ĝy unikaÊ takich instrukcji,

o ile s

È dostÚpne w posiadanym systemie zarzÈdzania bazami danych.

Innymi s

ïowy, polecenia SQL-a moĝna wyposaĝyÊ w duĝÈ iloĂÊ elementów

kontrolnych. W przypadku pojedynczej operacji korzy

ĂÊ z tych mechanizmów

by

Ê moĝe nie jest wielka, lecz z zastosowaniem instrukcji

CASE

i wielu

instrukcji wykonywanych warunkowo jest ju

ĝ o co walczyÊ.

O ile to mo

ĝliwe, warto implementowaÊ logikÚ aplikacji

w zapytaniach SQL zamiast w wykorzystuj

Ècej je aplikacji.

Jednoczesne wielokrotne modyfikacje

Moje g

ïówne zaïoĝenie w tym podrozdziale opiera siÚ na stwierdzeniu,

ĝe kolejne modyfikacje danych w pojedynczej tabeli sÈ dopuszczalne pod
warunkiem

ĝe dotyczÈ rozïÈcznych zbiorów wierszy. W przeciwnym razie

nale

ĝy ïÈczyÊ je w ramach pojedynczego zapytania. Oto przykïad

z rzeczywistej aplikacji

4

:

update tbo_invoice_extractor
set pga_status = 0
where pga_status in (1, 3)
and inv_type = 0;
update tbo_invoice_extractor
set rd_status = 0
where rd_status in (1, 3)
and inv_type = 0;

W tej samej tabeli dokonywane s

È dwie kolejne operacje modyfikujÈce.

Czy te same wiersze b

ÚdÈ wykorzystywane dwukrotnie? Nie ma

mo

ĝliwoĂci, aby to stwierdziÊ. Zasadniczym pytaniem jest tu jednak, jak

wydajne s

È kryteria wyszukiwania? Atrybuty o nazwach

type

(typ) lub

status

z du

ĝym prawdopodobieñstwem gwarantujÈ sïabÈ dystrybucjÚ

warto

Ăci. Jest zatem caïkiem moĝliwe, ĝe najefektywniejszym sposobem

odczytu tych danych b

Údzie peïne, sekwencyjne przeszukiwanie tabeli.

3

Dost

Úpny na przykïad w Oracle od wersji 9.2.

4

Nazwy tabel zosta

ïy zmienione.

P R O W A D Z E N I E W O J N Y

7 3

Mo

ĝe teĝ byÊ tak, ĝe jedno z zapytañ wykorzysta indeks, a drugie bÚdzie

wymaga

ïo peïnego przeszukiwania. W najkorzystniejszym przypadku

obydwa zapytania skorzystaj

È z wydajnego indeksu. Niezaleĝnie jednak

od tego, nie mamy prawie nic do stracenia, aby nie spróbowa

Ê poïÈczyÊ

obydwu zapyta

ñ w jedno:

update tbo_invoice_extractor
set pga_status = (case pga_status
when 1 then 0
when 3 then 0
else pga_status
end),
rd_status = (case rd_status
when 1 then 0
when 3 then 0
else rd_status
end)
where (pga_status in (1, 3)
or rd_status in (1, 3))
and inv_type = 0;

Istnieje prawdopodobie

ñstwo wystÈpienia niewielkiego narzutu

spowodowanego aktualizacj

È kolumn o wartoĂci juĝ przez nie posiadanej.

Jednak w wi

ÚkszoĂci przypadków jedna zïoĝona aktualizacja danych jest

o wiele szybsza ni

ĝ skïadowe wywoïane osobno. Warto zauwaĝyÊ

zastosowanie logiki warunkowej z u

ĝyciem instrukcji

CASE

. Dzi

Úki temu

przetworzone zostan

È tylko te wiersze, które speïniajÈ kryteria, niezaleĝnie

od tego, jak wiele kryteriów b

Údzie zastosowanych w zapytaniu.

Operacje modyfikuj

Èce warto wykonywaÊ w pojedynczej, zïoĝonej

operacji, aby zminimalizowa

Ê wielokrotne odczyty tej samej tabeli.

Ostro

ĝne wykorzystanie

funkcji u

ĝytkownika

Gdy w zapytaniu jest wykorzystana funkcja u

ĝytkownika, istnieje

mo

ĝliwoĂÊ, ĝe bÚdzie wywoïywana wielokrotnie. JeĂli funkcja wystÚpuje

w li

Ăcie

SELECT

, b

Údzie wywoïywana dla kaĝdego zwróconego wiersza. JeĂli

wyst

Èpi w instrukcji

WHERE

, b

Údzie wywoïywana dla kaĝdego sprawdzonego

7 4

R O Z D Z I A

’ DRUGI

wiersza, który spe

ïnia kryteria sprawdzone wczeĂniej. To moĝe oznaczaÊ

bardzo wiele wywo

ïañ w przypadku, gdy wczeĂniej sprawdzane kryteria

nie s

È bardzo mocno selektywne.

Warto si

Ú zastanowiÊ, co siÚ stanie, gdy taka funkcja wywoïuje inne

zapytanie. To zapytanie b

Údzie wywoïywane przy kaĝdym wywoïaniu

funkcji. W praktyce jej wynik b

Údzie taki sam jak w przypadku wywoïania

podzapytania, z t

È róĝnicÈ, ĝe w przypadku zapytania ukrytego w funkcji

optymalizator nie ma mo

ĝliwoĂci lepszego zoptymalizowania zapytania

g

ïównego. Co wiÚcej, procedura osadzona jest wykonywana na osobnej

warstwie abstrakcji w stosunku do silnika SQL, wi

Úc bÚdzie dziaïaÊ mniej

wydajnie ni

ĝ bezpoĂrednie podzapytanie.

Zaprezentuj

Ú przykïad demonstrujÈcy zagroĝenia wynikajÈce z ukrywania

kodu SQL w funkcjach u

ĝytkownika. Weěmy pod uwagÚ tabelÚ

flights

opisuj

ÈcÈ loty linii lotniczych. Tabela ta zawiera kolumny:

flight_number

,

departure_time

,

arrival_time

i

iata_airport_codes

5

. S

ïownik kodów (okoïo

dziewi

ÚÊ tysiÚcy pozycji) jest zapisany w osobnej tabeli zawierajÈcej nazwÚ

miasta (lub lotniska, je

Ăli w jednym mieĂcie znajduje siÚ kilka lotnisk),

nazw

Ú kraju itp. OczywiĂcie kaĝda informacja o locie wyĂwietlana

u

ĝytkownikom powinna zawieraÊ nazwÚ miasta i lotniska docelowego

zamiast nic niemówi

Ècego kodu IATA.

W tym miejscu trafiamy na jedn

È ze sprzecznoĂci w inĝynierii nowoczesnego

oprogramowania. Do „dobrych praktyk” programowania zaliczana jest
mi

Údzy innymi modularnoĂÊ, polegajÈca w uproszczeniu na opracowaniu

kilku odosobnionych warstw logiki. Ta zasada sprawdza si

Ú doskonale

w ogólnym przypadku, lecz w kontek

Ăcie baz danych, w których kod

stanowi element wspólny mi

Údzy programistÈ a bazÈ danych, potrzeba

zastosowania modularno

Ăci kodu jest znacznie mniej wyraěna. Zastosujmy

jednak zasad

Ú modularnoĂci, tworzÈc niewielkÈ funkcjÚ zwracajÈcÈ peïnÈ

nazw

Ú lotniska na podstawie kodu IATA:

create or replace function airport_city(iata_code in char)
return varchar2
is
city_name varchar2(50);
begin
select city

5

IATA: International Air Transport Association.

P R O W A D Z E N I E W O J N Y

7 5

into city_naine
from iata_airport_codes
where code = iata_code;
return(city_name);
end;
/

Dla czytelników niezaznajomionych ze sk

ïadniÈ Oracle: wywoïanie

trunc(sysdate

) zwraca dzisiejsz

È datÚ, godzinÚ 00:00, arytmetyka dat jest

oparta na dniach. Warunek dotycz

Ècy czasów odlotu odnosi siÚ zatem do

czasów mi

Údzy 8:30 a 16:00 dnia dzisiejszego. Zapytania wykorzystujÈce

funkcj

Ú

airport_city()

mog

È byÊ bardzo proste, na przykïad:

select flight_number,
to_char(departure_time, 'HH24:MI') DEPARTURE,
airport_city(arrival) "TO"
from flights
where departure_time between trunc(sysdate) + 17/48
and trunc(sysdate) + 16/24
order by departure_time
/

To zapytanie wykonuje si

Ú z zadowalajÈcÈ prÚdkoĂciÈ. Z zastosowaniem

losowej próbki na mojej maszynie siedemdziesi

Èt siedem wierszy jest

zwracanych w czasie 0,18 sekundy (

Ărednia z kilku wywoïañ). Taka

wydajno

ĂÊ jest do przyjÚcia. Statystyki informujÈ jednak, ĝe podczas

wywo

ïania zostaïy odczytane trzysta trzy bloki w piÚÊdziesiÚciu trzech

operacjach odczytu z dysku. A nale

ĝy pamiÚtaÊ, ĝe ta funkcja jest

wywo

ïywana rekurencyjnie dla kaĝdego wiersza.

Alternatyw

È dla funkcji pobierajÈcej dane z tabeli (sïownika) moĝe byÊ

z

ïÈczenie tabel. W tym przypadku zapytanie nieco siÚ skomplikuje:

select f.flight_number,
to_char(f.departure_time, 'HH24:MI') DEPARTURE,
a.city "TO"
from flights f,
iata_airport_codes a
where a.code = f.arrival
and departure_time between trunc(sysdate) + 17/48
and trunc(sysdate) + 16/24
order by departure_time
/

7 6

R O Z D Z I A

’ DRUGI

To zapytanie wykonuje si

Ú w czasie 0,05 sekundy (te same statystyki, ale

nie mamy do czynienia z rekurencyjnymi wywo

ïaniami). Takie oszczÚdnoĂci

mog

È wydaÊ siÚ niewiele warte — trzykrotne przyspieszenie zapytania w wersji

nieoptymalnej trwaj

Ècego uïamek sekundy. Jednak doĂÊ powszechne jest,

ĝe w rozbudowanych systemach (miÚdzy innymi na lotniskach) niektóre
zapytania s

È wywoïywane setki tysiÚcy razy dziennie. Zaïóĝmy, ĝe nasze

zapytanie musi by

Ê wywoïywane piÚÊdziesiÈt tysiÚcy razy dziennie. Gdy

zostanie u

ĝyta wersja zapytania wykorzystujÈca funkcjÚ, caïkowity czas

wykonania tych zapyta

ñ wyniesie okoïo dwie godziny i trzydzieĂci minut.

W przypadku z

ïÈczenia bÚdÈ to czterdzieĂci dwie minuty. Oznacza to

usprawnienie rz

Údu 300%, co w Ărodowiskach o duĝej liczbie zapytañ

oznacza znacz

Èce przyspieszenie, które moĝe przekïadaÊ siÚ na konkretne

oszcz

ÚdnoĂci finansowe. Bardzo czÚsto zastosowanie funkcji powoduje

niespodziewany spadek wydajno

Ăci zapytania. Co wiÚcej, wydïuĝenie czasu

wykonania zapyta

ñ powoduje, ĝe mniej uĝytkowników jest w stanie

korzysta

Ê z bazy danych jednoczeĂnie, o czym wiÚcej piszÚ w rozdziale 9.

Kod funkcji u

ĝytkownika nie jest poddawany analizie

optymalizatora.

Oszcz

Údny SQL

Do

Ăwiadczony programista baz danych zawsze stara siÚ wykonaÊ jak

najwi

Úcej pracy za pomocÈ jak najmniejszej liczby instrukcji SQL-a.

Klasyczny programista natomiast stara si

Ú dostosowaÊ swój program

do ustalonego schematu funkcyjnego. Na przyk

ïad:

-- Odczyt pocz

ątku okresu ksiĊgowego

select closure_date
into dtPerSta
from tperrslt
where fiscal_year=to_char(Param_dtAcc,'YYYY')
and rslt_period='1' || to_char(Param_dtAcc,'MM');
-- Odczyt ko

Ĕca okresu na podstawie daty początku

select closure_date
into dtPerClosure
from tperrslt
where fiscal_year=to_char(Param_dtAcc,'YYYY')
and rslt_period='9' || to_char(Param_dtAcc,'MM');

P R O W A D Z E N I E W O J N Y

7 7

To jest przyk

ïad kodu o bardzo niskiej jakoĂci, mimo tego ĝe szybkoĂÊ jego

wykonania jest zadowalaj

Èca. Niestety, taka jakoĂÊ jest typowa dla wiÚkszoĂci

kodu, z którym musz

È mierzyÊ siÚ specjaliĂci od optymalizacji. Dlaczego

dane s

È odczytywane z zastosowaniem dwóch osobnych zapytañ? Ten

przyk

ïad byï uruchamiany na bazie Oracle, w której ïatwo zaimplementowaÊ

zapytanie zapisuj

Èce odpowiednie wartoĂci w tabeli wynikowej. Wystarczy

odpowiednio zastosowa

Ê instrukcjÚ

ORDER BY

na kolumnie

rslt_period

:

select closure_date
bulk collect into dtPerStaArray
from tperrslt
where fiscal_year=to_char(Param_dtAcc,'YYYY')
and rslt_period in ('1' || to_char(Param_dtAcc,'MM'),
'9' || to_char(Param_dtAcc,'MM'))
order by rslt_period;

Dwie odczytane daty s

È zapisywane odpowiednio w pierwszej i drugiej

komórce macierzy. Operacja

bulk collect

jest specyficzna dla j

Úzyka

PL/SQL, lecz w pozosta

ïych jÚzykach obsïugujÈcych pobieranie danych

do macierzy obowi

Èzuje podobna zasada.

Warto zauwa

ĝyÊ, ĝe macierz nie jest tu niezbÚdna, a te dwie wartoĂci moĝna

pobra

Ê do zmiennych skalarnych, wystarczy zastosowaÊ nastÚpujÈcÈ sztuczkÚ

6

:

select max(decode(substr(rslt_period, 1, 1), -- sprawdzenie pierwszego znaku
'1', closure_date,
-- je

Ğli to '1', zwracamy datĊ

to_date('14/10/1066', 'DD/MM/YYYY'))),
-- w przeciwnym razie
max(decode(substr(rslt_period, 1, 1),
'9', closuredate, -- o t

Ċ datĊ chodzi

to_date('14/10/1066', 'DD/MM/YYYY'))),
into dtPerSta, dtPerClosure
from tperrslt
where fiscal_year=to_char(Param_dtAcc, 'YYYY')
and rslt_period in ('1' || to_char(Param_dtAcc,'MM'),
'9' || to_char(Param_dtAcc,'MM'));

6

Funkcja

decode()

baz danych Oracle dzia

ïa jak instrukcja

CASE

. Dane porównywane

podaje si

Ú w pierwszym argumencie. JeĂli wartoĂÊ jest równa drugiemu argumentowi,

zwracany jest trzeci. Je

Ăli nie zostanie podany piÈty argument, w takim przypadku

czwarty jest traktowany jako warto

ĂÊ

ELSE

; w przeciwnym razie, je

Ăli pierwszy

argument jest równy czwartemu, zwracany jest pi

Èty i tak dalej w odpowiednich

parach warto

Ăci.

7 8

R O Z D Z I A

’ DRUGI

W tym przyk

ïadzie wynik bÚdzie dwuwierszowy, a oczekujemy wyniku

jednowierszowego zawieraj

Ècego dwie kolumny (tak, jak w przykïadzie

z macierz

È). Dokonamy tego, sprawdzajÈc za kaĝdym razem wartoĂÊ

w kolumnie rozró

ĝniajÈcej wartoĂci z kaĝdego wiersza, czyli

rslt_period

.

Je

Ăli odnaleziony wiersz jest tym, którego szukamy, zwracana jest odpowiednia

data. W przeciwnym razie zwracana jest dowolna data (w tym przypadku
data bitwy pod Hastings), znacznie starsza (z punktu widzenia porównania
„mniejsza”) od jakiejkolwiek daty w tej tabeli. Wybieraj

Èc maksimum,

mamy pewno

ĂÊ, ĝe otrzymamy odpowiedniÈ datÚ. Ten trik jest bardzo

praktyczny i mo

ĝna go z powodzeniem stosowaÊ do danych znakowych

lub liczbowych. Wi

Úcej tego typu technik omówiÚ w rozdziale 11.

SQL jest j

Úzykiem deklaratywnym, zatem naleĝy zachowaÊ dystans

do proceduralno

Ăci zastosowañ biznesowych.

Ofensywne kodowanie w SQL-u

Programistom cz

Ústo doradza siÚ programowanie defensywne polegajÈce

mi

Údzy innymi na sprawdzaniu poprawnoĂci wszystkich parametrów przed

ich zastosowaniem w wywo

ïaniu. Przy korzystaniu z baz danych wiÚksze

zalety ma jednak kodowanie ofensywne, polegaj

Èce na wykonywaniu kilku

dzia

ïañ równolegle.

Dobrym przyk

ïadem jest mechanizm obsïugi kontroli poprawnoĂci

polegaj

Ècy na wykonywaniu serii sprawdzeñ i zaprojektowany w ten

sposób,

ĝe w przypadku wystÈpienia choÊ jednego wyniku negatywnego

wywo

ïywany jest wyjÈtek. Zaïóĝmy, ĝe mamy przetworzyÊ pïatnoĂÊ kartÈ

p

ïatniczÈ. Kontrola takiej transakcji skïada siÚ z kilku etapów. Naleĝy

sprawdzi

Ê, ĝe poprawny jest identyfikator klienta i numer karty oraz ĝe sÈ

prawid

ïowo ze sobÈ powiÈzane. Naleĝy równieĝ zweryfikowaÊ datÚ

wa

ĝnoĂci karty. No i oczywiĂcie bieĝÈcy zakup nie moĝe spowodowaÊ

przekroczenia limitu karty. Gdy wszystkie testy zako

ñczÈ siÚ pomyĂlnie,

mo

ĝe zostaÊ przeprowadzona operacja obciÈĝenia konta karty.

Niedo

Ăwiadczony programista mógïby napisaÊ coĂ takiego:

select count(*)
from customers
where customer_id = id_klienta

P R O W A D Z E N I E W O J N Y

7 9

W tym miejscu nast

Úpuje sprawdzenie wyniku, a jeĂli wynik jest pomyĂlny,

nast

Úpuje wywoïanie:

select card_num, expiry_date, credit_limit
from accounts
where customer_id = id_klienta

Tutaj równie

ĝ nastÈpi sprawdzenie wyniku, po którym (w przypadku

powodzenia) wywo

ïywana jest transakcja finansowa.

Do

Ăwiadczony programista zapewne napisze to nieco inaczej

(zak

ïadajÈc, ĝe

today()

to funkcja zwracaj

Èca bieĝÈcÈ datÚ):

update accounts
set balance = balance – wielkosc_zamowienia
where balance >= wielkosc_zamowienia
and credit_limit >= wielkosc_zamowienia
and expiry_date > today()
and customer_id = id_klienta
and card_num = numer_karty

Tutaj nast

Úpuje sprawdzenie liczby zmodyfikowanych wierszy. JeĂli jest to

zero, przyczyn

Ú takiej sytuacji moĝna sprawdziÊ za pomocÈ jednego zapytania:

select c.customer_id, a.card_num, a.expiry_date,
a.creditlimit, a.balance
from customers c
left outer join accounts a
on a.customer_id = c.customer_id
and a.cardnum = numer_karty
where c.customer_id = id_klienta

Je

Ăli zapytanie nie zwróci ĝadnego wiersza, oznacza to, ĝe wartoĂÊ

customer_id

jest b

ïÚdna, jeĂli w wyniku

card_num

jest NULL, oznacza to,

ĝe numer karty jest bïÚdny itd. Jednak w wiÚkszoĂci przypadków to drugie
zapytanie nie b

Údzie nawet uruchomione.

UWAGA

Warto zwróci

Ê uwagÚ na wywoïanie

count(*)

w pierwszym fragmencie kodu

niedo

Ăwiadczonego programisty. To doskonaïa ilustracja bïÚdnego uĝycia funkcji

count(*)

do sprawdzenia, czy w tabeli istniej

È pozycje speïniajÈce warunek.

Zasadnicz

È cechÈ programowania ofensywnego jest opieranie swoich zaïoĝeñ

na rozs

Èdnym prawdopodobieñstwie. Na przykïad nie ma wiÚkszego sensu,

by sprawdza

Ê istnienie klienta. JeĂli nie istnieje, w bazie danych nie

znajdzie si

Ú ĝaden dotyczÈcy go rekord (zatem w wyniku wywoïania

zapytania bez wcze

Ăniejszej kontroli i tak nie zostanÈ zmodyfikowane

8 0

R O Z D Z I A

’ DRUGI

ĝadne dane)! Zakïadamy, ĝe wszystko zakoñczy siÚ powodzeniem, a nawet
je

Ăli tak siÚ nie stanie, przygotowujemy mechanizm ratujÈcy nas z opresji

w tym jednym punkcie — i tylko w tym jednym. Co interesuj

Èce, tego

typu strategia przypomina nieco „optymistyczn

È kontrolÚ wspóïdzielenia”

zastosowan

È w niektórych bazach danych. Chodzi o to, ĝe zaïoĝono z góry,

i

ĝ z duĝym prawdopodobieñstwem nie bÚdzie sytuacji konfliktu dostÚpu

do danych, a je

Ăli jednak siÚ to zdarzy, dopiero wówczas uruchamiane sÈ

stosowne konstrukcje kontrolne. W wyniku zastosowania tej strategii
wydajno

ĂÊ systemu jest znacznie wyĝsza niĝ w przypadku systemów

stosuj

Ècych strategiÚ pesymistycznÈ.

Nale

ĝy kodowaÊ w oparciu o rachunek prawdopodobieñstwa.

Zak

ïada siÚ, ĝe najprawdopodobniej wszystko zakoñczy siÚ

pomy

Ălnie, a dopiero w przypadku niepowodzenia uruchamia siÚ

plany awaryjne.

¥wiadome uĝycie wyjÈtków

Mi

Údzy odwagÈ a zapalczywoĂciÈ róĝnica jest doĂÊ subtelna. Gdy zalecam

stosowanie agresywnych metod kodowania, nie sugeruj

Ú bynajmniej szarĝy

w stylu Lekkiej Brygady pod Ba

ïakïawÈ

7

. Programowanie z u

ĝyciem wyjÈtków

równie

ĝ moĝe byÊ konsekwencjÈ brawury, gdy dumni programiĂci decydujÈ siÚ

„i

ĂÊ na caïoĂÊ”. MajÈ bowiem przeĂwiadczenie, ĝe testy i moĝliwoĂÊ obsïugi

wyj

Ètków bÚdÈ ich tarczÈ w tym boju. No tak, odwaĝni umierajÈ mïodo!

Jak sugeruje nazwa, wyj

Ètki to zdarzenia wystÚpujÈce w niecodziennych

sytuacjach. W programowaniu z u

ĝyciem baz danych nie wszystkie wyjÈtki

wykorzystuj

È te same zasoby systemowe. Naleĝy poznaÊ te uwarunkowania,

aby korzysta

Ê z wyjÈtków w sposób inteligentny. Moĝna wyróĝniÊ dobre

wyj

Ètki, wywoïywane, zanim zostaje wykonane dziaïanie, oraz zïe wyjÈtki,

które s

È wywoïywane dopiero po fakcie wyrzÈdzenia powaĝnych zniszczeñ.

7

Podczas Wojny Krymskiej, w 1854 roku, odby

ïa siÚ bitwa miÚdzy wojskami Anglii,

Francji i Turcji a si

ïami Rosji. W wyniku nieprecyzyjnego rozkazu oraz osobistych

animozji mi

Údzy niektórymi z dowódców siï sprzymierzonych doszïo do szarĝy ponad

sze

Ăciuset ĝoïnierzy kawalerii brytyjskiej wprost na bateriÚ rosyjskiej artylerii. Na skutek

starcia zgin

Úïo okoïo stu dwudziestu kawalerzystów oraz poïowa koni, bez jakiegokolwiek

dobrego rezultatu. Odwaga ludzi zosta

ïa wkrótce wysïawiona przez wiersz Tennysona,

a potem w kilku filmach hollywoodzkich, dzi

Úki czemu zwykïa gïupota jednej militarnej

decyzji obróci

ïa siÚ w mit.

P R O W A D Z E N I E W O J N Y

8 1

Zapytanie wykorzystuj

Èce klucz gïówny, które nie znajdzie ĝadnych

wierszy, wykorzystuje niewiele zasobów — sytuacja jest identyfikowana
ju

ĝ na etapie przeszukiwania indeksu. JeĂli jednak w celu stwierdzenia, ĝe

dane spe

ïniajÈce warunek nie wystÚpujÈ w tabeli, zapytanie nie moĝe uĝyÊ

indeksu, zachodzi konieczno

ĂÊ dokonania peïnego przeszukiwania tabeli

(full scan). W przypadku wielkich tabel czas potrzebny do odczytu
sekwencyjnego w systemie dzia

ïajÈcym w danym monecie na granicy

swojej wydajno

Ăci moĝna potraktowaÊ jako czynnik katastrofalny.

Niektóre wyj

Ètki sÈ szczególnie kosztowne, nawet przy najbardziej

sprzyjaj

Ècych okolicznoĂciach. Weěmy na przykïad wykrywanie

duplikatów. W jaki sposób w bazie danych jest obs

ïugiwany mechanizm

unikalno

Ăci? Prawie zawsze sïuĝy do tego unikalny indeks i gdy wystÈpi

próba wprowadzenia do tabeli warto

Ăci zawierajÈcej klucz wystÚpujÈcy juĝ

w indeksie, zadzia

ïa mechanizm zabezpieczajÈcy przed zduplikowaniem

klucza, co efektywnie zablokuje zapis duplikatu. Jednak

ĝe zanim nastÈpi

próba zapisu indeksu (weryfikacji duplikatu), w tabeli musi zosta

Ê

fizycznie zapisana odpowiednia warto

ĂÊ (do procedury indeksujÈcej

przesy

ïany jest fizyczny adres wiersza w tabeli). Z tego wynika, ĝe

naruszenie ograniczenia unikalno

Ăci klucza nastÚpuje po fakcie zapisu

w tabeli danych, które musz

È byÊ wycofane, czemu dodatkowo towarzyszy

komunikat informuj

Ècy o wystÈpieniu bïÚdu. Wszystkie te operacje wiÈĝÈ

si

Ú z okreĂlonym kosztem czasowym. NajwiÚkszym jednak grzechem jest

podejmowanie samodzielnych prób dzia

ïania na poziomie wyjÈtków.

W takim przypadku przejmujemy od systemu zadanie obs

ïugi operacji

na poziomie wierszy, nie ca

ïych zbiorów danych, czyli sprzeciwiamy siÚ

fundamentalnej koncepcji relacyjnego modelu danych. Konsekwencj

È

wyst

Úpowania czÚstych naruszeñ ograniczeñ w bazie bÚdzie w takim

przypadku stopniowa degradacja jej wydajno

Ăci.

Przyjrzyjmy si

Ú przykïadowi opartemu na bazie Oracle. Zaïóĝmy, ĝe

pracujemy nad integracj

È systemów informatycznych dwóch poïÈczonych firm.

Adres e-mail zosta

ï ustandaryzowany w postaci wzorca

<InicjaïNazwisko>

i ma zawiera

Ê co najwyĝej dwanaĂcie znaków, wszystkie spacje i znaki

specjalne s

È zastÚpowane znakami podkreĂlenia

8

.

8

Przyk

ïad nie uwzglÚdnia obsïugi polskich znaków diakrytycznych, niedozwolonych

w adresach e-mail — przyp.red.

8 2

R O Z D Z I A

’ DRUGI

Za

ïóĝmy, ĝe nowÈ tabelÚ pracowników naleĝy wypeïniÊ trzema tysiÈcami

wierszy z tabeli

employees_old

. Chcemy te

ĝ, ĝeby kaĝdy pracownik

posiada

ï unikalny adres e-mail. Z tego powodu musimy zastosowaÊ

okre

ĂlonÈ zasadÚ nazewnictwa: Jan Kowalski bÚdzie miaï e-mail o postaci

jkowalski

, a Józef Kowalski (

ĝadnego pokrewieñstwa)

jkowalski2

itd.

W naszych danych testowych znajdziemy trzydzie

Ăci trzy potencjalne

pozycje konfliktowe, co przy próbie

ïadowania danych da nastÚpujÈcy efekt:

SQL> insert into employees(emp_num, emp_name,
emp_firstname, emp_email)
2 select emp_num,
3 emp_name,
4 emp_firstname,
5 substr(substr(EMP_FIRSTNAME, 1, 1)
6 ||translate(EMP_NAME, ' ''', '_'), 1, 12)
7 from employees_old;

insert into employees(emp_num, emp_name, emp_firstname, emp_email)
*
ERROR at line 1:
ORA-0000l: unique constraint (EMP_EMAIL_UQ) violated

Elapsed: 00:00:00.85

Trzydzie

Ăci trzy duplikaty ze zbioru trzech tysiÚcy to trochÚ powyĝej 1%,

by

Ê moĝe zatem warto byïoby obsïuĝyÊ te 99%, a elementy problemowe

obs

ïuĝyÊ z uĝyciem wyjÈtków? W koñcu 1% danych nie powinien

powodowa

Ê znacznego obciÈĝenia bazy w wyniku procedury obsïugi

wyj

Ètków. Poniĝej kod realizujÈcy ten optymistyczny scenariusz:

SQL> declare
2 v_counter varchar2(l2);
3 b_ok boolean;
4 n_counter number;
5 cursor c is select emp_num,
6 emp_name,
7 emp_firstname
8 from employees_old;
9 begin
10 for rec in c
11 loop
12 begin
13 insert into employees(emp_num, emp_name,
14 emp_firstname, emp_email)

Czytaj dalej...

P R O W A D Z E N I E W O J N Y

8 3

15 values (rec.emp_num,
16 rec.emp_name,
17 rec.emp_firstname,
18 substr(substr(rec.emp_firstname, 1, 1)
19 ||translate(rec.emp_name, ' ''', ' '), 1, 12));
20 exception
21 when dup_val_on_index then
22 b_ok := FALSE;
23 n_counter := 1;
24 begin
25 v counter := ltrim(to_char(n_counter));
26 insert into employees(emp_num, emp_name,
27 emp_firstname, emp_email)
28 values (rec.emp_num,
29 rec.emp_name,
30 rec.emp_firstname,
31 substr(substr(rec.emp_firstname, 1, 1)
32 ||translate(rec.emp_name, ' ''', '__'), 1,
33 12 - length(v_counter)) || v_counter);
34 b_ok : = TRUE;
35 exception
36 when dup_val_on_index then
37 n_counter := n_counter + 1;
38 end;
39 end;
40 end loop;
41 end;
40 /

PL/SOL procedure successfully completed.
Elapsed: 00:00:18.41

Jaki jest jednak rzeczywisty koszt obs

ïugi wyjÈtków? Gdyby ten sam test

przeprowadzi

Ê na danych pozbawionych duplikatów, okaĝe siÚ, ĝe koszt

rzeczywistej obs

ïugi wyjÈtków (ich wystÈpieñ) jest pomijalny. Procedura

wywo

ïana na danych z duplikatami dziaïa okoïo osiemnaĂcie sekund,

podobnie jak na danych bez duplikatów. Jednak gdy wykonamy ten test
(dane bez duplikatów) na naszej oryginalnej procedurze nieobs

ïugujÈcej

wyj

Ètków (

insert...select

), zauwa

ĝymy, ĝe wykona siÚ znacznie szybciej

od p

Útli. PrzeïÈczenie siÚ w tryb „wiersz po wierszu” powoduje okoïo

50-procentowy narzut czasu przetwarzania. Czy w takim razie mo

ĝliwe

jest unikni

Úcie tego trybu? To kwestia tego, czy zdecydujemy siÚ na rezygnacjÚ

z mechanizmu obs

ïugi wyjÈtków, który to wïaĂnie zmusiï nas do obsïugi

danych w trybie wierszowym.