Czy ty też nadużywasz procedur składowanych?
Marzec 5, 2009
Derik Whittaker na swoim blogu poruszył bardzo ważną kwestię związaną z nadużywaniem procedur składowanych w celu pisania logiki biznesowej. Jako, że należę do zwolenników programowania obiektowego, całkowicie się zgadzam ze zdaniem Derika. W moim ówczesnym jak i w wcześniejszych projektach, często spotykałem się z nadużyciem procedur składowanych do pisania logiki biznesowej, dlatego też jestem świadomy wielu wad takiego podejścia. Podobnie jak Derrik Whittaker, napotkałem się z argumentacją, że procedurę składowaną można szybko zastąpić bez zbędnego ponownego „deployowania” aplikacji. Moim zdaniem nigdy nie powinno dojść do takiej sytuacji dlatego, że podmienianie funkcjonalności „na żywca” wiąże się ze zbyt wielkim ryzykiem. Jaką możemy mieć pewność, że dana zmiana będzie działać? Sytuacje takie najczęściej kojarzą mi się ze scenami z filmów, w których główny bohater przecina kabelki bomby. W rzeczywistości nie jest tak jak w filmach, gdzie wszystko się kończy szczęśliwie z amerykańską flagą w tle:).
Uważam, że częstym powodem podmiany procedur składowanych na środowisku produkcyjnej jest metodologia FDD(Fear Driven Development), w której każdy się boi cokolwiek ruszyć, dlatego, że szybko może doprowadzić to do „wybuchu”. W związku z tym ponowne deployowanie aplikacji z poprawionym błędem wydaje się większym ryzykiem niż podmiana procedury. Najszybszym rozwiązaniem tego rodzaju problemów jest stworzenie skryptów pozwalających zautomatyzować całkowicie proces deploymentu. Jeżeli posiadacie w projekcie dokument z listą koniecznych kroków, do której sięgacie przy każdym deploymencie, to jest to pierwszy poważny znak na to, że nadszedł czas na poprawienie lub stworzenia skryptów. Same skrypty to tylko początek sukcesu. Kolejnym ważnym elementem są automatyczne testy, które muszą się poprawnie wykonać zanim aplikacja zostanie zdeployowana. Zakładając, że dany projekt korzysta z serwera Continues Integration, tego rodzaju zapewnienie nie stwarza żadnego problemu.
Kolejnym argumentem często używanym przez zwolenników procedur składowanych jest wydajność. Trudno czasami zaprzeczyć temu faktowi, dlatego, że wywołanie jednej procedury zawierającej obszerną logikę biznesową to wyłącznie tylko jedno zdalne zapytanie, które musi dokonać nasza aplikacja. W przypadku zaś, gdy nasza operacja biznesowa dokonywana będzie po stronie aplikacji i wymagać będzie ona stworzenia wielu obiektów zapisywanych w bazie danych, jesteśmy zmuszeni do wielu zdalnych zapytań, a to prowadzi z kolei do problemów wydajnościowych. Dlatego też warto skorzystać z dostępnych mapperów obiektowo relacyjnych, z których niektóre są w stanie wywołać wiele kwerend w ramach jednego zapytania (operacje wsadowe). Należy zwrócić uwagę, że to rozwiązanie nie jest złotym środkiem na wszystko. Jednym z przypadków nadużycia jest tworzenie procesu ETL (Extract Transform Load) przy wykorzystaniu mappera dlatego, że wydajność będzie znikoma w porównaniu do wykorzystywania procedur składowanych lub narzędzi przewidzianych do tego rodzaju procesów takich jak na przykład SSIS, a w przypadku tego typu procesów wydajność odgrywa bardzo ważną rolę.
Jeżeli piszecie logikę w procedurach składowanych i dla operacji innych niż wsadowych ponieważ uważacie, że jest to bardziej wydajne rozwiązanie, zadajcie sobie proste pytanie: Jak często dokonujemy zmian w kodzie? Często jest tak, że zmieniają się wymagania biznesowe, w takich przypadkach klient oczekuje by dana funkcjonalność została jak najszybciej zmieniona i mu udostępniona. Wydajność w tym momencie nie jest kluczowym elementem. Zresztą moim zdaniem często wydajność w aplikacjach biznesowych jest ważna tylko w przypadku, gdy już natrafiamy na problemy z jej brakiem, wówczas to jesteśmy zmuszeni do zlokalizowania wąskiego gardło.
Powracając do problemu ze zmianami. Jak sama nazwa wskazuje procedury składowane są pisane w postaci kodu proceduralnego, co niesie ze sobą wiele ograniczeń w przypadku realizacji zaawansowanej logiki. W dzisiejszych czasach if i else nie wystarcza, dlatego też stosujemy języki obiektowe. Moim zdaniem brak możliwości programowania obiektowego w procedurach składowanych wskazuje na to, że twórcy języka SQL nie mieli zamiaru by język ten służył do programowania skomplikowanej logiki. Poza tym, który z Was jest wstanie zrozumieć w ciągu 5 min zapytanie z więcej niż pięcioma joinami i pod zapytaniem w warunku? Jak tego rodzaju argumenty nie przemawiają do was, to znaczy, że nigdy nie napotkaliście się na procedurę składowaną, która miała więcej niż 100 linii. Kolejnym dużym problemem jest brak możliwości refaktoryzacji kodu procedur składowanych przy pomocy dostępnych narzędzi developerskich. Nie wspominam już o testach jednostkowych. Choć w tym zakresie pojawiło się parę produktów umożliwiających testowanie, co nie znaczy, że zarządzanie tego typu testami jest proste.
Jednym z ciekawych argumentów, z którymi się również spotkałem była prostota wykorzystywania procedur składowanych do integracji wielu aplikacji. Rozwiązanie to ma na celu wyeliminowanie duplikacji kodu, ale jako, że każda z aplikacji jest tworzona najczęściej przez oddzielne zespoły, rozwiązanie to wręcz prowadzi do tworzenia wielu identycznych procedur. W przypadku, gdy dwie aplikacje będą korzystać z tej samej procedury, jaką mamy pewność, że wszelkiego rodzaju zmiany w procedurze konieczna dla jednej aplikacji nie spowoduje błędu w tej drugiej? Każda aplikacja powinna posiadać swój własny ograniczony kontekst, którego nie ma sensu reprodukować w innej aplikacji. Dlatego też nie powinno dochodzić do potrzeby wywoływania tej samej funkcjonalności przez dwie różne aplikacje z poziomu procedury składowanej. Stosowanie współdzielonej bazy danych w dzisiejszych czasach – opanowanych przez architekturę SOA – jest kruche, dlatego warto zastosować integrację na poziomie funkcyjnym.
Mam nadzieję, że argumenty te przekonały Was do rozważnego zastanowienia się nad tym, czy procedura składowana jest odpowiednim rozwiązaniem na Wasze potrzeby, zanim ją użyjecie. Zachęcam również do wyrażenie swojego zdania na ten temat.
Entry Filed under: Programowanie. Tagi: Logika biznesowa, ORM, SQL.
10 Comments Add your own
Leave a Comment
Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <pre> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>
Trackback this post | Subscribe to the comments via RSS Feed
1. dotnetomaniak.pl | Marzec 11, 2009 at 2:02 pm
Czy ty też nadużywasz procedur składowanych? « !FrAgile Thinking…
Dziękujemy za publikację – Trackback z dotnetomaniak.pl…
2.
dario-g | Marzec 11, 2009 at 10:33 pm
Osobiście nie używam procedur składowanych od… niech policzę… od ponad 3 lat i nie mam żadnych problemów z wydajnością w swoich aplikacjach
3.
Darek | Marzec 16, 2009 at 12:23 am
Nie szukajmy w SP diabła, bo chyba nie o to chodzi. Zgodzę się z faktem, że SP są nadużywane. Co gorsze są uważane za gwarant wysokiej wydajności aplikacji, najszybszy, najbezpieczniejszy i naj-naj sposób na wykonanie operacji na bazie itp. Prowadzi to do tego o czym piszesz. Ciekawy post.
4.
thoughtcriminal | Marzec 30, 2009 at 1:27 am
Refactoring SQL’a http://www.red-gate.com/products/SQL_Refactor/index.htm
Do unit testing’u bazy też już są narzędzia, np. http://msdn.microsoft.com/en-us/library/bb381703(VS.80).aspx
5.
jenrom | Marzec 30, 2009 at 9:34 am
@Darek: Zgodze się z tobą, że tego typu narzędzia pomogą, przy czym ich funkcjonalność nie jest na tak wysokim poziomie jak ich odpowiedników stosowanych w objektówce. Zresztą na to zwróciłem uwagę w poście.
PS: Automatyczne testowanie bazy jest mało wydajne i problematyczne jest przygotowywanie danych potrzebnych do poprawnego wykonania się testu. Co w głównej mierze zniechęca mnie do używania narzędzi typu Team Edition for DB. Co nie znaczy, że w niektórych przypadkach nie należy tego typu rozwiązania używać. Lepiej wolne testy niż żadne
6.
thoughtcriminal | Marzec 30, 2009 at 1:36 am
@Darek: co do wydajności, to jeśli chodzi o MSSQL’a to ponoć już od wersji 2000 prekompilowane zapytania są cache’owane, więc zysku na wydajności raczej nie ma. Natomiast SP mogą być postrzegane jako warstwa i oferować dzięki temu wiele zalet jak m.in. lepsze zarządzenie bezpieczeństwem.
7.
Robert | Kwiecień 30, 2009 at 9:21 pm
Witam, jestem “dinozaurem” (kończyłem studia w 1990, gdy Windows nie był rozpowszechniony), na SP (Informix) zjadłem zęby, więc chciałbym podać kilka argumentów na ich rzecz.
Po pierwsze, są takie zastosowania, gdzie SP to jedyna opcja – gdy coś ma się dziać na triggerze (logowanie zmian, propagacja zmian z jednej tabeli do innej).
Po drugie więzy integralności, np. w systemie obsługi karty kredytowej procedura na triggerze do tabeli kart sprawdza, czy numer karty znajduje się w zakresie, który został podany w parametrach. Często są to bardzo złożone reguły biznesowe.
Po trzecie zasada DRY – skoro do naszej bazy podpięte są programy różnych firm napisane w VB, PowerBuilder, java, .NET, Ab Initio (ETL) to lepiej jest mieć logikę biznesową w procedurach. Jeśli np. zmienimy procedurę liczącą kwotę opłaty okresowej, to wszyscy klienci bazy od razu to uwzględnią – takie mini SOA. W przeciwnym razie musielibyśmy zmieniać kilka różnych aplikacji i ich wdrożenie synchronizować w czasie.
Po czwarte łatwość testowania. Jak zamawiamy u dostawcy oprogramowania drobną zmianę, ale przysyła nam jeden wielki msi, to wiadomo że na pewno spieprzyli coś jeszcze w kodzie, który nie miał być zmieniany. A więc testy regresyjne całej aplikacji tabunem użytkowników. Kiedy natomiast dostarczają kilka zmienionych procedur bazy, to przynajmniej wiemy że wystarczy przetestować tylko te zmienione procedury.
Po piąte wydajność. W aplikacjach takich jak system autoryzacji transakcji to oczywiście najwyższy priorytet (czyli C i procedury składowane). Ale również i w aplikacjach
mniej krytycznych czasowo to jest ważne. Proces końca dnia liczący opłaty i odsetki – tutaj teoretycznie ORM byłby idealny, bo mamy bardzo złożony model dziedziny. W praktyce nikt tak nie robi. Chodzi nie tylko o wydajność pod względem czasu wykonania, ale też niektórych rzeczy w ORM nie da się zrobić wcale! Typowe przetwarzanie końca dnia to pętla typu (Informix):
foreach
select *
Into r_rachunki.*
From rachunki
begin work;
commit work;
end foreach
Problem w tym, że w klasycznym NHibernate mogę zrobić tylko coś takiego:
foreach (Account account in Criteria.List())
– co zabija serwer, bo cała lista rachunków (milion) jest wczytywana do pamięci
albo: Interfejs Enumerable, który przetwarza wprawdzie po jednym rekordzie, ale dla każdego rachunku wykonuje dodatkowy pojedyńczy select. A jest jeszcze transakcja w środku pętli, czyli coomit zamknie kursor po stronie bazy.
ORM nigdy nie będzie rozwiązaniem wydajnym, bo nie działa na zbiorach, np. takie coś w procedurach końca roku:
update rachunki
set odsetki_rok_poprzedni = odsetki_aktualne,
odsetki_aktualne=0
znów zabija ORM’a, bo każdy rachunek zostanie pojedyńczo pobrany z bazy, a potem zapisany.
Tak więc ORM jest dobry dla obsługi niewielkiej liczby obiektów, typowo w aplikacjach webowych. Ułatwia też tworzenie testów jednostkowych (ale to tylko część, i to ta najprostsza całego procesu testowania). Pozwala uniknąć “SQLi w długich stringach”, jednak to ostatnie zapewniają również SP (są tylko “krótkie stringi”, a liczba odwołań do bazy mniejsza).
Wiem, że jestem na wymarciu, pocieszam się że wciąż jednak istnieje mniejszość w IT, która nie może, lub nie chce nawrócić się na rozwiązania czysto obiektowe (o jej istnieniu świadczą artykuły takie jak “A Database-centric Approach to J2EE Application Development”).
8.
Marian Boczek | Maj 19, 2009 at 8:01 am
Naprawdę procedury składowe są aż tak tragiczne? Od zawsze ich używałem i szczerze mówiąc nigdy nie miałem żadnych problemów. Chociaż może rzeczywiście trochę ich nadużywam…
9.
jenrom | Maj 19, 2009 at 12:36 pm
@Robert
Zgodzę się z tobą, że występują sytuacje w których należy korzystać z procedur składowanych, o czym w poście wspominałem . Mimo to uważam, że stosowanie triggerów do wywoływania skomplikowanych reguł biznesowych jest “hardcorowe”, dlatego że czymś takim się bardzo trudno zarządza, w dużych projektach jest wręcz strzałem w własną stopę.
Tworzenie SOA opartego o wspólną baze danych jest moim zdaniem jednym z największych anty wzorców, który prowadzi do jednego wielkiego tzw “Big Bull of Mud”, w którym nie tylko logika (zachowanie) jest “pokręcona”, ale również dane. Spowodowane jest to głównie tym, że różne aplikacje korzystają z tych samych danych w różnym kontekście operacyjnym, co skolei eliminuje możliwość stworzenie wspólnego współdzielonego modelu.
W wątku dotyczącym testowania wskazujesz na rozwiązanie, które moim zdaniem jest tylko zaślepką na kolejny poważny problem, czyli brak automatyzacji testów i brak buildu integrującego. O tym problemie wspominałem już wcześniej. Powracając do twoich argumentów, to chętnie bym się dowiedział skąd masz pewność, że zmiana w jednej procedurze składowanej czy trigerzenie nie może wpływać na inne funkcjonalności całego systemu.
Odnośnie piątego argumentu. Moim zdaniem procedura składowana nie jest gwarantem wydajności. Owszem zgodze się z tobą, że w przypadku wywoływania tzw. jobów możemy zyskać na wydajności ze względu na minimalizację zdalnych zapytań do bazy. W systemach informatycznych przewyższa jednak liczba operacji składających się z pojedynczej transakcji wywołanych przez użytkowników systemu, typu złożenie zamówienia. W tym przypadku liczba zdalnych zapytań jest znikoma i już nie uzyskamy zbyt wielkiego wzrostu wydajności, stosując procedurę składowaną.
@Marian Boczek
Jeżeli jesteś zadowolony z procedur składowanych i nie przeszkadzają ci w codziennej pracy to używaj ich dalej. W innym przypadku rozważ sens wyżej przedstawionych argumentów.
Ja sam swego czasu korzystałem nagminnie z procedur składowanych i myślałem, że jest to super wydajne, elastyczne itd. Dzisiaj jak widać już tak nie jest
10.
zajefajnyx | Listopad 23, 2009 at 7:18 pm
Chcemy wiecej