Procedury składowane a wbudowany SQL

Wiem, że procedury składowane są bardziej wydajne dzięki ścieżce wykonania (niż wbudowany sql w aplikacjach). Jednak po naciśnięciu nie wiem, dlaczego.

Chciałbym poznać techniczne uzasadnienie tego (w sposób, który mogę wyjaśnić komuś później).

Czy ktoś może mi pomóc sformułować dobrą odpowiedź?

Wierzę, że to zdanie było prawdziwe w pewnym momencie, ale nie w obecnych wersjach SQL Server. Cały problem polegał na tym, że w dawnych czasach instrukcje SQL ad hoc nie mogły być właściwie optymalizowane, ponieważ SQL Server mógł optymalizować / kompilować tylko na poziomie partii. Teraz mamy optymalizację na poziomie instrukcji, więc poprawnie sparametryzowane zapytanie pochodzące z aplikacji może korzystać z tego samego planu wykonania, co zapytanie wbudowane w procedurę przechowywaną.

Nadal wolę procedury składowane od strony DBA z następujących powodów (a kilka z nich może mieć ogromny wpływ na wydajność):

• Jeśli mam wiele aplikacji, które ponownie używają tych samych zapytań, procedura przechowywana zawiera tę logikę, zamiast zaśmiecać to samo zapytanie ad hoc wiele razy w różnych bazach kodów. Aplikacje, które ponownie wykorzystują te same zapytania, mogą również podlegać rozszerzeniu pamięci podręcznej planu, chyba że zostaną skopiowane dosłownie. Nawet różnice w przypadku i pustej przestrzeni mogą prowadzić do przechowywania wielu wersji tego samego planu (marnotrawstwo).
• Mogę sprawdzać i rozwiązywać problemy związane z zapytaniami bez dostępu do kodu źródłowego aplikacji lub uruchamiania drogich śladów, aby zobaczyć dokładnie, co robi aplikacja.
• Mogę również kontrolować (i wiedzieć z wyprzedzeniem), jakie zapytania może uruchamiać aplikacja, do jakich tabel ma dostęp iw jakim kontekście itp. Jeśli programiści piszą zapytania ad-hoc w swojej aplikacji, albo będą musieli przyjdź pociągnąć koszulę za każdym razem, gdy potrzebują dostępu do stołu, o którym nie wiedziałem lub którego nie mogłem przewidzieć, lub jeśli jestem mniej odpowiedzialny / zachwycony i / lub świadomy bezpieczeństwa, zamierzam to promować użytkownika do dbo, żeby przestali mnie denerwować. Zazwyczaj dzieje się tak, gdy programiści przewyższają DBA lub DBA są uparte. Ten ostatni punkt jest naszym złym i musimy być lepsi w dostarczaniu zapytań, których potrzebujesz.
• W powiązanej notatce zestaw procedur przechowywanych jest bardzo łatwym sposobem na inwentaryzację dokładnie, jakie zapytania mogą być uruchomione w moim systemie. Gdy tylko aplikacja może ominąć procedury i przesłać własne zapytania ad-hoc, aby je znaleźć, muszę uruchomić śledzenie obejmujące cały cykl biznesowy lub przeanalizować cały kod aplikacji (ponownie, że Mogę nie mieć dostępu do), aby znaleźć coś, co wygląda jak zapytanie. Możliwość zobaczenia listy procedur przechowywanych (i grep z jednego źródła sys.sql_modules, dla odniesień do określonych obiektów) znacznie ułatwia życie wszystkim.
• Mogę przejść na znacznie większe długości, aby zapobiec wstrzyknięciu SQL; nawet jeśli pobiorę dane wejściowe i wykonam je za pomocą dynamicznego SQL, mogę kontrolować wiele z tego, co jest dozwolone. Nie mam kontroli nad tym, co robi programista podczas konstruowania wbudowanych instrukcji SQL.
• Mogę zoptymalizować zapytanie (lub zapytania) bez dostępu do kodu źródłowego aplikacji, możliwości wprowadzania zmian, znajomości języka aplikacji, aby to robić skutecznie, uprawnień (nie wspominając o kłopotach) do ponownej kompilacji i ponownego wdrożenia aplikacja itp. Jest to szczególnie problematyczne, jeśli aplikacja jest rozpowszechniana.
• Czy mogę wymusić pewne ustawione opcje w ramach procedury składowanej, aby uniknąć sytuacji, w której poszczególne zapytania będą podlegały częściowej spowolnieniu w aplikacji, szybko w SSMS? problemy. Oznacza to, że w przypadku dwóch różnych aplikacji wywołujących zapytanie ad hoc, jedna mogła mieć SET ANSI_WARNINGS ON, a druga mogłaby SET ANSI_WARNINGS OFF, i każda z nich miałaby własną kopię planu. Plan, który otrzymują, zależy od używanych parametrów, statystyk itp. Przy pierwszym wywołaniu zapytania w każdym przypadku, co może prowadzić do różnych planów, a tym samym do bardzo różnych wyników.
• Mogę kontrolować rzeczy takie jak typy danych i sposób użycia parametrów, w przeciwieństwie do niektórych ORM - niektóre wcześniejsze wersje rzeczy, takie jak EF, sparametryzowałyby zapytanie na podstawie długości parametru, więc gdybym miał parametr N'Smith 'i inny N' Johnson „Dostałbym dwie różne wersje planu. Naprawili to. Naprawili to, ale co jeszcze jest zepsute?
• Mogę robić rzeczy, których ORM i inne „pomocne” frameworki i biblioteki nie są jeszcze w stanie obsłużyć.

To powiedziawszy, pytanie to może wywołać więcej religijnych argumentów niż debata techniczna. Jeśli zobaczymy, że tak się dzieje, prawdopodobnie to zamkniemy.

Chociaż szanuję zgłaszającego, pokornie nie zgadzam się z udzieloną odpowiedzią, a nie z „powodów religijnych”. Innymi słowy, uważam, że firma Microsoft nie zapewniła żadnego narzędzia, które zmniejszałoby potrzebę korzystania z procedur przechowywanych w wytycznych.

Wszelkie wskazówki udzielane deweloperowi, które faworyzują stosowanie zapytań SQL w postaci nieprzetworzonego tekstu, muszą być wypełnione wieloma zastrzeżeniami, tak więc uważam, że najrozsądniejszą radą jest znaczne zachęcanie do korzystania z procedur przechowywanych i zniechęcanie zespołów programistów do udziału w praktyce osadzania instrukcji SQL w kodzie lub przesyłania surowych, zwykłych tekstowych żądań SQL poza SQL SPROC (procedury składowane).

Myślę, że prosta odpowiedź na pytanie, dlaczego używać SPROC, jest taka, jak zakładał autor: SPROC są analizowane, optymalizowane i kompilowane. W związku z tym ich plany zapytań / wykonania są buforowane, ponieważ zapisano statyczną reprezentację zapytania, a Ty będziesz go różnicować tylko według parametrów, co nie jest prawdą w przypadku skopiowanych / wklejonych instrukcji SQL, które prawdopodobnie zmieniają się od strony do strony i komponentu / warstwy, i często są zmienne w zakresie, w jakim różne tabele, nawet nazwy baz danych, mogą być określane od wezwania do wywołania. Zezwolenie na ten rodzaj dynamicznego ad hocPrzesyłanie kodu SQL znacznie zmniejsza prawdopodobieństwo, że silnik DB ponownie użyje planu zapytań do instrukcji ad hoc, zgodnie z pewnymi bardzo surowymi zasadami. W tym miejscu dokonuję rozróżnienia między dynamicznymi zapytaniami ad hoc (w duchu postawionego pytania) a użyciem wydajnego Systemu SPROC sp_executesql.

Mówiąc dokładniej, istnieją następujące elementy:

• Szeregowe i równoległe plany zapytań, które nie zawierają kontekstu użytkownika i pozwalają na ponowne użycie przez silnik DB.
• Kontekst wykonania, który umożliwia ponowne wykorzystanie planu zapytań przez nowego użytkownika z różnymi parametrami danych.
• Pamięć podręczna procedur, o którą pyta silnik DB w celu uzyskania oczekiwanej wydajności.

Gdy ze strony internetowej wydawana jest instrukcja SQL, zwana „instrukcją ad hoc”, silnik szuka istniejącego planu wykonania, który obsłużyłby żądanie. Ponieważ jest to tekst przesłany przez użytkownika, zostanie przyjęty, przeanalizowany, skompilowany i wykonany, jeśli jest poprawny. W tej chwili otrzyma zerowy koszt zapytania. Koszt zapytania jest wykorzystywany, gdy silnik DB korzysta z algorytmu w celu ustalenia, które plany wykonania zostaną wykluczone z pamięci podręcznej.

Kwerendy ad hoc otrzymują domyślnie zerową wartość kosztu zapytania. Po kolejnym wykonaniu dokładnie tego samego tekstu zapytania ad hoc przez inny proces użytkownika (lub ten sam) bieżący koszt zapytania jest resetowany do pierwotnego kosztu kompilacji. Ponieważ nasz koszt kompilacji zapytań ad hoc wynosi zero, nie wróży to dobrze możliwości ponownego użycia. Oczywiście zero jest najmniej wartościowaną liczbą całkowitą, ale dlaczego miałaby być eksmitowana?

Gdy pojawią się presje pamięciowe, które będą występować, jeśli masz często używaną witrynę, silnik DB korzysta z algorytmu czyszczenia w celu ustalenia, w jaki sposób może odzyskać pamięć używaną przez pamięć podręczną procedury. Wykorzystuje bieżący koszt zapytania, aby zdecydować, które plany eksmisji. Jak można się domyślać, plany z zerowym kosztem są pierwszymi, które są eksmitowane z pamięci podręcznej, ponieważ zero zasadniczo oznacza „brak obecnych użytkowników lub odniesienia do tego planu”.

• Uwaga: Plany wykonania ad hoc - bieżący koszt jest zwiększany o każdy proces użytkownika o pierwotny koszt kompilacji planu. Jednak maksymalny koszt żadnego planu nie może przekraczać pierwotnego kosztu kompilacji ... w przypadku zapytań ad hoc ... zero. Tak więc zostanie „powiększony” o tę wartość… zero - co zasadniczo oznacza, że ​​pozostanie planem o najniższych kosztach.

Dlatego jest całkiem prawdopodobne, że taki plan zostanie najpierw eksmitowany, gdy pojawi się presja pamięci.

Jeśli więc masz serwer z dużą ilością pamięci „przekraczającą Twoje potrzeby”, problem może nie występować tak często, jak zajęty serwer, który ma tylko „wystarczającą” pamięć, aby obsłużyć swoje obciążenie. (Niestety, pojemność pamięci serwera i wykorzystanie są nieco subiektywne / względne, chociaż algorytm nie jest.)

Teraz, jeśli nie mam racji co do jednego lub więcej punktów, z pewnością jestem otwarty na korektę.

Na koniec autor napisał:

„Teraz mamy optymalizację na poziomie instrukcji, więc odpowiednio sparametryzowane zapytanie pochodzące z aplikacji może korzystać z tego samego planu wykonania, co zapytanie wbudowane w procedurę przechowywaną”.

Uważam, że autor odnosi się do opcji „optymalizuj pod kątem obciążeń ad hoc”.

Jeśli tak, ta opcja umożliwia dwuetapowy proces, który pozwala uniknąć natychmiastowego wysłania pełnego planu zapytań do pamięci podręcznej procedury. Wysyła tam tylko mniejszy kod zapytania. Jeśli dokładne wywołanie zapytania zostanie wysłane z powrotem do serwera, gdy kod pośredniczący zapytania nadal znajduje się w pamięci podręcznej procedury, wówczas pełny plan wykonania zapytania zostanie zapisany w pamięci podręcznej procedury. Oszczędza to pamięć, która podczas incydentów związanych z ciśnieniem pamięci może pozwolić algorytmowi eksmisji na eksmisję twojego kodu pośredniczącego rzadziej niż większy buforowany plan zapytań. Znowu zależy to od pamięci serwera i wykorzystania.

Musisz jednak włączyć tę opcję, ponieważ jest ona domyślnie wyłączona.

Na koniec chcę podkreślić, że często powodem, dla którego programiści osadzają SQL na stronach, komponentach i innych miejscach, jest to, że chcą być elastyczni i przesyłać dynamiczne zapytania SQL do silnika bazy danych. Dlatego w rzeczywistym przypadku użycia przesłanie tego samego tekstu, wywołanie połączenia, jest mało prawdopodobne, podobnie jak buforowanie / wydajność, której szukamy, przesyłając zapytania ad hoc do programu SQL Server.

Aby uzyskać dodatkowe informacje, zobacz:

https://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx
http://sqlmag.com/database-performance-tuning/don-t-fear-dynamic-sql

Cześć,
Henry

TLDR: Nie ma zauważalnej różnicy wydajności między nimi, o ile sparametryzowany jest Twój wbudowany sql.

Oto powód, dla którego powoli wycofałem procedury składowane:

• Prowadzimy środowisko aplikacji „beta” - środowisko równoległe do produkcji, które korzysta z produkcyjnej bazy danych. Ponieważ kod db jest na poziomie aplikacji, a zmiany struktury db są rzadkie, możemy pozwolić ludziom potwierdzać nowe funkcje poza kontrolą jakości i wykonywać wdrożenia poza oknem wdrożenia produkcyjnego, ale nadal zapewniać funkcje produkcyjne i poprawki niekrytyczne. Nie byłoby to możliwe, gdyby połowa kodu aplikacji znajdowała się w bazie danych.

• Ćwiczymy devops na poziomie bazy danych (ośmiornica + dacpac). Jednak chociaż warstwę biznesową i wyższą można zasadniczo oczyścić i zastąpić, a odzyskiwanie jest odwrotne, nie jest to prawdą w przypadku przyrostowych i potencjalnie destrukcyjnych zmian, które muszą przejść do baz danych. W związku z tym wolimy, aby nasze wdrożenia DB były lżejsze i rzadsze.

• Aby uniknąć prawie dokładnych kopii tego samego kodu dla opcjonalnych parametrów, często używamy wzorca „gdzie @var ma wartość null lub @ var = table.field”. Dzięki przechowywanemu procesowi możesz uzyskać ten sam plan wykonania, pomimo raczej różnych zamiarów, a zatem albo wystąpić problemy z wydajnością, albo wyeliminować plany buforowane za pomocą wskazówek dotyczących „ponownej kompilacji”. Jednak za pomocą prostego fragmentu kodu, który dołącza komentarz „podpis” na końcu sql, możemy wymusić różne plany na podstawie tego, które zmienne miały wartość null (nie należy interpretować tego jako innego planu dla wszystkich kombinacji zmiennych - tylko null vs Nie jest zerem).

• Mogę dokonać radykalnych zmian w wynikach z niewielkimi zmianami w locie do sql. Na przykład mogę mieć instrukcję zamykającą się za pomocą dwóch CTE, „Raw” i „ReportReady”. Nic nie mówi, że oba CTE muszą być użyte. Moja instrukcja sql może wtedy być:

  ...

  wybierz * z {(format)} ”

To pozwala mi używać dokładnie tej samej metody logiki biznesowej zarówno dla usprawnionego wywołania interfejsu API, jak i raportu, który musi być bardziej szczegółowy, aby nie powielać skomplikowanej logiki.

• kiedy masz regułę „tylko proces”, kończysz z mnóstwem redundancji w zdecydowanej większości twojego sql, co ostatecznie kończy się na CRUD - wiążesz wszystkie parametry, podajesz wszystkie te parametry w podpisie proc (i teraz jesteś w innym pliku w innym projekcie), mapujesz te proste parametry na ich kolumny. To stwarza dość rozłączne doświadczenie programistyczne.

Istnieją ważne powody, aby używać procs:

• Bezpieczeństwo - masz tutaj kolejną warstwę, którą aplikacja musi przejść. Jeśli konto usługi aplikacji nie może dotykać tabel, ale ma tylko uprawnienia do wykonywania na procesach, masz dodatkową ochronę. To nie oznacza, że ​​jest to dane, ponieważ ma koszt, ale jest taka możliwość.

• Ponowne użycie - Chociaż powiedziałbym, że ponowne użycie powinno w dużej mierze odbywać się w warstwie biznesowej, aby upewnić się, że nie omija się reguł biznesowych niezwiązanych z db, nadal mamy okazyjne, niskopoziomowe „narzędzia używane wszędzie”.

Istnieje kilka argumentów, które tak naprawdę nie obsługują procs lub można je łatwo złagodzić IMO:

• Ponowne użycie - wspomniałem o tym powyżej jako „plus”, ale chciałem również wspomnieć tutaj, że ponowne użycie powinno w dużej mierze mieć miejsce na poziomie biznesowym. Proces wstawiania rekordu nie powinien być uważany za „wielokrotnego użytku”, gdy warstwa biznesowa może również sprawdzać inne usługi inne niż db.

• Nadęty plan pamięci podręcznej - jedynym sposobem, w jaki będzie to stanowić problem, jest połączenie wartości zamiast parametryzacji. Fakt, że rzadko otrzymujesz więcej niż jeden plan na proces, często boli cię, gdy masz zapytanie „lub”

• Rozmiar instrukcji - dodatkowy KB instrukcji SQL w stosunku do nazwy proc zwykle będzie nieistotny w stosunku do zwracanych danych. Jeśli jest to w porządku dla Entities, jest dla mnie w porządku.

• Widzenie dokładnego zapytania - Ułatwienie znajdowania zapytań w kodzie jest tak proste, jak dodanie lokalizacji wywołującej jako komentarza do kodu. Tworzenie kodu, który można skopiować z kodu c # do ssms, jest tak proste, jak niektóre twórcze interpolacje i użycie komentarzy:

      //Usage /*{SSMSOnly_}*/Pure Sql To run in SSMS/*{_SSMSOnly}*/
      const string SSMSOnly_ = "*//*<SSMSOnly>/*";
      const string _SSMSOnly = "*/</SSMSOnly>";
      //Usage /*{NetOnly_}{InterpolationVariable}{_NetOnly}*/
      const string NetOnly_ = "*/";
      const string _NetOnly = "/*";

• Wstrzykiwanie SQL - sparametryzuj swoje zapytania. Gotowy. Można to faktycznie cofnąć, jeśli zamiast tego proc używa dynamicznego SQL.

• Obejście wdrożenia - Ćwiczymy devops również na poziomie bazy danych, więc nie jest to dla nas opcja.

• „Powolny w aplikacji, szybki w SSMS” - Jest to problem buforowania planu, który wpływa na obie strony. Ustawione opcje powodują jedynie skompilowanie nowego planu, który wydaje się rozwiązać problem zmiennych ONE SET OFF. To tylko odpowiada, dlaczego widzisz różne wyniki - same ustawione opcje NIE naprawiają problemu wąchania parametrów.

• Plany wykonania Inline SQL nie są buforowane - po prostu fałszywe. Instrukcja sparametryzowana, podobnie jak nazwa proc, jest szybko mieszana, a następnie ten hash wyszukuje plan. Jest w 100% taki sam.

• Żeby było jasne, mówię o surowym inline sql nie generowanym kodzie z ORM - używamy tylko Dapper, który w najlepszym razie jest mikro ORM.

https://weblogs.asp.net/fbouma/38178

/programming//a/15277/852208