Skuteczność procedur przechowywanych w porównaniu z surowymi zapytaniami

Dużo czytałem po obu stronach tej debaty: czy można uzyskać znaczący wzrost wydajności dzięki zastosowaniu tylko procedur przechowywanych zamiast surowych zapytań? Jestem szczególnie zainteresowany programem SQL Server, ale interesowałbym się wszystkimi bazami danych.

Tak jest mniej w SQL Server 2008 i nowszych wersjach, ale nadal tam jest. Sprowadza się to do pamięci podręcznej planu wykonania i możliwości automatycznego parametryzowania zapytań wysyłanych przez SQL Server. Podczas korzystania z procedur przechowywanych (które nie zawierają dynamicznego SQL) zapytania są już parametryzowane, więc SQL Server nie „ trzeba wygenerować plan dla każdego zapytania, gdy jest ono uruchomione, ponieważ plany są już przechowywane w pamięci podręcznej planu.

I nie zapomnij o problemach bezpieczeństwa (dynamiczny SQL, minimalne uprawnienia itp.), Które znikają podczas korzystania z procedur przechowywanych.

Gdy aplikacja korzysta z dynamicznego SQL w stosunku do tabel podstawowych do wybierania, wstawiania, aktualizowania i usuwania danych w tabelach, aplikacja musi mieć prawa do wszystkich tych obiektów bezpośrednio. Więc jeśli ktoś używa SQL Injection, aby dostać się na serwer, będzie miał prawo do zapytania, zmiany lub usunięcia wszystkich danych w tych tabelach.

Jeśli korzystasz z procedur przechowywanych, mają one jedynie prawo do wykonania procedur przechowywanych, zwracając tylko informacje, które procedura zwrócona zwróci. Zamiast wydać szybką instrukcję usuwania i wysadzić wszystko w powietrze, musieliby dowiedzieć się, jakie procedury można zastosować do usunięcia danych, a następnie dowiedzieć się, jak skorzystać z tej procedury.

Biorąc pod uwagę, że SQL Injection jest najłatwiejszym sposobem włamania się do bazy danych, jest to dość ważne.

Jako dodatek do odpowiedzi Denny'ego, nierzadko zdarza się, aby znaleźć systemy, w których znaczna pamięć puli buforów jest marnowana na pojedyncze lub mało użyteczne plany wykonania ad-hoc, utworzone w wyniku zapytań używanych w procesach.

Najgorsze przypadki ostatnio, 8 GB przypisane do instancji, pamięć podręczna planu 3 GB, plany jednorazowego użytku 2,5 GB. Większość z nich to SQL2005, więc nie było opcji wypróbowania ustawienia optymalizacji obciążeń ad-hoc.

Z pewnością coraz trudniej jest włączyć wydajność do uzasadnienia dla procedur w stosunku do surowych zapytań. Jednym z najmocniejszych argumentów dla mnie jest teraz: „Jeśli korzystasz z procedur, znacznie łatwiej jest mi pomóc, gdy pojawią się problemy z wydajnością”. Dynamiczny interfejs / linq / orm nie uniemożliwia strojenia, ale może poważnie ograniczyć twoje opcje.

SQL Server buforuje i optymalizuje procedury przechowywane oraz SQL ad-hoc w ten sam sposób. Na przykład ta procedura:

create procedure dbo.TestSB(@id int) as select * from Orders where id = @id

Zostanie zoptymalizowany i buforowany identycznie do:

select * from Orders where id = @id

Jednak następujący kod SQL ad-hoc nie może być skutecznie buforowany z powodu zakodowanej wartości:

select * from Orders where id = 42

Chociaż wydajność jest taka sama, istnieją dobre powody, aby używać procedur przechowywanych. Procedury przechowywane zapewniają wyraźne oddzielenie DBA od twórców aplikacji. Dobrze jest mieć dodatkową warstwę obrony między cennymi danymi a ciągle zmieniającymi się programami :)