Каковы плюсы и минусы сохранения SQL в сохраненных Procs по сравнению с кодом

Я не фанат хранимых процедур

Хранимые процедуры являются MORE ремонтопригодными, потому что: * Вам не нужно перекомпилировать ваше приложение C# всякий раз, когда вы хотите изменить некоторые SQL

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

• В конечном итоге вы повторно используете код SQL.

Языки программирования, включая C#, имеют эту удивительную вещь, называемую функцией. Это означает, что вы можете вызвать один и тот же блок кода из нескольких мест! Удивительно! Затем вы можете поместить многоразовый код SQL в один из них, или если вы хотите получить действительно высокотехнологичный, вы можете использовать библиотеку, которая делает это за вас. Я полагаю, что они называются объектно-реляционными картографами и довольно распространены в наши дни.

Повторение кода-это худшее, что вы можете сделать, когда пытаетесь создать поддерживаемое приложение!

Согласен, именно поэтому storedprocs-это плохо. Гораздо проще рефакторировать и разложить (разбить на более мелкие части) код на функции, чем SQL на... блоки SQL?

У вас есть 4 веб-сервера и куча приложений windows, которые используют один и тот же код SQL теперь вы поняли, что есть небольшая проблема с кодом SQl, так что вы скорее...... измените proc в 1 месте или отправьте код на все веб-серверы, переустановите все настольные приложения(clickonce может помочь) на всех полях windows

Почему ваши приложения windows подключаются непосредственно к центральной базе данных? Это похоже на дыру в безопасности HUGE прямо там, и узкое место, поскольку оно исключает кэширование на стороне сервера. Разве они не должны подключаться через веб-службу или аналогично вашим веб-серверам?

Итак, нажмите 1 новый sproc или 4 новых веб-сервера?

В этом случае проще нажать один новый sproc, но по моему опыту, 95% из 'pushed changes' влияют на код, а не на базу данных. Если вы отправляете 20 вещей на веб-серверы в этом месяце и 1 на базу данных, вы вряд ли потеряете много, если вместо этого вы отправите 21 вещь на веб-серверы и ноль на базу данных.

Более легко просматривать код.

Вы можете объяснить, как это произошло? Я этого не понимаю. Особенно учитывая, что sprocs, вероятно, не находятся в системе управления версиями и поэтому не могут быть доступны через веб-браузеры SCM и так далее.

Больше минусов:

Storedprocs живут в базе данных, которая представляется внешнему миру в виде черного ящика. Простые вещи, такие как желание поместить их в систему управления версиями, становятся кошмаром.

Существует также проблема чистого усилия. Возможно, имеет смысл разбить все на миллион уровней , если вы пытаетесь оправдать перед своим CEO, почему им просто стоило 7 миллионов долларов построить некоторые форумы, но в противном случае создание storedproc для каждой мелочи-это просто дополнительная работа без пользы.

В настоящее время это обсуждается на нескольких других темах здесь. Я последовательный сторонник хранимых процедур, хотя некоторые хорошие аргументы в пользу Linq-Sql уже представлены.

Встраивание запросов в код тесно связывает вас с моделью данных. Хранимые процедуры-это хорошая форма контрактного программирования, означающая, что a DBA имеет свободу изменять модель данных и код в процедуре, пока сохраняется контракт, представленный входами и выходами хранимой процедуры.

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

[Edit] вот еще одна текущая дискуссия

По моему мнению, вы не можете голосовать за " да " или " нет " по этому вопросу. Это полностью зависит от дизайна вашего приложения.

Я полностью голосую против использования SPs в 3-уровневой среде, где у вас есть сервер приложений спереди. В такой среде сервер приложений используется для выполнения бизнес-логики. Если вы дополнительно используете SPs, вы начинаете распределять свою реализацию бизнес-логики по всей системе, и становится очень неясно, кто за что отвечает. В конечном итоге вы получите сервер приложений, который в основном не будет делать ничего, кроме следующего:

(Pseudocode)

Function createOrder(Order yourOrder) 
Begin
  Call SP_createOrder(yourOrder)
End

Так что в конце концов у вас есть свой средний уровень, работающий на этом очень крутом кластере из 4 серверов, каждый из которых оснащен 16 CPUs, и он фактически ничего не будет делать! Какое расточительство!

Если у вас есть толстый gui-клиент, который напрямую подключается к вашему DB или, возможно, даже больше приложений, это совсем другая история. В этой ситуации SPs может служить своего рода псевдо-средним уровнем, который отделяет ваше приложение от модели данных и обеспечивает Управляемый доступ.

Преимущества Для в код:

• Проще в обслуживании - не нужно запускать скрипт SQL для обновления запросов
• Проще портировать на другой DB-нет procs для порта

Вообще-то, я думаю, что у тебя все наоборот. IMHO, SQL в коде-это боль для поддержания, потому что:

• в конечном итоге вы повторяете себя в связанных блоках кода
• SQL не поддерживается как язык во многих IDE-х, поэтому у вас есть только ряд строк без ошибок, выполняющих задачи для вас
• изменения в типе данных, имени таблицы или ограничении гораздо более распространены, чем замена всей базы данных на новую
• ваш уровень сложности увеличивается по мере роста сложности вашего запроса
• а тестирование встроенного запроса требует построения проекта

Думайте о сохраненных Procs как о методах, которые вы вызываете из объекта базы данных - их гораздо легче повторно использовать, есть только одно место для редактирования, и в случае изменения поставщиков DB изменения происходят в ваших сохраненных Procs, а не в вашем коде.

Тем не менее, прирост производительности хранимых procs минимален, как сказал Стью до меня, и вы не можете поставить точку останова в хранимой процедуре (пока).

CON

Я нахожу, что выполнение большого количества обработки внутри хранимых процедур сделало бы ваш сервер DB единственной точкой негибкости, когда дело доходит до масштабирования вашего действия.

Однако выполнение всего этого хруста в вашей программе, в отличие от sql-сервера, может позволить вам масштабировать больше, если у вас есть несколько серверов, на которых выполняется ваш код. Конечно, это не относится к хранимым процессам, которые выполняют только обычную выборку или обновление, но и к тем, которые выполняют больше обработки, например циклирование по наборам данных.

PROS

1. Спектакль для того, что может быть стоит (во избежание разбора запроса водитель DB / план отдыха и т. д.)
2. Манипуляция данными не встроена в код C/C++/C#, что означает, что у меня есть менее низкоуровневый код для просмотра. SQL менее многословен и легче просматривается, когда он указан отдельно.
3. Благодаря разделению люди могут найти и повторно использовать код SQL намного проще.
4. Его легче изменить, когда меняется схема - вы просто должны дать тот же вывод кода, и он будет работать просто отлично
5. Проще портировать в другую базу данных.
6. Я могу перечислить отдельные разрешения на мои хранимые процедуры и управлять доступом на этом уровне тоже.
7. Я могу профилировать свой код сохраняемости data query/ отдельно от кода преобразования данных.
8. Я могу реализовать изменяемые условия в своей хранимой процедуре, и это будет легко настроить на сайте клиента.
9. Становится проще использовать некоторые автоматизированные инструменты для преобразования моей схемы и операторов вместе, а не когда она встроена в мой код, где я должен был бы выследить их.
10. Обеспечение наилучших практик для доступа к данным проще, когда у вас есть весь код доступа к данным внутри одного файла - я могу проверить запросы, которые обращаются к неиспользуемой таблице или к той, которая использует более высокий уровень сериализации или выбирает *в коде и т. д.
11. Становится легче найти изменения схемы / логики манипулирования данными, когда все это перечислено в одном файле.
12. Становится проще выполнять поиск и замену правок на SQL, когда они находятся в одном и том же месте, например, изменять / добавлять операторы изоляции транзакций для всех сохраненных процессов.
13. Я и DBA парень находим, что иметь отдельный файл SQL легче / удобнее, когда DBA должен просмотреть мои SQL вещи.
14. Наконец, вам не нужно беспокоиться о SQL инъекционных атаках, потому что некоторые ленивые члены вашей команды не использовали параметризованные запросы при использовании встроенных sqls.

Преимущество производительности для хранимых процедур часто пренебрежимо мало.

Дополнительные преимущества для хранимых процедур:

• Предотвращение обратного инжиниринга (если он был создан с помощью шифрования, конечно)
• Лучшая централизация доступа к базе данных
• Возможность прозрачного изменения модели данных (без необходимости развертывания новых клиентов); особенно удобно, если несколько программ обращаются к одной и той же модели данных

Я падаю на кодовую сторону. Мы создаем слой доступа к данным, который используется всеми приложениями (как веб -, так и клиентскими), поэтому с этой точки зрения он DRY. Это упрощает базу данных deployment, потому что мы просто должны убедиться, что схема таблицы правильна. Это упрощает обслуживание кода, потому что нам не нужно смотреть на исходный код и базу данных.

У меня нет особых проблем с тесной связью с моделью данных, потому что я не вижу, где можно действительно разорвать эту связь. Приложение и его данные неразрывно связаны.

Сохраняемые процедуры.

Если произойдет ошибка или логика немного изменится,вам не нужно будет перекомпилировать проект. Кроме того, он позволяет получить доступ из разных источников, а не только из одного места, где вы закодировали запрос в своем проекте.

Я не думаю, что это сложнее поддерживать хранимые процедуры, вы не должны кодировать их непосредственно в базе данных, но сначала в отдельных файлах, а затем вы можете просто запустить их на любом DB, который вам нужно настроить.

Преимущества хранимых процедур :

Более легко просматривать код.

Меньше соединено, поэтому более легко испытано.

Более легко настраивается.

Производительность, как правило, лучше, с точки зрения сетевого трафика - если у вас есть курсор или что-то подобное, то нет нескольких поездок в базу данных

Вы можете защитить доступ к данным более легко, удалить прямой доступ к таблицам, обеспечить безопасность через procs - это также позволяет относительно быстро найти любой код, который обновляет таблицу.

Если используются другие службы (например, службы Reporting services), вам может оказаться проще хранить всю свою логику в хранимой процедуре, а не в коде, и вам придется дублировать ее

Ущерб:

Сложнее управлять разработчикам: контроль версий скриптов: есть ли у каждого своя база данных, интегрирована ли система контроля версий с базой данных и IDE?

В некоторых случаях динамически созданный sql в коде может иметь лучшую производительность, чем сохраненный proc. Если вы создали сохраненный proc (скажем, sp_customersearch), который становится чрезвычайно сложным с десятками параметров, потому что он должен быть очень гибким, вы, вероятно, можете создать гораздо более простой оператор sql в коде во время выполнения.

Можно было бы возразить, что это просто перемещает некоторую обработку с SQL на веб-сервер, но в целом это было бы хорошо.

Еще одна замечательная вещь в этом методе заключается в том, что если вы смотрите в SQL profiler, вы можете увидеть созданный запрос и отладить его гораздо проще, чем увидеть сохраненный вызов proc с 20 параметрами.

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

Большой поклонник Transact SQL, настройка больших запросов оказалась для меня очень полезной. Я не писал ни одного inline SQL уже около 6 лет!

Вы перечисляете 2 pro-points для sprocs:

Производительность-не очень. В Sql 2000 или выше оптимизация плана запроса довольно хороша и кэшируется. Я уверен, что Oracle и другие делают то же самое. Я больше не думаю, что есть дело для sprocs для производительности.

Безопасность? Зачем хранимые процедуры будет более безопасным? Если у вас нет довольно незащищенной базы данных, в любом случае весь доступ будет осуществляться с вашего DBAs или через ваше приложение. Всегда параметризуйте все запросы - никогда не вставляйте что-то из пользовательского ввода, и вы будете в порядке.

В любом случае, это лучшая практика для исполнения.

Linq-это определенно то, как я бы сейчас пошел на новый проект. Смотрите этот аналогичный пост .

@Keith

Безопасность? Зачем хранимые процедуры будет более безопасным?

Как предлагает Komradekatz, вы можете запретить доступ к таблицам (для комбинации имя пользователя / пароль, которая подключается к DB) и разрешить доступ только к SP. Таким образом, если кто-то получает имя пользователя и пароль к вашей базе данных, он может выполнить SP, но не может получить доступ к таблицам или любой другой части DB.

(Конечно, выполнение sprocs может дать им все необходимые данные, но это будет зависеть от доступных sprocs. Предоставление им доступа к таблицам дает им доступ ко всему.)

Подумайте об этом так

У вас есть 4 веб-сервера и куча windows приложений, которые используют один и тот же код SQL Теперь вы поняли, что есть небольшая проблема с кодом SQl так же, как и ты...... меняем прок в 1 месте или передвиньте код на все веб-серверы, переустановите все настольные приложения(clickonce может помочь) на всех полях windows

Я предпочитаю хранимые процедуры

Также проще провести тестирование производительности с помощью proc, поместив его в анализатор запросов установить статистику io/time on установите showplan_text on и вуаля

нет необходимости запускать профилировщик, чтобы точно увидеть, что называется

только мои 2 цента

Я предпочитаю хранить их в коде (используя ORM, а не встроенный или ad-hoc), поэтому они покрываются системой управления версиями без необходимости сохранения файлов .sql.

Кроме того, хранимые процедуры по своей сути не являются более безопасными. Вы можете написать плохой запрос с помощью sproc так же легко, как и встроенный. Параметризованные встроенные запросы могут быть так же безопасны, как и sproc.

Используйте свой код приложения как то, что он делает лучше всего: обрабатывать логику.
Используйте свою базу данных для того, что она делает лучше всего: хранения данных.

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

Кроме того, хранимые процедуры с необязательными параметрами поиска очень эффективны, потому что вы должны заранее указать все возможные параметры, а сложные поиски иногда невозможны, потому что вы не можете предсказать, сколько раз параметр будет повторяться в seach.

Когда речь заходит о безопасности, хранимые процедуры гораздо более безопасны. Некоторые утверждают, что весь доступ будет осуществляться через приложение в любом случае. Дело в том, что многие люди забывают, что большинство нарушений безопасности происходит изнутри компании. Подумайте о том, сколько разработчиков знают имя пользователя "hidden" и пароль для вашего приложения?

Кроме того, как указал MatthieuF, производительность может быть значительно улучшена за счет меньшего количества циклов между приложением (будь то на рабочем столе или веб-сервере) и сервером базы данных.

По моему опыту, абстракция модели данных с помощью хранимых процедур также значительно улучшает ремонтопригодность. Как человек, которому приходилось поддерживать много баз данных в прошлом, это такое облегчение, когда он сталкивается с необходимым изменением модели, чтобы иметь возможность просто изменить одну или две хранимые процедуры и сделать это изменение полностью прозрачным для ALL внешних приложений. Во многих случаях ваше приложение не является единственным, указывающим на базу данных, - есть и другие приложения, решения для создания отчетов и т. д. таким образом, отслеживание всех этих затронутых точек может быть проблемой с открытым доступом к таблицам.

Я также добавлю в колонку "плюс" проверки для передачи программирования SQL в руки тех, кто специализируется на нем, и для SPs, что значительно облегчает выделение и тестирование/оптимизацию кода.

Единственный недостаток, который я вижу, заключается в том, что многие языки не позволяют передавать параметры таблицы, поэтому передача неизвестного числа значений данных может раздражать, а некоторые языки все еще не могут обрабатывать получение нескольких наборов результатов из одной хранимой процедуры (хотя последняя не делает SPs хуже, чем встроенная SQL в этом отношении).

Одно из предложений от Microsoft TechEd сессий по безопасности, на которых я присутствовал, чтобы сделать все вызовы через сохраненные procs и запретить доступ непосредственно к таблицам. Этот подход был объявлен как обеспечивающий дополнительную безопасность. Я не уверен, стоит ли это просто для безопасности, но если вы уже используете сохраненные procs, это не повредит.

Определенно, его легче поддерживать, если поместить его в хранимую процедуру. Если есть сложная логика, которая потенциально может измениться в будущем, то, безусловно, неплохо поместить ее в базу данных, когда у вас есть несколько подключенных клиентов. Например, я сейчас работаю над приложением, которое имеет веб-интерфейс конечного пользователя и административное настольное приложение, оба из которых имеют общую базу данных (очевидно), и я пытаюсь сохранить как можно больше логики в базе данных. Это прекрасный пример принципа DRY .

Мы используем хранимые процедуры с Oracle DB, где я сейчас работаю. Мы также используем подрывную деятельность. Все хранимые процедуры создаются как файлы .pkb & .pks и сохраняются в Subversion. Я уже делал in-line SQL раньше, и это очень больно! Мне гораздо больше нравится, как мы это делаем здесь. Создание и тестирование новых хранимых процедур намного проще, чем выполнение их в коде.

Тереза

Я твердо стою на стороне сохраненных proc, предполагая, что вы не обманываете и не используете dynamic SQL в сохраненном proc. Во-первых, использование хранимых procs позволяет dba устанавливать разрешения на уровне хранимых proc, а не на уровне таблицы. Это имеет решающее значение не только для борьбы с SQL инъекционными атаками, но и для предотвращения прямого доступа инсайдеров к базе данных и изменения ситуации. Это способ помочь предотвратить мошенничество. Никакая база данных, содержащая личную информацию (SSNs, номера кредитных карт и т. д.)Или которая в любом случае создает финансовые транзакции, никогда не должна быть доступна, кроме как через процедуры strored. Если вы используете любой другой метод, вы оставляете свою базу данных широко открытой для лиц в компании, чтобы создавать поддельные финансовые транзакции или красть данные, которые могут быть использованы для кражи личных данных.

Сохраненные procs также гораздо проще поддерживать и настраивать производительность, чем SQL, отправленные из приложения. Они также позволяют dba видеть, какое влияние структурные изменения базы данных окажут на способ доступа к данным. Я никогда не встречал хорошего dba, который позволил бы динамический доступ к базе данных.

Я не большой поклонник хранимых процедур, но я использую их в одном условии:

Когда запрос довольно большой, лучше хранить его в базе данных как хранимую процедуру, а не отправлять из кода. Таким образом, вместо отправки огромного количества строковых символов с сервера приложений в базу данных будет отправлена только команда "EXEC SPNAME" .

Это излишне, когда сервер базы данных и веб-сервер не находятся в одной сети (например, связь через интернет). И даже если это не так, слишком большой стресс означает много потраченного впустую времени.

Но, черт возьми, с ними так ужасно управляться. Я стараюсь избегать их, насколько это возможно.

Сохраненный proc SQL не увеличивает производительность запроса

Очевидно, что использование хранимых процедур имеет ряд преимуществ перед построением SQL в коде.

1. Ваша реализация кода и SQL становятся независимыми друг от друга.
2. Код легче читать.
3. Пишите один раз, используйте много раз.
4. Изменить один раз
5. Нет необходимости давать программисту внутренние сведения о базе данных. и так далее, и тому подобное.

Небольших бревен

Еще один незначительный Профи для хранимых процедур, который не был упомянут: когда речь заходит о трафике SQL, доступ к данным на основе sp генерирует гораздо меньше трафика. Это становится важным, когда вы отслеживаете трафик для анализа и профилирования-журналы будут намного меньше и удобочитаемы.