Являются ли хранимые процедуры в целом более эффективными, чем встроенные операторы в современных СУБД?

Не знаю, что они быстрее. Мне нравится использовать ORM для доступа к данным (чтобы не изобретать колесо заново), но я понимаю, что это не всегда жизнеспособный вариант.

У Франса Баумы есть хорошая статья на эту тему: http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

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

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

Прочтите об этом отличный пост Франса Баумы (если он немного предвзято).

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

Я могу говорить только с SQL-сервером. На этой платформе хранимые процедуры прекрасны, потому что сервер хранит план выполнения, что в большинстве случаев значительно повышает производительность. Я говорю «в большинстве случаев», потому что, если у SP очень разные пути выполнения, вы можете получить неоптимальную производительность. Однако даже в этих случаях некоторый продуманный рефакторинг SP может ускорить процесс.

Это споры, которые бушуют постоянно (например, здесь).

Написать плохие хранимые процедуры так же просто, как написать плохую логику доступа к данным в вашем приложении.

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

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

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

Обновлено: хотя статья Франса Боумы действительно многословна, она на милю упускает суть в отношении безопасности. Тот факт, что ему 5 лет, тоже не способствует его актуальности.

Я предпочитаю использовать SP, когда есть смысл их использовать. В любом случае в SQL Server у SP нет преимущества в производительности по сравнению с параметризованным запросом.

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

Так что в данном случае чувства покупателя преобладают над всеми остальными аргументами.

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

Обратите внимание, что параметризованный запрос отличается от специального sql.

Основная причина, по которой imo по-прежнему отдает предпочтение хранимым процедурам, больше связана с безопасностью. Если вы используете хранимые процедуры исключительно, вы можете отключить разрешения INSERT, SELECT, UPDATE, DELETE, ALTER, DROP, CREATE и т. д. Для пользователя вашего приложения, оставив его только с EXECUTE.

Это обеспечивает небольшую дополнительную защиту от внедрения 2-й порядок sql. Параметризованные запросы защищают только от внедрения 1-й порядок.

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

1. Разработчики приложений не всегда лучшие программисты SQL. Хранимые процедуры скрывают SQL от приложения.

2. Хранимые процедуры автоматически используют переменные связывания. Разработчики приложений часто избегают связывать переменные, потому что они кажутся ненужным кодом и малоэффективны в небольших тестовых системах. Позже отказ от использования переменных связывания может снизить производительность СУБД.

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

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

Тем не менее, я обычно использую необработанный SQL в своих приложениях, чтобы сам управлять им. Это зависит от вашей команды разработчиков и философии.

NOTE that this is a general look at stored procedures not regulated to a specific DBMS. Some DBMS (and even, different versions of the same DBMS!) may operate contrary to this, so you'll want to double-check with your target DBMS before assuming all of this still holds.

I've been a Sybase ASE, MySQL, and SQL Server DBA on-and off since for almost a decade (along with application development in C, PHP, PL/SQL, C#.NET, and Ruby). So, I have no particular axe to grind in this (sometimes) holy war.

Исторический выигрыш в производительности хранимых процедур обычно был следующим (в произвольном порядке):

• Предварительно проанализированный SQL
• Предварительно сгенерированный план выполнения запроса
• Сниженная задержка в сети
• Возможные преимущества кеширования

Предварительно проанализированный SQL - преимущества, аналогичные компилированному и интерпретируемому коду, за исключением очень микроуровня.

Все еще преимущество? Совсем не очень заметно на современном ЦП, но если вы отправляете один оператор SQL, который ОЧЕНЬ большой - одиннадцать миллиардов раз в секунду, накладные расходы на синтаксический анализ могут возрасти.

Предварительно сгенерированный план выполнения запроса. Если у вас много JOIN, перестановки могут стать совершенно неуправляемыми (современные оптимизаторы имеют ограничения и ограничения по соображениям производительности). Известно, что очень сложный SQL имеет отчетливые, измеримые (я видел, как сложный запрос занимал 10+ секунд только для генерации плана, прежде чем мы настраивали СУБД) задержки из-за того, что оптимизатор пытается выяснить «почти лучший "план исполнения. Хранимые процедуры, как правило, сохраняют это в памяти, чтобы вы могли избежать этих накладных расходов.

Все еще преимущество? Большинство СУБД (последние выпуски) кэшируют планы запросов для ИНДИВИДУАЛЬНЫХ операторов SQL, что значительно снижает разницу в производительности между хранимыми процедурами и специальным SQL. Есть некоторые предостережения и случаи, когда это не так, поэтому вам необходимо протестировать свою целевую СУБД.

Кроме того, все больше и больше СУБД позволяют предоставлять планы путей оптимизатора (абстрактные планы запросов), чтобы значительно сократить время оптимизации (как для специальных, так и для хранимых процедур SQL !!).

WARNING Cached query plans are not a performance panacea. Occasionally the query plan that is generated is sub-optimal. For example, if you send SELECT * FROM table WHERE id BETWEEN 1 AND 99999999, the DBMS may select a full-table scan instead of an index scan because you're grabbing every row in the table (so sayeth the statistics). If this is the cached version, then you can get poor performance when you later send SELECT * FROM table WHERE id BETWEEN 1 AND 2. The reasoning behind this is outside the scope of this posting, but for further reading see: http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx and http://msdn.microsoft.com/en-us/library/ms181055.aspx and http://www.simple-talk.com/sql/performance/execution-plan-basics/

"In summary, they determined that supplying anything other than the common values when a compile or recompile was performed resulted in the optimizer compiling and caching the query plan for that particular value. Yet, when that query plan was reused for subsequent executions of the same query for the common values (‘M’, ‘R’, or ‘T’), it resulted in sub-optimal performance. This sub-optimal performance problem existed until the query was recompiled. At that point, based on the @P1 parameter value supplied, the query might or might not have a performance problem."

Сниженная задержка в сети A) Если вы запускаете один и тот же SQL снова и снова - а SQL добавляет много килобайт кода, - замена его простым "exec foobar" действительно может сложиться. Б) Сохраненные процедуры могут использоваться для перемещения процедурного кода в СУБД. Это позволяет избежать перетасовки больших объемов данных клиенту только для того, чтобы он отправил обратно тонкую струйку информации (или вообще никакой!). Аналогично выполнению JOIN в СУБД по сравнению с вашим кодом (всеми любимый WTF!)

Все еще преимущество? A) Современный Ethernet 1 Гб (и 10 Гб и выше!) Действительно делает это незначительным. Б) Зависит от того, насколько насыщена ваша сеть - зачем толкать несколько мегабайт данных туда и обратно без уважительной причины?

Возможные преимущества кеширования Выполнение преобразований данных на стороне сервера потенциально может быть быстрее, если у вас достаточно памяти в СУБД и необходимые данные находятся в памяти сервера.

Все еще преимущество? Если ваше приложение не имеет доступа к общей памяти для данных СУБД, край всегда будет для сохраненных процессов.

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

Параметризованный / подготовленный SQL
Это нечто среднее между хранимыми процедурами и специальным SQL, они представляют собой встроенные операторы SQL на языке хоста, который использует «параметры» для значений запроса, например:

SELECT .. FROM yourtable WHERE foo = ? AND bar = ?

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

Специальный SQL Просто откройте окно консоли своей СУБД и введите оператор SQL. В прошлом это были «худшие» исполнители (в среднем), поскольку СУБД не имела возможности предварительно оптимизировать запросы, как в параметризованном / сохраненном методе процедуры.

Все еще недостаток? Не обязательно. Большинство СУБД имеют возможность «абстрагировать» специальный SQL в параметризованные версии - таким образом, более или менее нивелируя разницу между ними. Некоторые делают это неявно или должны быть включены с помощью настройки команды (SQL-сервер: http://msdn.microsoft.com/en-us/library/ms175037.aspx, Oracle: http://www.praetoriate.com/oracle_tips_cursor_sharing.htm).

Уроки выучены? Закон Мура продолжает действовать, и оптимизаторы СУБД с каждым выпуском становятся все сложнее. Конечно, вы можете поместить каждую дурацкую крошечную инструкцию SQL в хранимую процедуру, но просто знайте, что программисты, работающие над оптимизаторами, очень умны и постоянно ищут способы повышения производительности. В конце концов (если его еще нет) производительность специального SQL станет неотличимой (в среднем!) От производительности хранимых процедур, поэтому любая хранимая процедура массивный использует ** исключительно «по соображениям производительности» **, для меня это звучит как преждевременная оптимизация .

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

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

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

Да, в большинстве случаев они быстрее. Составление SQL также является огромной областью настройки производительности. Если я делаю приложение типа бэк-офиса, я могу пропустить их, но все, что касается производства, я использую их наверняка по всем причинам, о которых говорили другие, а именно безопасности.

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

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

Единственная тема, которую еще никто не упомянул в качестве преимущества хранимых процедур, - это безопасность. Если вы создаете приложение исключительно с доступом к данным через хранимые процедуры, вы можете заблокировать базу данных, чтобы ЕДИНСТВЕННЫЙ доступ осуществлялся через эти хранимые процедуры. Таким образом, даже если кто-то получит идентификатор базы данных и пароль, он будет ограничен в том, что он может видеть или делать с этой базой данных.

ИМХО...

Ограничение операций "C_UD" хранимыми процедурами может сохранить логику целостности данных в одном месте. Это также можно сделать, ограничив операции "C_UD" одним промежуточным слоем.

Приложению могут быть предоставлены операции чтения, чтобы они могли присоединяться только к нужным им таблицам / столбцам.

Хранимые процедуры также могут использоваться вместо параметризованных запросов (или специальных запросов) для некоторых других преимуществ:

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

Причины использования хранимых процедур:

• Уменьшите сетевой трафик - вы должны отправить инструкцию SQL по сети. С помощью sprocs вы можете выполнять SQL пакетами, что также более эффективно.
• Кэширующий план запроса - при первом запуске sproc SQL Server создает план выполнения, который кэшируется для повторного использования. Это особенно эффективно при частом выполнении небольших запросов.
• Возможность использования выходных параметров - если вы отправляете встроенный SQL, который возвращает одну строку, вы можете вернуть только набор записей. С помощью sprocs вы можете вернуть их в качестве выходных параметров, что значительно быстрее.
• Разрешения - когда вы отправляете встроенный SQL, вы должны предоставить пользователю права доступа к таблице (таблицам), что дает гораздо больший доступ, чем просто разрешение на выполнение sproc.
• Разделение логики - удалить код, генерирующий SQL, и выделить его в базе данных.
• Возможность редактирования без перекомпиляции - это может быть спорным. Вы можете редактировать SQL в sproc без перекомпиляции приложения.
• Найдите, где используется таблица - с помощью sproc, если вы хотите найти все операторы SQL, ссылающиеся на определенную таблицу, вы можете экспортировать код sproc и выполнить поиск по нему. Это намного проще, чем пытаться найти это в коде.
• Оптимизация - администраторам баз данных проще оптимизировать SQL и настроить базу данных при использовании sprocs. Легче найти недостающие индексы и тому подобное.
• Атаки с использованием SQL-инъекций - правильно написанный встроенный SQL может защитить от атак, но sprocs лучше для этой защиты.

Сниженный сетевой трафик - SP обычно хуже Dynamic SQL. Поскольку люди не создают новый SP для каждого выбора, если вам нужен только один столбец, вам говорят, используйте SP, в котором есть нужные им столбцы, и игнорируйте остальные. Получите дополнительный столбец и любое меньшее использование сети, которое вы только что прекратили. Кроме того, при использовании SP у вас, как правило, много фильтрации клиентов.

кэширование - MS-SQL не обрабатывает их по-другому, поскольку MS-SQL 2000 может быть 7, но я не помню.

разрешения - не проблема, так как почти все, что я делаю, - это веб-сайт или средний уровень приложения, который выполняет весь доступ к базе данных. Единственное программное обеспечение, с которым я работаю, у которого есть прямой клиентский доступ к базе данных, - это сторонние продукты, которые предназначены для пользователей с прямым доступом и основаны на предоставлении пользователям разрешений. И да, модель безопасности разрешений MS-SQL ОТСАСЫВАЕТ !!! (еще не уделяли внимания 2008 г.) В заключение хотелось бы увидеть обзор того, сколько людей все еще занимаются прямым программированием клиент / сервер по сравнению с программированием веб-серверов и серверов промежуточных приложений; и если они делают большие проекты, почему нет ORM.

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

Возможность редактирования - Что у вас нет тестирования и контроля версий, о чем вам нужно беспокоиться? Также проблема только с клиентом / сервером, в веб-мире не проблема.

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

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

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

В 2007 году я был в проекте, где мы использовали MS SQL Server через ORM. У нас было 2 большие растущие таблицы, загрузка которых на SQL Server занимала до 7-8 секунд. После создания двух больших хранимых процедур SQL и их оптимизации с помощью планировщика запросов время загрузки каждой БД сократилось до менее 20 миллисекунд, поэтому очевидно, что для использования хранимых процедур SQL все еще есть причины для повышения эффективности.

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

Наш поставщик ORM обычно заявлял, что выполнение множества небольших SQL-запросов будет более эффективным, чем получение больших объединенных наборов данных. Наш опыт (к нашему удивлению) показал другое.

Это, конечно, может варьироваться между машинами, сетями, операционными системами, SQL-серверами, платформами приложений, структурами ORM и языковыми реализациями, поэтому измерьте любую выгоду, вы ДУМАЕТЕ, что можете получить от выполнения чего-то другого.

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

Для меня одно из преимуществ хранимых процедур заключается в том, что они не зависят от основного языка: вы можете переключаться с C, Python, PHP или любого другого приложения на другой язык программирования, не переписывая свой код. Кроме того, некоторые функции, такие как массовые операции, действительно повышают производительность и недоступны (вообще?) На языках хоста.