Являются ли хранимые процедуры в целом более эффективными, чем встроенные операторы современных СУБД?
Общепринятое мнение гласит, что хранимые процедуры всегда быстрее. Так что, поскольку они всегда быстрее, используйте их ВСЕ ВРЕМЯ.
Я почти уверен, что это основано на некотором историческом контексте, где это было когда-то. Я не сторонник того, что хранимые процедуры не нужны, но я хочу знать, в каких случаях хранимые процедуры необходимы в современных базах данных, таких как MySQL, SQL Server, Oracle или < Insert_your_DB_here>. Излишне ли иметь ВСЕ доступ через хранимые процедуры?
20 ответов
Обратите внимание, что это общий взгляд на хранимые процедуры, не регулируемые конкретной СУБД. Некоторые СУБД (и даже разные версии одной и той же СУБД!) Могут работать вопреки этому, поэтому вы должны перепроверить вашу целевую СУБД, прежде чем предположить, что все это по-прежнему сохраняется.
Я работаю с Sybase ASE, MySQL и SQL Server DBA почти десять лет (вместе с разработкой приложений на C, PHP, PL/SQL, C#.NET и Ruby). Так что у меня нет особого топора, который можно заточить в этой (иногда) священной войне.
Историческая выгода для производительности хранимых процедур обычно заключалась в следующем (в произвольном порядке):
- Предварительно проанализированный SQL
- Предварительно сгенерированный план выполнения запроса
- Уменьшенная задержка в сети
- Потенциальные преимущества кэша
Предварительно проанализированный SQL - аналогичные преимущества для скомпилированного и интерпретированного кода, за исключением очень микроуровня.
Все еще преимущество? Совсем не заметно на современном процессоре, но если вы отправляете один оператор SQL, ОЧЕНЬ большой, одиннадцать миллиардов раз в секунду, накладные расходы при разборе могут сложиться.
Предварительно сгенерированный план выполнения запроса. Если у вас много JOIN, то перестановки могут стать совершенно неуправляемыми (современные оптимизаторы имеют ограничения и ограничения по соображениям производительности). Не секрет, что очень сложный SQL имеет отчетливые, измеримые (я видел, как сложный запрос занимал 10+ секунд, просто чтобы сгенерировать план, прежде чем мы настроили СУБД), задержки из-за того, что оптимизатор пытался выяснить "почти лучший" План выполнения. Хранимые процедуры, как правило, хранят это в памяти, так что вы можете избежать этих издержек.
Все еще преимущество? Большинство СУБД (последние выпуски) будут кэшировать планы запросов для операторов INDIVIDUAL SQL, значительно уменьшая разницу в производительности между хранимыми процессами и специальным SQL. Есть некоторые предостережения и случаи, в которых это не так, поэтому вам нужно протестировать на вашей целевой СУБД.
Кроме того, все больше и больше СУБД позволяют предоставлять планы путей оптимизатора (абстрактные планы запросов), чтобы значительно сократить время оптимизации (как для специальной, так и для хранимой процедуры SQL!!).
ПРЕДУПРЕЖДЕНИЕ. Кэшированные планы запросов не являются панацеей от производительности. Иногда сгенерированный план запроса является неоптимальным. Например, если вы отправляете
SELECT * FROM table WHERE id BETWEEN 1 AND 99999999СУБД может выбрать сканирование полной таблицы вместо сканирования индекса, потому что вы захватываете каждую строку в таблице (например, статистику). Если это кэшированная версия, вы можете получить низкую производительность при последующей отправкеSELECT * FROM table WHERE id BETWEEN 1 AND 2, Обоснование этого выходит за рамки этой публикации, но для дальнейшего чтения см.: http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx и http://msdn.microsoft.com/en-us/library/ms181055.aspx и http://www.simple-talk.com/sql/performance/execution-plan-basics/В итоге они определили, что предоставление чего-либо, кроме общих значений, когда выполнялась компиляция или перекомпиляция, приводило к тому, что оптимизатор компилировал и кэшировал план запроса для этого конкретного значения. Тем не менее, когда этот план запроса использовался повторно для последующих выполнений того же самого запрос общих значений ('M', 'R' или 'T'), это привело к неоптимальной производительности. Эта проблема неоптимальной производительности существовала до тех пор, пока запрос не был перекомпилирован. В тот момент, на основе @P1 Если задано значение параметра, запрос может иметь или не иметь проблемы с производительностью."
Снижение задержки в сети A) Если вы запускаете один и тот же SQL снова и снова - и SQL добавляет много килобайт кода - его можно заменить простой "exec foobar". Б) Хранимые процедуры могут использоваться для перемещения процедурного кода в СУБД. Это экономит перетасовку больших объемов данных клиенту только для того, чтобы он отправлял струю информации обратно (или ни одной вообще!). Аналогично выполнению JOIN в СУБД и в вашем коде (всем любимый WTF!)
Все еще преимущество? A) Современный 1Gb (и 10Gb и выше!) Ethernet действительно делает это незначительным. Б) Зависит от того, насколько насыщена ваша сеть - зачем без толку переносить несколько мегабайт данных туда-сюда?
Потенциальные преимущества кэша Выполнение преобразований данных на стороне сервера потенциально может быть быстрее, если у вас достаточно памяти в СУБД и необходимые данные находятся в памяти сервера.
Все еще преимущество? Если ваше приложение не имеет доступа к общей памяти к данным СУБД, преимущество всегда будет за сохраненными процессами.
Конечно, никакое обсуждение оптимизации хранимых процедур не будет полным без обсуждения параметризованного и специального 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 ad hoc станет (в среднем!) Неотличимой от производительности хранимых процедур, поэтому любой вид массивной хранимой процедуры, используемой ** исключительно по "причинам производительности"**, для меня кажется преждевременной оптимизацией,
Во всяком случае, я думаю, что если вы избегаете крайних случаев и имеете довольно ванильный SQL, вы не заметите разницы между специальными и хранимыми процедурами.
Причины использования хранимых процедур:
- Сократите сетевой трафик - вы должны отправить SQL-оператор по сети. С помощью sprocs вы можете выполнять SQL пакетами, что также более эффективно.
- Кэширование плана запроса - при первом запуске sproc SQL Server создает план выполнения, который кэшируется для повторного использования. Это особенно эффективно для небольших запросов, выполняемых часто.
- Возможность использовать выходные параметры - если вы отправляете встроенный SQL, который возвращает одну строку, вы можете получить только набор записей. С помощью sprocs вы можете получить их обратно в качестве выходных параметров, что значительно быстрее.
- Разрешения - когда вы отправляете встроенный SQL, вы должны предоставить пользователю разрешения на таблицы (таблицы), которые предоставляют гораздо больший доступ, чем просто разрешение на выполнение sproc.
- Разделение логики - удалите генерирующий SQL код и выделите его в базе данных.
- Возможность редактирования без перекомпиляции - это может быть спорным. Вы можете редактировать SQL в sproc без перекомпиляции приложения.
- Найдите, где используется таблица - с помощью sprocs, если вы хотите найти все операторы SQL, ссылающиеся на конкретную таблицу, вы можете экспортировать код sproc и найти его. Это гораздо проще, чем пытаться найти его в коде.
- Оптимизация - администратору базы данных легче оптимизировать SQL и настроить базу данных при использовании sprocs. Проще найти недостающие индексы и тому подобное.
- Атаки SQL-инъекций - правильно написанный встроенный SQL-код может защитить от атак, но sprocs лучше для этой защиты.
Во многих случаях хранимые процедуры на самом деле медленнее, потому что они более обобщены. В то время как хранимые процедуры могут быть сильно настроены, по моему опыту, есть достаточно развития и институционального трения, что они остаются на месте, когда они работают, поэтому хранимые процедуры часто имеют тенденцию возвращать много столбцов "на всякий случай" - потому что вы не хотите развернуть новую хранимую процедуру каждый раз, когда вы меняете приложение. OR/M, с другой стороны, запрашивает только те столбцы, которые использует приложение, что сокращает сетевой трафик, ненужные объединения и т. Д.
В большинстве современных баз данных нет заметных различий в скорости хранимых процедур по сравнению с параметризованными или подготовленными запросами, поскольку база данных также будет кэшировать планы выполнения для этих запросов.
Обратите внимание, что параметризованный запрос не совпадает с ad hoc sql.
Основная причина, по которой imo по-прежнему предпочитает хранимые процедуры, сегодня больше связана с безопасностью. Если вы используете исключительно хранимые процедуры, вы можете отключить разрешения INSERT, SELECT, UPDATE, DELETE, ALTER, DROP, CREATE и т. Д. Для пользователя вашего приложения, оставив его только с кнопкой EXECUTE.
Это обеспечивает немного дополнительную защиту от инъекций sql 2-го порядка. Параметризованные запросы защищают только от внедрения 1-го порядка.
Это дебаты, которые бушуют снова и снова (например, здесь).
Писать плохие хранимые процедуры так же легко, как писать логику доступа к плохим данным в вашем приложении.
Я предпочитаю Stored Procs, но это потому, что я обычно работаю с очень большими и сложными приложениями в корпоративной среде, где есть выделенные администраторы баз данных, которые отвечают за бесперебойную работу серверов баз данных.
В других ситуациях я достаточно счастлив, что технологии доступа к данным, такие как LINQ, позаботятся об оптимизации.
Чистая производительность - не единственное соображение. Такие аспекты, как безопасность и управление конфигурацией, как минимум, так же важны.
Редактировать: Хотя статья Франса Боума действительно многословна, она упускает из виду пункт о безопасности на милю. Тот факт, что ему 5 лет, тоже не помогает его актуальности.
В 2007 году я был в проекте, где мы использовали MS SQL Server через ORM. У нас было 2 больших растущих таблицы, которые занимали до 7-8 секунд времени загрузки на SQL Server. После создания 2 больших хранимых процедур SQL и их оптимизации из планировщика запросов время загрузки каждой БД сократилось до менее чем 20 миллисекунд, поэтому очевидно, что все еще существуют причины эффективности использования хранимых процедур SQL.
Сказав это, мы выяснили, что наиболее важным преимуществом хранимых процедур является дополнительная простота обслуживания, безопасность, целостность данных и отделение бизнес-логики от логики промежуточного программного обеспечения, извлекающей выгоду из всей логики промежуточного программного обеспечения от повторного использования двух процедур.,
Наш поставщик ORM обычно заявлял, что запуск множества небольших SQL-запросов будет более эффективным, чем выборка больших объединенных наборов данных. Наш опыт (к нашему удивлению) показал что-то еще.
Конечно, это может варьироваться в зависимости от машин, сетей, операционных систем, серверов SQL, сред приложений, сред ORM и языковых реализаций, поэтому оцените любую выгоду, ВЫ ДУМАЕТЕ, что можете получить что-то еще.
Только когда мы провели сравнительный анализ, мы обнаружили, что проблема заключается в том, что ORM и база данных принимают на себя всю нагрузку.
Одна тема, которую еще никто не упомянул в качестве преимущества хранимых процедур, - это безопасность. Если вы создаете приложение исключительно с доступом к данным с помощью хранимых процедур, вы можете заблокировать базу данных, чтобы ЕДИНСТВЕННЫЙ доступ осуществлялся с помощью этих хранимых процедур. Поэтому, даже если кто-то получит идентификатор базы данных и пароль, он будет ограничен в том, что он может видеть или делать с этой базой данных.
Очевидно, что фактические показатели должны измеряться в отдельных случаях, а не предполагаться. Но даже в тех случаях, когда выполнение хранимых процедур снижает производительность, есть веские причины использовать их:
Разработчики приложений не всегда лучшие SQL-кодеры. Хранимые процедуры скрывают SQL от приложения.
Хранимые процедуры автоматически используют переменные связывания. Разработчики приложений часто избегают связывания переменных, потому что они кажутся ненужным кодом и дают небольшую выгоду в небольших тестовых системах. Позже отказ от использования переменных связывания может снизить производительность СУБД.
Хранимые процедуры создают слой косвенности, который может быть полезен позже. Можно изменить детали реализации (включая структуру таблицы) на стороне базы данных, не затрагивая код приложения.
Создание хранимых процедур может быть полезно для документирования всех взаимодействий базы данных для системы. И легче обновлять документацию, когда все меняется.
Тем не менее, я обычно встраиваю сырой SQL в свои приложения, чтобы я мог контролировать его сам. Это зависит от вашей команды разработчиков и философии.
Я предпочитаю использовать SP, когда есть смысл их использовать. В любом случае, в SQL Server нет преимуществ в производительности для SP по сравнению с параметризованным запросом.
Однако на моей нынешней работе мой начальник упомянул, что мы вынуждены использовать SP, потому что наши клиенты требуют их. Они чувствуют, что они в большей безопасности. Я не был здесь достаточно долго, чтобы увидеть, реализуем ли мы безопасность на основе ролей, но у меня такое чувство, что мы это делаем.
Таким образом, чувства клиента превосходят все остальные аргументы в этом случае.
Прочитайте превосходный пост Франса Баума (если немного предвзятый) об этом.
Для меня одним из преимуществ хранимых процедур является независимость от основного языка: вы можете переключаться с C, Python, PHP или любого другого приложения на другой язык программирования без переписывания кода. Кроме того, некоторые функции, такие как массовые операции, действительно повышают производительность и не могут быть легко доступны (вообще не доступны) на основных языках.
Я не знаю, что они быстрее. Мне нравится использовать ORM для доступа к данным (чтобы не изобретать велосипед), но я понимаю, что это не всегда приемлемый вариант.
У Франса Боума есть хорошая статья на эту тему: http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
Использование хранимых процедур для операций CRUD, вероятно, излишне, но это будет зависеть от используемых инструментов и ваших собственных предпочтений (или требований). Я предпочитаю встроенный SQL, но я стараюсь использовать параметризованные запросы для предотвращения атак с использованием SQL-инъекций. Я храню распечатку этого комикса xkcd как напоминание о том, что может пойти не так, если вы не будете осторожны.
Хранимые процедуры могут принести реальный выигрыш в производительности, когда вы работаете с несколькими наборами данных, чтобы вернуть один набор данных. Обычно более эффективно обрабатывать наборы данных в хранимой процедуре, чем отправлять их по проводам для обработки на стороне клиента.
Все, что я могу говорить, это сервер SQL. На этой платформе хранимые процедуры хороши тем, что сервер хранит план выполнения, что в большинстве случаев значительно повышает производительность. Я говорю "в большинстве случаев", потому что, если у SP очень разные пути выполнения, вы можете получить неоптимальную производительность. Тем не менее, даже в этих случаях некоторые просвещенные рефакторинг SP могут ускорить процесс.
Понимая, что это немного не по теме, но если вы используете много хранимых процедур, убедитесь, что есть последовательный способ поставить их под какой-то контроль источника (например, subversion или git) и иметь возможность перенести обновления из вашей системы разработки в тестовую систему в производственную систему.
Когда это делается вручную, без возможности легко проверить, где находится код, это быстро превращается в кошмар.
Да, они быстрее в большинстве случаев. Композиция SQL также является огромной областью настройки производительности. Если я работаю с приложением типа бэк-офиса, я могу их пропустить, но когда сталкиваюсь с производством, я использую их наверняка по всем причинам, о которых говорили другие... а именно по безопасности.
Хранимые процедуры также можно использовать вместо параметризованных запросов (или специальных запросов) для некоторых других преимуществ:
- Если вам нужно что-то исправить (порядок сортировки и т. Д.), Вам не нужно перекомпилировать приложение
- Вы можете запретить доступ ко всем таблицам для этой учетной записи пользователя, предоставить доступ только к хранимым процедурам и направить весь доступ через хранимые процедуры. Таким образом, вы можете настраивать проверку всех входных данных гораздо более гибко, чем ограничения таблиц.
ПО МОЕМУ МНЕНИЮ...
Ограничение операций "C_UD" для хранимых процедур может хранить логику целостности данных в одном месте. Это также можно сделать, ограничив операции "C_UD" одним промежуточным программным уровнем.
Операции чтения могут быть предоставлены приложению, чтобы они могли объединять только те таблицы / столбцы, которые им необходимы.
Снижение сетевого трафика - SP обычно хуже, чем Dynamic SQL. Поскольку люди не создают новый SP для каждого выбора, если вам нужен только один столбец, вам говорят, что используйте SP, в котором есть нужные столбцы, и игнорируйте остальные. Получите дополнительный столбец и уменьшите использование сети, которое вы только что ушли. Кроме того, при использовании SP у вас много фильтрации клиентов.
кеширование - MS-SQL не относится к ним по-другому, не с тех пор как MS-SQL 2000 было 7, но я не помню.
Права доступа - не проблема, так как почти все, что я делаю, это веб или какой-то средний уровень приложения, который обеспечивает доступ к базе данных. Единственное программное обеспечение, с которым я работаю и которое имеет прямой доступ клиента к базе данных, - это сторонние продукты, которые предназначены для прямого доступа пользователей и основаны на предоставлении пользователям разрешений. И да, модель безопасности разрешения MS-SQL отстой!!! (еще не потратил время на 2008 год) В качестве заключительной части этого хотелось бы увидеть опрос о том, сколько людей все еще занимаются прямым клиент-серверным программированием по сравнению с веб- и промежуточным программированием на сервере приложений; и если они делают большие проекты, почему нет ORM.
Разделение - люди могут спросить, почему вы ставите бизнес-логику за пределы среднего уровня. Также, если вы хотите разделить код обработки данных, есть способы сделать это, не помещая его в базу данных.
Возможность редактирования - Что у вас нет тестирования и контроля версий вы должны беспокоиться? Также только проблема с клиентом / сервером, в мире Интернета не проблема.
Найти таблицу - Только если вы сможете определить SP, который его использует, он будет использовать инструменты системы контроля версий, поиска агента или Visual Studio, чтобы найти.
Оптимизация - ваш администратор баз данных должен использовать инструменты базы данных для поиска запросов, которые нуждаются в оптимизации. База данных может сказать администратору базы данных, какие операторы говорят больше всего времени и ресурсов, и они могут исправить это. В случае сложных операторов SQL программистам следует поговорить с администратором базы данных, если об этом не беспокоятся простые операторы выбора.
Атаки SQL-инъекций - SP не предлагают лучшей защиты. Единственное, что они получают, это то, что большинство из них учат, используя параметры, а не динамический SQL, большинство примеров игнорируют параметры.
Хранимые процедуры отлично подходят для случаев, когда код SQL запускается часто, потому что база данных хранит его токены в памяти. Если вы неоднократно запускали один и тот же код за пределами хранимого процесса, вы, скорее всего, понесете прирост производительности из базы данных, повторяя один и тот же код снова и снова.
Я обычно часто называю код как хранимый процесс или как объект SqlCommand (.NET) и выполняю столько раз, сколько необходимо.