Kiedy powinienem korzystać z procedur przechowywanych?

Jeśli mam wszystkie moje logiki biznesowej w kodzie i korzystania z Entity Framework, w jakich sytuacjach (jeśli w ogóle), że będę lepiej porusza jakąś logikę biznesową do procedury przechowywanej, zamiast trzymać to wszystko w kodzie?

Dla jasności mam na myśli w połączeniu z bieżącą konfiguracją (logika biznesowa w kodzie), a nie zamiast. Widziałem wiele podobnych pytań, które zadają zalety i wady posiadania całej logiki biznesowej w procedurach przechowywanych, ale nie znalazłem wiele na temat oszczędnego korzystania z procedur przechowywanych w logice przypadków na krawędzi, zachowując resztę logiki biznesowej W kodzie.

Jeśli to robi różnicę, używam MSSQL i Entity Framework.

Są to sytuacje, w których wcześniej korzystałem z procedur przechowywanych:

• Skomplikowany raport, którego uruchomienie zajęło kilka minut (była to strona w aplikacji internetowej). Odkryłem, że potrafię napisać SQL, który jest znacznie bardziej wydajny (zajmuje tylko kilka sekund), niż zapewnia LINQ.
• Aplikacja internetowa musiała czytać i zapisywać w kilku tabelach w osobnej bazie danych, która zawierała wiele innych poufnych informacji, które nie były istotne dla aplikacji. Zamiast dać mu dostęp do wszystkiego, użyłem procedury składowanej, która robi tylko to, co jest potrzebne i zwraca tylko ograniczone informacje. Aplikacja internetowa mogłaby wówczas uzyskać dostęp tylko do tej procedury składowanej, bez dostępu do żadnych tabel itp.

Inne posty, na które spojrzałem, zanim zadałem to pytanie:

• Procedury przechowywane to zła praktyka w jednej z największych na świecie firm konsultingowych w zakresie oprogramowania IT?
• Plusy i minusy posiadania całej logiki biznesowej w procedurach przechowywanych w aplikacji internetowej
• /programming/15142/what-are-the-pros-and-cons-to-keeping-sql-in-stored-procs-versus-code
• /dba/2450/what-are-the-arguments-against-or-for-putting-application-logic-in-the-database/2452#2452
• Kiedy nie używać ORM i preferować procedury składowane?

Masz już kilka idealnie dobrych scenariuszy.

Istnieje również wiele innych powodów. EF jest naprawdę dobry w CRUD i przy dość prostym raportowaniu. Czasami jednak EF nie jest idealnym narzędziem. Niektóre inne powody (scenariusze), które należy rozważyć użycie procedur przechowywanych w połączeniu z Entity Framework, obejmują:

• Masz złożone jednostki pracy, być może obejmujące wiele tabel, których nie można łatwo zawrzeć w transakcji za pomocą funkcji EF.
• Twoja baza danych nie działa dobrze z EF, ponieważ nie wykorzystuje deklaratywnej integralności referencyjnej (ograniczenia klucza obcego). Zazwyczaj jest to zły scenariusz, w którym można się znaleźć, ale czasami istnieją odpowiednie scenariusze, takie jak bazy danych używane dla procesów ETL.
• Musisz pracować z danymi, które przekraczają granice serwerów z połączonymi serwerami.
• Masz bardzo złożone scenariusze pobierania danych, w których SQL „bare metal” jest potrzebny w celu zapewnienia odpowiedniej wydajności. Na przykład masz złożone sprzężenia, które potrzebują wskazówek do zapytania, aby dobrze działać w konkretnej sytuacji.
• Twoja aplikacja nie ma pełnych uprawnień CRUD dla tabeli, ale można zezwolić na jej działanie w kontekście bezpieczeństwa, któremu ufa serwer (na przykład tożsamość aplikacji, a nie tożsamość użytkownika). Może się tak zdarzyć w sytuacjach, w których DBA ograniczają dostęp do tabeli do przechowywanych procesów tylko dlatego, że daje im bardziej szczegółową kontrolę nad tym, kto może co robić.

Jestem pewien, że jest ich znacznie więcej. Sposobem ustalenia najlepszej ścieżki naprzód w każdych szczególnych okolicznościach jest użycie zdrowego rozsądku i skupienie się na głównym celu, jakim jest napisanie wysokiej jakości, łatwego w utrzymaniu kodu. Wybierz narzędzia, które dadzą ci ten wynik w każdym przypadku.

Być może ma to sens, aby odwrócić pytanie i znaleźć przypadki użycia, w których mogłyby zrobić tylko procedury składowane, co chcesz osiągnąć. Być może istnieją naprawdę przypadki użycia, w których wyróżniają się procedury przechowywane.

Jeśli to robi różnicę, używam MSSQL i Entity Framework.

Moja wiedza na temat EF jest ograniczona, ale o ile widzę, EF jest ( tylko ) ORM jak każda inna; i na szczęście jest w stanie używać surowego SQL .

Jeśli wezmę twoje dwa główne punkty:

Skomplikowany raport, którego uruchomienie zajęło kilka minut (była to strona w aplikacji internetowej). Odkryłem, że potrafię napisać SQL, który jest znacznie bardziej wydajny (zajmuje tylko kilka sekund), niż zapewnia LINQ.

Podczas sporządzania raportu LINQ / EF nie spełniał oczekiwań. I jak zauważyłeś, było SQLznacznie szybsze niż używanie ORM. Ale czy to przemawia na korzyść procedur przechowywanych, czy tylko przeciwko wykorzystaniu ORM do wszystkiego?

Twój problem można oczywiście rozwiązać za pomocą SQL . Jeśli to zapytanie zostało zapisane, a wersja kontrolowana w twojej bazie kodu - zgodnie z twoim przykładem - przynajmniej nie ma różnicy .

Aplikacja internetowa musiała czytać i zapisywać w kilku tabelach w osobnej bazie danych, która zawierała wiele innych poufnych informacji, które nie były istotne dla aplikacji. Zamiast dać mu dostęp do wszystkiego, użyłem procedury składowanej, która robi tylko to, co jest potrzebne i zwraca tylko ograniczone informacje. Aplikacja internetowa mogłaby wówczas uzyskać dostęp tylko do tej procedury składowanej, bez dostępu do żadnych tabel itp.

To samo tutaj: prosty ciąg połączenia i AKTUALIZACJA, a Twój problem został zakończony. Ten problem można rozwiązać nawet za pomocą ORM : wystarczy użyć usługi sieci Web przed drugą bazą danych i uzyskuje się taką samą kompartmentalizację / izolację .

Więc nie ma tu nic do zobaczenia.

Przyglądając się niektórym punktom inni:

Masz złożone jednostki pracy, być może obejmujące wiele tabel, których nie można łatwo zawrzeć w transakcji za pomocą funkcji EF.

Ale SQLmogę to zrobić. Nie ma tu żadnej magii .

Twoja baza danych nie działa dobrze z EF

Ponownie: w razie potrzeby użyj EF .

Musisz pracować z danymi, które przekraczają granice serwerów z połączonymi serwerami

Nie rozumiem, w jaki sposób procedury przechowywane pomagają. Nie widzę więc przewagi procedur przechowywanych; ale może ktoś rzuci na to trochę światła.

Masz bardzo złożone scenariusze pobierania danych, w których SQL „bare metal” jest potrzebny w celu zapewnienia odpowiedniej wydajności

Ponownie: „Granice EF ”.

Twoja aplikacja nie ma pełnych uprawnień CRUD dla tabeli, ale można zezwolić na jej działanie w kontekście bezpieczeństwa, któremu ufa serwer

W porządku. Idę z może .

Do tej pory tylko połowa punktów była na korzyść procedur przechowywanych.

Być może istnieją względy wydajności, które przemawiają za procedurami przechowywanymi.

1) Zaletą przechowywania zapytań jest proste wywołanie procedury składowanej, która uwzględnia złożoność. Ponieważ narzędzie do planowania zapytań zna zapytanie, „łatwiej” je zoptymalizować. Ale zapisy są z obecnym zaawansowanym planerem zapytań _minimal.

Co więcej, nawet jeśli korzystanie z zapytań ad hoc wiąże się z niewielkimi kosztami , jeśli dane są dobrze ustrukturyzowane i starannie indeksowane, baza danych jest jedynie wąskim gardłem Twojej aplikacji. Więc nawet jeśli istnieje niewielka delta, można ją pominąć, biorąc pod uwagę inne czynniki.

2) Niemniej jednak argumentowano za przechowywaniem złożonych zapytań w bazie danych. Należy wziąć pod uwagę dwie rzeczy:

a) złożone zapytania masowo wykorzystują infrastrukturę DB, co wydłuża czas odpowiedzi dla każdego innego zapytania. Nie można równolegle uruchamiać wielu kosztownych zapytań . Nie mówi to ani o procedurach przechowywanych pro, ani przeciwnie , ale o złożone zapytania.

b) Jeśli zapytanie i tak wymaga czasu, dlaczego zawracać sobie głowę małą szybkością wygrywa procedurę przechowywaną.

tl; dr

Nic nie przemawia wprost przeciwko procedurom przechowywanym. Więc korzystanie z procedur przechowywanych jest w porządku - jeśli cię uszczęśliwia.

Ale z drugiej strony: nie mogłem sobie wyobrazić właściwego przypadku użycia, który mówi jednoznacznie za.

Kiedy powinienem korzystać z procedur przechowywanych?

Prawidłowa odpowiedź to: kiedy tylko chcesz . Ale są też inne opcje.

W konkretnym przypadku, ponieważ używasz struktury encji, należy rozważyć użycie procedur przechowywanych, gdy struktura encji nie spełnia wymagań lub obaw.

Struktura encji wykonuje przyzwoitą pracę, a wydajność będzie bliska lub równa z wykorzystaniem procedur przechowywanych. Wybrałeś strukturę encji, ponieważ nie chciałeś zajmować się SQL i pozwolisz frameworkowi wygenerować SQL dla ciebie.

Ale może się zdarzyć, że ramy encji są niewystarczające i nie mogą zaspokoić twoich potrzeb. W takim przypadku należy ręcznie napisać SQL przy użyciu procedury składowanej lub sparametryzowanego zapytania, a następnie użyć struktury encji do bezpośredniego wywołania tej procedury przechowywanej / instrukcji SQL.

Tak więc nie przeniósłbym niczego do procedury składowanej, chyba że używany framework nie może spełnić tego wymagania.

Myślę, że najgorsze, co może się zdarzyć, to to, że decyduje się na użycie frameworku encji, a następnie zapisuje wszystkie procedury składowane. Teraz jedna ma dodatkową warstwę, która nie dodaje żadnej wartości.

Posiadanie potrzeby technicznej nie jest najbardziej prawdopodobnym wyborem. Programiści preferują swoje narzędzia i zwykle bardziej dostosowują się do rozwiązywania swoich problemów. Wyjątkiem jest umieszczenie logiki w sproc. Należy wziąć pod uwagę dług techniczny i przyszłych deweloperów, którzy muszą poświęcić czas na pracę z wyjątkiem. W twoim RDBMS może wystąpić wzrost wydajności, który jest łatwiejszy do zaimplementowania w sprocu, a może nawet w innym obiekcie db, takim jak widok indeksowany.

Będą chwile, kiedy będziesz potrzebować danych z bazy danych i po prostu nie będziesz mógł ich pobrać z kodu aplikacji. W wielu dużych firmach / przedsiębiorstwach potrzeby biznesowe mogą przekraczać możliwości działu IT. Firmy są kupowane i sprzedawane, a ich aplikacje przychodzą i odchodzą z nimi. Agencje regulacyjne i banki nie dbają o to, czy nie możesz zbudować wysoce skalowalnej i pięknie zakodowanej aplikacji. Mogą utrudniać życie firmie, więc musisz to zrobić.

Natknąłem się na sytuacje, w których aplikacje innych firm lub narzędzia do pisania raportów nie pozwalają uzyskać kodu. Bez otwartego kodu źródłowego, bez API, bez interfejsu sieciowego i bez usług. Jedyne, co mają, to własne narzędzie do pisania raportów, które działa tylko z obiektami bazy danych: tabelami, widokami, sprocami, funkcjami zdefiniowanymi przez użytkownika, itp. Logikę umieszczasz gdziekolwiek możesz.

Jeśli masz DBA, który jest bardzo dobry w pisaniu procedur przechowywanych, możesz wykorzystać ten talent w niektórych sytuacjach. Możesz zainicjować tworzenie kopii zapasowej z aplikacji, ponieważ zamierzasz dokonać poważnej zmiany danych, więc dlaczego nie wykorzystać tego, czego używa dba?

Niektóre żądania ad hoc są po prostu łatwiejsze do napisania proc, dopóki nie uzyskasz wszystkich funkcji w swojej aplikacji. Nienawidzimy tego. Odpychamy się, ale program musi trwać.

Zdecydowanie odradzam umieszczanie logiki biznesowej w procedurze przechowywanej. Może jednak zaistnieć przypadek (wymienione dwa dobre przykłady), w którym lepiej jest umieścić logikę dostępu do danych .

Ogólnie rzecz biorąc, jeśli odczuwasz pokusę używania SP, jest to znak (zapach kodu) wskazujący, że Twój model danych nie jest dobrze dostosowany do jego potrzeb.

Edycja: Kiedy programiści pytają mnie o logikę biznesową / dostępu do danych, mówię, że logika biznesowa dotyczy tego, jak dane zachowują się i wchodzą w interakcje, podczas gdy logika dostępu do danych dotyczy tego, jak dane są przechowywane i pobierane.

Na przykład: w modelu domeny możesz mieć byty „Student” i „Nauczyciel”, oba pochodzące od „Osoba”. (Nie mówię, że jest to preferowane, tylko przykład). Można to przechowywać w relacyjnych bazach danych w postaci jednej, dwóch lub trzech tabel. Wybór zależy od liczby udostępnianych właściwości oraz wymagań dotyczących wydajności odczytu / zapisu.

W logice biznesowej nie powinno mieć znaczenia, w jaki sposób te podmioty są przechowywane. (lub jeśli w ogóle znajdują się w RDB). Logika dostępu do danych polega na tym, jak są fizycznie przechowywane.

Tak więc w odniesieniu do pierwotnego pytania: jeśli procedura przechowywana sprawia, że ​​przechowywanie i odzyskiwanie danych jest bardziej wydajne (lub bardziej niezawodne), masz przypadek. Jeśli procedura składowana służy jedynie narzuceniu zasady, którą obowiązuję niezależnie od sposobu przechowywania danych, unikaj jej. Dzięki temu kod jest ściślej sprzężony z bazą danych.

Myślę, że są tutaj trzy problemy.

1. Logika biznesowa w SQL jest zła. Nawet jeśli działa szybciej indywidualnie, nie skaluje się.

2. Tradycyjnie SPROC powinny być zawsze używane dla wszystkich danych jako warstwa abstrakcji. Umożliwienie DBA wprowadzenia zmian wydajności lub innych warstw danych bez wpływu na konsumujące aplikacje.

3. EF nie gra dobrze z sprocami. Utracisz wiele „dobrych” funkcji i wzrost wydajności, odchodząc od dynamicznego generowania zapytań Linq.

Tak więc moja ogólna rada byłaby taka

• Użyj SPROC dla wszystkich zapytań SQL,

• Nie umieszczaj w nich logiki biznesowej ani żadnych pętli,

• Nie używaj EF.