Традиционная мудрость гласит, что хранимые процедуры всегда быстрее. Итак, поскольку они всегда быстрее, используйте их ВСЕ ВРЕМЯ.
Я уверен, что это обосновано в каком-то историческом контексте, где это было когда-то. Теперь я не сторонник того, что Хранимые Procs не нужны, но я хочу знать, в каких случаях хранимые procs необходимы в современных базах данных, таких как MySql, SqlServer, Oracle или. Препятствует ли ему ВСЕ доступ через хранимые процедуры?
ПРИМЕЧАНИЕ, что это общий взгляд на хранимые процедуры, не регулируемые конкретным СУБД. Некоторые СУБД (и даже разные версии одной и той же СУБД!) могут работать вопреки этому, так что вы захотите двойная проверка с вашей целевой СУБД прежде чем считать, что все это сохраняется.
Я уже почти десять лет являюсь администратором баз данных ASE, MySQL и SQL Server Sybase, а также разработкой приложений на C, PHP, PL/SQL, С#.NET и Ruby. Итак, у меня нет особого топора, чтобы размолоть эту (иногда) святую войну.
Историческое преимущество производительности хранимых процессов обычно происходит от следующего (в определенном порядке):
Предварительно проанализированный SQL - аналогичные преимущества для скомпилированного или интерпретированного кода, за исключением очень микроуровня.
Еще преимущество? Не очень заметно вообще на современном процессоре, но если вы отправляете один оператор SQL, который ОЧЕНЬ большой одиннадцать миллиардов раз в секунду, накладные расходы синтаксического анализа могут складываться.
Предварительно сгенерированный план выполнения запросов. Если у вас много JOINs, перестановки могут стать совершенно неуправляемыми (современные оптимизаторы имеют ограничения и ограничения по производительности). Неизвестно, что очень сложный 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" может действительно скомпоноваться. B) Сохраненные procs могут использоваться для перемещения процедурного кода в СУБД. Это избавляет от переполнения большого количества данных клиенту только для того, чтобы он посылал информацию об обратной связи (или вообще ничего!). Аналогично выполнению JOIN в СУБД и в вашем коде (все любимые WTF!)
Еще преимущество? A) Современный Ethernet 1Gb (и 10Gb и выше!) Действительно делает это незначительным. B) В зависимости от того, насколько насыщена ваша сеть - зачем выталкивать несколько мегабайт данных туда и обратно без уважительной причины?
Потенциальные преимущества кеша Выполнение серверных преобразований данных может быть более быстрым, если у вас достаточно памяти в СУБД, а необходимые вам данные - в памяти сервера.
Еще преимущество? Если ваше приложение не использует общий доступ к данным СУБД, кромка всегда будет храниться в procs.
Конечно, обсуждение оптимизации хранимой процедуры не было бы полным без обсуждения параметризованного и специального SQL.
Параметрированный/подготовленный SQL
Например, между хранимыми процедурами и специальным SQL, они являются встроенными операциями SQL на языке хоста, который использует "параметры" для значений запроса, например:
SELECT .. FROM yourtable WHERE foo = ? AND bar = ?
Они предоставляют более обобщенную версию запроса, который современные оптимизаторы могут использовать для кэширования (и повторного использования) плана выполнения запросов, в результате чего большая часть преимуществ производительности хранимых процедур.
Специальный SQL Просто откройте окно консоли в вашей СУБД и введите инструкцию SQL. Раньше это были "худшие" исполнители (в среднем), поскольку СУБД не имела возможности предварительно оптимизировать запросы, как в параметризованном/сохраненном методе proc.
Еще недостаток? Не обязательно. Большинство СУБД имеют возможность "абстрагировать" специальный SQL в параметризованных версиях, тем самым более или менее отрицая разницу между ними. Некоторые делают это неявно или должны быть включены с помощью параметра команды (SQL-сервер: http://msdn.microsoft.com/en-us/library/ms175037.aspx, Oracle: <а4 > ).
Извлеченные уроки? Закон Мура продолжает продвигаться, и оптимизаторы СУБД с каждым выпуском становятся более изощренными. Конечно, вы можете разместить каждый глупый подросток SQL-запроса внутри хранимой процедуры, но просто знайте, что программисты, работающие над оптимизаторами, очень умны и постоянно ищут способы повысить производительность. В конце концов (если это уже не так) ad hoc производительность SQL станет неотличимой (в среднем!) От производительности хранимой процедуры, поэтому любое использование массивной хранимой процедуры ** исключительно по "причинам производительности" ** обязательно звучит как преждевременная оптимизация для меня.
В любом случае, я думаю, что если вы избегаете крайних случаев и имеете довольно ванильный SQL, вы не заметите разницы между специальными и хранимыми процедурами.
Во многих случаях хранимые процедуры на самом деле медленнее, потому что они более genaralized. Хотя хранимые процедуры могут быть сильно настроены, по моему опыту достаточно развития и институционального трения, что они оставлены на месте после их работы, поэтому хранимые процедуры часто имеют тенденцию возвращать много столбцов "на всякий случай" - потому что вы не хотите развернуть новую хранимую процедуру каждый раз, когда вы меняете свое приложение. OR/M, с другой стороны, запрашивает только те столбцы, которые использует приложение, что сокращает сетевой трафик, ненужные соединения и т.д.
Причины использования хранимых процедур:
Это дискуссия, которая бушует и продолжается (например, здесь).
Это так же просто писать плохие хранимые процедуры, так как это означает, что в вашем приложении есть логика плохого доступа к данным.
Я предпочитаю хранимые процедуры, но это потому, что я обычно работаю с очень большими и сложными приложениями в корпоративной среде, где есть выделенные администраторы баз данных, которые несут ответственность за поддержание слайдов на серверах баз данных.
В других ситуациях я достаточно доволен технологиями доступа к данным, такими как LINQ, чтобы заботиться об оптимизации.
Чистая производительность - не единственное соображение. Такие аспекты, как безопасность и управление конфигурацией, как правило, не менее важны.
Изменить: В то время как статья Франса Бумы действительно многословна, она пропускает точку в отношении безопасности на милю. Тот факт, что ему 5 лет, также не помогает его релевантности.
Нет заметной разницы в скорости для хранимых процедур по сравнению с параметризованными или подготовленными запросами в большинстве современных баз данных, так как база данных также кэширует планы выполнения для этих запросов.
Обратите внимание, что параметризованный запрос не совпадает с специальным sql.
Основная причина того, что сегодня по-прежнему предпочитают хранимые процедуры, в большей степени связана с безопасностью. Если вы используете исключительно хранимые процедуры, вы можете отключить разрешения INSERT, SELECT, UPDATE, DELETE, ALTER, DROP и CREATE и т.д. Для вашего пользователя приложения, оставив его только с EXECUTE.
Это обеспечивает небольшую дополнительную защиту от SQL-инъекции 2-го порядка. Параметрированные запросы защищают только от впрыска 1-го порядка.
Очевидно, что фактические показатели следует измерять в отдельных случаях, а не предполагать. Но даже в тех случаях, когда производительность затруднена хранимой процедурой, есть веские причины для их использования:
Разработчики приложений не всегда являются лучшими SQL-кодами. Хранимые процедуры скрывают SQL из приложения.
Сохраненные процедуры автоматически используют переменные связывания. Разработчики приложений часто избегают связывать переменные, потому что они кажутся ненужным кодом и мало помогают в небольших тестовых системах. Позже отказ использования переменных связывания может дросселировать производительность РСУБД.
Хранимые процедуры создают слой косвенности, который может быть полезен позже. Можно изменить детали реализации (включая структуру таблицы) на стороне базы данных, не касаясь кода приложения.
Упражнение по созданию хранимых процедур может быть полезно для документирования всех взаимодействий базы данных для системы. И упростить обновление документации при изменении вещей.
Тем не менее, я обычно использую raw SQL в своих приложениях, чтобы я мог сам управлять этим. Это зависит от вашей команды разработчиков и философии.
Одна тема, которую никто еще не упомянул в качестве преимущества хранимых процедур, - это безопасность. Если вы создаете приложение исключительно с доступом к данным через хранимые процедуры, вы можете заблокировать базу данных, чтобы ТОЛЬКО доступ через эти хранимые процедуры. Поэтому, даже если кто-то получает идентификатор базы данных и пароль, они будут ограничены тем, что они могут видеть или делать с этой базой данных.
Я предпочитаю использовать SP, когда имеет смысл использовать их. В SQL Server в любом случае нет преимущества производительности для SP по параметризованному запросу.
Однако на моей нынешней работе мой босс упомянул, что мы вынуждены использовать SP, потому что их клиент требует от них. Они чувствуют, что они более безопасны. Я не был здесь достаточно долго, чтобы понять, реализуем ли мы безопасность на основе ролей, но я чувствую, что мы делаем.
Таким образом, чувства клиента вызывают все другие аргументы в этом случае.
В 2007 году я был в проекте, где мы использовали MS SQL Server через ORM. У нас было 2 больших стола, которые занимали до 7-8 секунд времени загрузки на SQL Server. После создания двух больших хранимых процедур SQL и оптимизации их из планировщика запросов каждое время загрузки БД сократилось до менее 20 миллисекунд, поэтому, очевидно, по-прежнему существуют соображения эффективности использования хранимых процедур SQL.
Сказав это, мы выяснили, что наиболее важным преимуществом хранимых процедур является добавленная поддержка упрощения, безопасности, целостности данных и развязки бизнес-логики из логики промежуточного программного обеспечения, приносящая пользу всей промежуточной логике от повторного использования 2 процедуры.
Наш поставщик ORM сделал обычное утверждение, что отключение многих небольших запросов SQL будет более эффективным, чем выбор больших объединенных наборов данных. Наш опыт (к нашему удивлению) показал что-то еще.
Это может, конечно, варьироваться между машинами, сетями, операционными системами, серверами SQL, инфраструктурами приложений, структурами ORM и языковыми реализациями, поэтому измерьте любую выгоду, вы ДУМАЕТЕ, что вы можете сделать что-то еще.
Только после того, как мы провели сравнительный анализ, мы обнаружили, что проблема была между ORM и базой данных, принимающей всю нагрузку.
Прочитайте Frans Bouma отличный пост (если он немного предвзято).
Все, что я могу говорить, это SQL-сервер. В этой платформе хранимые процедуры прекрасны, потому что сервер хранит план выполнения, который в большинстве случаев ускоряет работу до хорошего. Я говорю "в большинстве случаев", потому что, если SP имеет широко варьируемые пути выполнения, вы можете получить субоптимальную производительность. Однако даже в тех случаях некоторые просвещенные рефакторинги SP могут ускорить процесс.
Для меня одно преимущество хранимых процедур - быть агностиком хост-языка: вы можете переключиться с C, Python, PHP или любого другого приложения на другой язык программирования, не переписывая свой код. Кроме того, некоторые функции, такие как массовые операции, действительно улучшают производительность и недоступны (не на всех?) На языках хостов.
Я не знаю, что они быстрее. Мне нравится использовать ORM для доступа к данным (чтобы не изобретать колесо), но я понимаю, что не всегда жизнеспособный вариант.
У Франса Бумы есть хорошая статья на эту тему: http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
Сохраненные procs отлично подходят для случаев, когда код SQL запускается часто, потому что база данных хранит его токенизированную в памяти. Если вы повторно запускали один и тот же код за пределами сохраненного proc, вы, вероятно, получите удар производительности из базы данных, повторно использующий один и тот же код снова и снова.
Я обычно часто называю код как хранимый proc или как объект SqlCommand (.NET) и выполняю столько раз, сколько необходимо.
Использование хранимых процедур для операций CRUD, вероятно, слишком велико, но это будет зависеть от используемых инструментов и ваших собственных предпочтений (или требований). Я предпочитаю встроенный SQL, но я обязательно использую параметризованные запросы для предотвращения атак SQL-инъекций. Я сохраняю распечатку этого xkcd comic как напоминание о том, что может пойти не так, если вы не будете осторожны.
Сохраненные процедуры могут иметь реальные преимущества при работе с несколькими наборами данных для возврата одного набора данных. Обычно более эффективно обрабатывать наборы данных в хранимой процедуре, чем отправлять их по проводу, который будет обрабатываться на стороне клиента.
Да, они быстрее всего времени. Состав SQL также является большой областью настройки производительности. Если я делаю приложение типа back office, я могу пропустить их, но все, что связано с продукцией, я использую их наверняка по всем причинам, о которых говорили другие, а именно: безопасность.
Понимая, что это немного не по теме, но если вы используете много хранимых процедур, убедитесь, что существует последовательный способ их подставить под каким-то контролем источника (например, subversion или git ) и сможете перенести обновления из вашей системы разработки в тестовую систему в производственную систему.
Когда это делается вручную, без возможности легко проверить, что такое код, это быстро становится кошмаром.
ИМХО...
Ограничение операций "C_UD" для хранимых процедур может поддерживать логику целостности данных в одном месте. Это также можно сделать, ограничив операции "C_UD" на один средний слой.
Операции чтения могут предоставляться приложению, поэтому они могут присоединяться только к таблицам/столбцам, которые им нужны.
Сохраненные процедуры также могут использоваться вместо параметризованных запросов (или специальных запросов) для некоторых других преимуществ:
Уменьшенный сетевой трафик - SP, как правило, хуже, чем Dynamic SQL. Поскольку люди не создают новый SP для каждого выбора, если вам нужен только один столбец, вам предлагается использовать SP, в котором есть нужные столбцы, и игнорировать остальные. Получите дополнительную колонку и меньшее сетевое использование, которое вы только что ушли. Кроме того, вы, как правило, имеете большую фильтрацию клиентов при использовании SP.
кэширование - MS-SQL не относится к ним иначе, не так как MS-SQL 2000 может быть 7, но я не помню.
Разрешения. Не проблема, поскольку почти все, что я делаю, это веб-интерфейс или средний уровень приложений, который обеспечивает доступ к базе данных. Единственное программное обеспечение, с которым я работаю, с прямым клиентом доступа к базе данных - это сторонние продукты, предназначенные для прямого доступа пользователей и основанные на предоставлении пользователям разрешений. И да MS-SQL разрешительная модель безопасности SUCKS!!! (еще не потратили время на 2008 год). В качестве заключительной части этого хотелось бы посмотреть опрос о том, сколько людей по-прежнему выполняет прямое клиент-серверное программирование против веб-и среднего программирования сервера приложений; и если они делают большие проекты, почему нет ORM.
Разделение - люди задавали вопрос, почему вы вводите бизнес-логику вне среднего уровня. Также, если вы ищете отдельный код обработки данных, есть способы сделать это, не помещая его в базу данных.
Возможность редактирования - что у вас нет тестирования и контроля версий, о которых вы должны беспокоиться? Также проблема только с клиентом/сервером, в веб-мире не проблема.
Найти таблицу - только если вы можете идентифицировать SP, который ее использует, вы будете использовать инструменты системы управления версиями, агента ransack или visual studio для поиска.
Оптимизация. Ваш администратор базы данных должен использовать инструменты базы данных для поиска запросов, требующих оптимизации. База данных может сообщить администратору базы данных, какие заявления говорят больше всего времени и ресурсов, и они могут исправить оттуда. Для сложных операторов SQL программистам следует попросить поговорить с администратором баз данных, если простые варианты не беспокоятся об этом.
Атаки SQL-инъекций - SP не обеспечивают лучшей защиты. Единственное, что им нравится, это то, что большинство из них обучают использованию параметров vs dynamic SQL. Большинство примеров игнорируют параметры.