Коли потрібно використовувати збережені процедури?

Якщо у мене є всі мої бізнес-логіки в коді і використовувати Entity Framework, в яких ситуаціях (якщо такі є) я б краще перемістити деяку бізнес-логіку до збереженої процедури, замість того, щоб зберігати все в коді ?

Щоб бути зрозумілим, я маю на увазі в поєднанні з поточною установкою (бізнес-логіка в коді), а не замість. Я бачив ряд подібних питань, які вимагають переваги та мінуси всієї бізнес-логіки в збережених процедурах, але я не знайшов багато чого щодо використання збережених процедур економно для логіки випадку краю, зберігаючи решту бізнес-логіки в коді.

Якщо це робить різницю, я використовую MSSQL і Entity Framework.


Такі ситуації, коли раніше я використовував збережені процедури:

  • Складний звіт, який запускав хвилини (ця сторінка була веб-програмою). Я знайшов, що я міг би писати SQL, який був набагато ефективнішим (лише за секунди до запуску), ніж те, що надав LINQ.
  • Для читання та запису до декількох таблиць у окремій базі даних, яка містила багато іншої конфіденційної інформації, яка не має відношення до програми, потрібно веб-додаток. Замість того, щоб надати йому доступ до всього, я використав збережену процедуру, яка лише робить те, що потрібно, і повертає лише обмежену інформацію. Після цього веб-додаток може отримати доступ до цієї збереженої процедури, без доступу до будь-яких таблиць і т.д.

Інші повідомлення, які я розглянув перед тим, як задати це питання:

14
Обидві ваші ситуації є цілком законними причинами для написання збережених процедур. Ви запитуєте, чому вони є законними?
додано Автор sgwill, джерело
Ви зробите виняток і напишіть збережену процедуру, коли ви визначите, що вона вирішує проблему краще, ніж інші доступні вам методи, що саме ви і зробили.
додано Автор sgwill, джерело
@RobertHarvey Я переконався, що вони є законними і шукають інші сценарії або причини, чому ви могли б зробити виняток і писати збережену процедуру, а не зберігати її в коді.
додано Автор Jason Bristol, джерело

6 Відповіді

У вас вже є кілька чудових сценаріїв.

Є також багато інших причин. EF є дійсно хорошим у CRUD та при досить прямому звітуванні. Іноді, хоча, EF не є ідеальним інструментом. Деякі інші причини (сценарії) для розгляду використання збережених процедур у поєднанні з Entity Framework буде включати:

  • У вас є складні одиниці роботи, можливо, пов'язані з багатьма таблицями, які неможливо легко загорнути в транзакцію, використовуючи функції EF.
  • Ваша база даних не працює добре з EF, оскільки вона не використовує переваги декларативної цілісності посилань (обмеження зовнішнього ключа). Зазвичай це поганий сценарій, щоб знайти себе, але іноді існують відповідні сценарії, такі як бази даних, що використовуються для процесів ETL.
  • Необхідно працювати з даними, які перетинають межі сервера з пов'язаними серверами.
  • У вас є дуже складні сценарії вилучення даних, де для забезпечення адекватної продуктивності необхідний "голий метал" SQL. Наприклад, у вас є складні об'єднання, які потребують підказок запиту, щоб добре працювати у вашій конкретній ситуації.
  • Ваша програма не має повних дозволів CRUD для таблиці, але вашій програмі може бути дозволено працювати в контексті безпеки, якому довіряє ваш сервер (наприклад, ідентифікатор програми, а не ідентифікатор користувача). Це може виникнути в ситуаціях, коли адміністратори баз даних обмежують доступ до таблиць до збережених процесів лише тому, що він надає їм більш детальний контроль над тим, хто може робити те, що.

Я впевнений, що є багато інших. Спосіб визначення кращого шляху вперед в будь-якій конкретній обставині полягає у використанні деякого здорового глузду і орієнтації на першочергову мету, яка повинна полягати в тому, щоб писати якісний, легко підтримуваний код. Виберіть інструмент (и), який дасть вам цей результат у кожному випадку.

8
додано
Завдяки Джоел, ви згадали кілька інших сценаріїв, про які я не думав.
додано Автор Jason Bristol, джерело

Я думаю, що тут є три питання.

  1. Бізнес-логіка в SQL є поганою. Навіть якщо він працює швидше індивідуально, він не масштабується.

  2. Традиційно SPROC повинні завжди використовуватися для доступу до даних як шар абстракції. Увімкнення DBA для внесення змін у продуктивність або інші дані, не впливаючи на споживчі програми.

  3. EF не грає добре з sprocs. Ви втратите багато "хороших" функцій і підвищення продуктивності, відключившись від генерації динамічних запитів у Linq.

Так що моя загальна порада була б

  • Використовуйте SPROC для всіх запитів SQL,

  • Не вкладайте в них бізнес-логіку або будь-які петлі

  • Не використовуйте EF.

4
додано
@Ewan: Ні, не для всіх дзвінків db. Тільки ті, які вимагають налаштування продуктивності/спеціального SQL. Тільки 20 відсотків (як правило, це менше, ніж на 5-10%). Немає "всіх збережених процедур, весь час" найкращої практики.
додано Автор sgwill, джерело
Це погана порада, в цілому. Існує багато варіантів доступу до даних, які мають переваги над ручним кодуванням збережених процедур і виконують так само добре. Ви можете запускати sprocs безпосередньо з EF; фактично, ви можете запустити будь-який sql-запит безпосередньо з EF. Деяка бізнес-логіка працює краще на сервері баз даних, тому ви не можете категорично стверджувати, що вона заборонена. Об'єктні реляційні маппери є інструментом 80%; вони ніколи не повинні були повністю замінити ваші процеси доступу до даних. Використовуйте збережені процедури для того, для чого вони призначені для використання: 20%, що ORM не працює добре.
додано Автор sgwill, джерело
EF все одно відобразить об'єкт для вас. Вам просто потрібно знати, як називати його належним чином.
додано Автор sgwill, джерело
Я бачу. Але тоді точка # 1 є чисто про компроміс між двома проблемами продуктивності. Але виникатимуть випадки, коли рішення SQL є чіткою перемогою з точки зору продуктивності, тому я не бачу, як його можна вважати категорично "поганим" на цій основі.
додано Автор mınxomaτ, джерело
це приклад одного конкретного сценарію, де SQL може бути поганим дизайном через масштабованість. Але навіть тоді не зрозуміло: це залежить від очікуваної кількості користувачів, відносних витрат на виконання розрахунку в базі даних в порівнянні з зовнішніми і так далі. Звичайно, ви правильні в деяких випадках, я просто не думаю, що це універсальне правило.
додано Автор mınxomaτ, джерело
@robert тільки якщо повернуті імена col відповідають, звичайно вам доведеться трохи налаштувати відображення
додано Автор Ewan, джерело
@Robert ви не знаєте заздалегідь, які SQL заяви будуть потрібні налаштування. Sprocs дають вам той шар абстракції, але ви повинні використовувати їх для всього, щоб уникнути необхідності змінити код, коли sql потребує налаштування
додано Автор Ewan, джерело
Оскільки всі SQL виконуються на сервері db, ви можете масштабувати лише за допомогою додаткових dbs, а не додаткових обчислювальних ящиків.
додано Автор Ewan, джерело
скажімо, у вас є веб-сервіс, який виконує розрахунок. Ви можете зробити це в коді або в sql. сказати, що sql швидше в голові до голови тест одного розрахунку. Але коли послуга вичерпана процесором його дуже дешево і легко додати другий, третій, четвертий .. вікно запуску веб-сервісу, але важко і дорого додати другу репліковану базу даних
додано Автор Ewan, джерело
@ dan1111 я можу відповісти тільки з мого досвіду, який є багато переміщення коду з sql для поліпшення scalablity
додано Автор Ewan, джерело
@robert ви можете, але при цьому ви втрачаєте переваги EF, ORM. Якщо ви стежите за найкращою практикою і використовуєте sprocs для всіх викликів db. Ви також можете не використовувати EF
додано Автор Ewan, джерело
@amy якщо ви використовуєте sprocs з EF, то ви втрачаєте об'єкти автоматичного відображення в tabels і linq -> sql і т.д.
додано Автор Ewan, джерело
Будь ласка, можете пояснити пункт 3? Ви, здається, припускаєте, що використання EF відходить від генерації динамічних запитів LINQ, але я використовую EF і LINQ, тому я трохи заплутався. Чи я не зрозумів вашу точку зору?
додано Автор Jason Bristol, джерело

Можливо, має сенс повернути питання навколо і знайти випадки, де тільки збережені процедури могли б зробити те, що ви хочете досягти. Можливо, є справжні випадки використання, де збережені процедури виділяються.

Якщо це має значення, я використовую MSSQL і Entity Framework.

Мої знання про EF обмежені, але, наскільки я бачу, EF є ( просто ) ORM, як і будь-який інший; і на щастя здатний використовувати raw SQL .

Якщо я візьму вас до двох основних моментів:

Складний звіт, який запускав хвилини (це сторінка веб-програми). Я знайшов, що я міг би писати SQL, який був набагато ефективнішим (лише за секунди до запуску), ніж те, що надав LINQ

LINQ/EF was falling short, when doing a report. And as you noticed, was SQL way faster, than using an ORM. But speaks this in favour of stored procedures or only against using the ORM for everything?

Ваша проблема, очевидно, може бути вирішена за допомогою SQL . Якщо цей запит був збережений, а версія, контрольована вашою кодовою базою, робить - відповідно до вашого прикладу - принаймні ніякої різниці .

Веб-додаток, необхідне для читання та запису до декількох таблиць в окремій базі даних, яка містить багато іншої конфіденційної інформації, яка не має відношення до програми. Замість того, щоб надати йому доступ до всього, я використав збережену процедуру, яка лише робить те, що потрібно, і повертає лише обмежену інформацію. Тоді веб-додаток може отримати доступ до цієї збереженої процедури, без доступу до будь-яких таблиць і т.д.

Те ж саме: простий рядок з'єднання і і UPDATE і ваша проблема виконана. Цю проблему можна вирішити навіть з ORM : просто використовує веб-службу перед іншою БД і досягається та ж compartementalization / ізоляція.

Так що тут нічого не бачити.

Дивлячись на деякі інші моменти:

У вас є складні одиниці роботи, можливо, пов'язані з багатьма таблицями, які неможливо легко загорнути в транзакцію, використовуючи функції EF.

Але SQL може це зробити. Тут немає магії .

Ваша база даних не грає добре з EF

Знову: використовуйте EF , коли це необхідно.

Необхідно працювати з даними, які перетинають межі сервера з пов'язаними серверами

Я не бачу, як допомагають збережені процедури. Тому я не можу побачити перевагу збережених процедур; але, можливо, хтось проливає світло на це.

У вас є дуже складні сценарії вилучення даних, де для забезпечення адекватної продуктивності необхідний "голий металевий" SQL

Знову: "Межі EF ".

Ваша програма не має повних дозволів CRUD на таблицю, але вашій програмі може бути дозволено працювати в контексті безпеки, якому ваш сервер довіряє

Гаразд. Я йду з можливо .

Поки тільки половина була зроблена на користь збережених процедур.


Можливо, існують міркування продуктивності, які говорять на користь збережених процедур.

1) Зберігання запитів має перевагу простого виклику до збереженої процедури, яка інкапсулює складність. Оскільки запитувальний верстат знає запит, його "легше" оптимізувати. Але збереження відбуваються з поточним складним запитувальником _minimal.

Крім того, навіть якщо існує незначна вартість за допомогою спеціальних запитів, якщо ваші дані добре структуровані та ретельно індексовані, база даних - це лише вузьке місце програми. Отже, навіть якщо є невелика дельта, вона нехтується з урахуванням інших факторів.

2) Незважаючи на це, аргументовано зберігати складні запити в БД. Є дві речі:

а) складні запити змушують масово використовувати інфраструктуру БД, що збільшує час для всіх інших запитів. Ви не можете запускати багато дорогих запитів паралельно . Це не говорить ні про про , ні про проти збережені процедури, а про складні запити.

b) Якщо запит вимагає часу, то заважайте невеликим виграшам швидкості збереженої процедури.

tl; dr

Ніщо не говорить безпосередньо проти збережених процедур. Таким чином, використання збережених процедур - це нормально - якщо це робить вас щасливими.

Але з іншого боку: я не міг уявити собі відповідний випадок використання , який говорить однозначно про.

Коли потрібно використовувати збережені процедури?

Правильна відповідь: Кожного разу, коли вам подобається . Але є й інші варіанти.

2
додано

Технічна потреба не є найбільш імовірним вибором. Програмісти віддають перевагу своїм інструментам і, як правило, більш адаптовані до вирішення своїх проблем з ними. Введення логіки в sproc буде винятком. Технічний борг і майбутні розробники, які повинні взяти час для роботи з виключенням, повинні бути розглянуті. Там може бути підвищення продуктивності у вашій конкретній СУБД, що просто простіше реалізувати в sproc або може бути навіть інший об'єкт db, як індексований вигляд.

Будуть випадки, коли вам потрібні дані з бази даних, і ви просто не можете отримати їх з коду програми. У багатьох ситуаціях великих компаній/підприємств потреби бізнесу можуть перевищувати охоплення ІТ-відділу. Компанії купуються і продаються, а їхні програми приходять і йдуть з ними. Регулюючі установи та банки не дбають, якщо ви не можете створити свій високо масштабований і красиво закодований додаток. Вони можуть зробити життя важким для бізнесу, тому вам доведеться це зробити.

Я зіткнувся з ситуаціями, коли програми сторонніх виробників або засоби написання звітів не дозволяють отримати код. Немає відкритого вихідного коду, жодного API, веб-інтерфейсу та послуг. Єдине, що вони мають - це власний спеціальний інструмент для написання звітів, який працює тільки з об'єктами бази даних: таблицями, переглядами, sprocs, призначеними для користувача функціями тощо. Ви розміщуєте логіку в будь-якому місці.

Якщо у вас є DBA, що дуже добре вміє писати збережені процедури, ви можете скористатися цим талантом у деяких ситуаціях. Можливо, ви захочете створити резервну копію з програми, оскільки ви збираєтеся змінити дані, так чому б не використовувати те, що використовує ваш dba?

Деякі спеціальні запити простіше писати процес, поки ви не зможете отримати всі функції у вашому додатку. Ми його ненавидимо. Ми відсуваємо назад, але шоу має продовжуватися.

0
додано

Для вашого конкретного випадку, оскільки ви використовуєте структуру суб'єкта господарювання, слід розглянути можливість використання збережених процедур, коли структура організації не відповідає вимогам або занепокоєнням.

Структура суб'єкта господарювання робить гідну роботу, а продуктивність буде близькою або рівною використанню збережених процедур. Ви вибрали фреймворк сутності, тому що ви не хотіли бути зацікавленими в SQL і дозволить структурі створити SQL для вас.

Але може існувати крайовий випадок, коли структура суб'єкта господарювання не відповідає вашим потребам. У цьому випадку можна було б записати SQL вручну, використовуючи збережену процедуру або параметризований запит, а потім використати фреймворк сутності, щоб викликати безпосередньо оператор/SQL.

Отже, я б не перемістив нічого до збереженої процедури, якщо рамки, які використовуються, не можуть відповідати цій вимозі.

Я думаю, що найгірше, що може статися, це те, що людина вибирає для використання рамки об'єктної сутності, а потім записує всі збережені процедури. Тепер у вас є додатковий шар, який не додає ніякого значення.

0
додано
100% згодні з останнім абзацом
додано Автор Ewan, джерело

Я дуже радив би не розміщувати логіку business у збереженій процедурі. Однак може виникнути випадок (ви перерахували два прекрасних прикладу), де бажано розмістити логіку доступу до даних .

Взагалі кажучи, якщо ви відчуваєте спокусу використовувати SP, це знак (запах коду), який натякає, що ваша модель даних не дуже підходить до її потреб.

Редагувати: Коли розробники запитують мене про бізнес/логіку доступу до даних, я кажу, що бізнес-логіка стосується того, як дані поводяться і взаємодіють, а логіка доступу до даних - про те, як дані зберігаються і витягуються.

Наприклад: у вашій моделі домену ви можете мати об'єкти "Студент" і "Вчитель", обидва отримані з "Особи". (Не кажучи, що це краще, тільки приклад). Це може бути збережено в реляційних базах даних як одна, дві або три таблиці. Вибір залежить від того, скільки властивостей вони поділяють, і від вимог до читання/запису.

У бізнес-логіці не має значення, як ці об'єкти зберігаються. (або вони взагалі перебувають у РДБ). Логіка доступу до даних - це те, як вони фізично зберігаються.

Отже, стосовно початкового питання: якщо збережена процедура робить зберігання та отримання даних більш ефективною (або більш надійною), ви маєте справу. Якщо збережена процедура призначена лише для накладання правила, що i дійсно незалежно від того, як зберігаються дані, уникайте цього. Це робить ваш код більш тісно пов'язаний з базою даних.

0
додано
Ні, якщо їх метою є доступ до даних.
додано Автор sgwill, джерело
@AmyBarrett: Ну, іноді ви пишете спеціальні методи в шарі доступу до даних, тому це не завжди CRUD.
додано Автор sgwill, джерело
додано Автор sgwill, джерело
Я б сказав, що логіка доступу до даних - скажімо "отримати всі рядки з кількістю рядків підменю менше 5" бізнес-логіка ", якщо col x> 5 потім col y = великий клієнт"
додано Автор Ewan, джерело
Не знаю, чому я був занижений. Додано питання щодо редагування про Amys
додано Автор BlueMoon93, джерело
Спасибі за пояснення і приклад, я вкладав цю логіку в бізнес-логіку.
додано Автор Jason Bristol, джерело
@RobertHarvey Чи не будуть ці користувацькі методи належать до рівня бізнес-логіки?
додано Автор Jason Bristol, джерело
@RobertHarvey Дякуємо за посилання. Чи є рівень доступу до даних таким же, як логіка доступу до даних? Я запитую, як, здається, не існує багато фактичної логіки, залученої до рівня доступу до даних - просто простих операцій CRUD.
додано Автор Jason Bristol, джерело
Як би Ви визначили логіку бізнесу та доступ до даних ?
додано Автор Jason Bristol, джерело