Джефф Этвуд написал об этом здесь, и, хотя я понимаю теоретическое повышение производительности, которое может предложить хранимая процедура, это похоже на потрясающий боль.
В каких типах запросов вы увидите наибольшее увеличение производительности с помощью хранимых процедур и какие типы запросов вы бы просто построили на лету?
Любая документация, так или иначе, будет очень благодарна.
Сохраненный аргумент proc/no stored procs стал религиозной проблемой. Для каждого человека, который подчеркивает оптимизированные планы выполнения для procs, другой указывает, что общие динамические запросы кэшируются и оптимизируются в большинстве современных СУБД. Для тех, кто указывает на безопасность, которую может предложить proc, другой поясняет, что динамические запросы могут быть сделаны столь же безопасными. Некоторым нравится гибкость изменения proc без повторной компиляции вашего приложения, в то время как другие утверждают, что запросы должны быть зафиксированы в коде приложения, чтобы они могли жить и расти в одной и той же базе кода.
Я говорю...
Сделайте то, что вам нравится. Я сомневаюсь, что мы сможем найти правильный ответ. Если procs - хлопот, не используйте их. Если они кажутся хорошей идеей, пойдите для этого. Я работал с обоими моделями, и я честно не имею предпочтения. Я продуктивен с или без них.
В прошедшие дни были значительные выгоды от использования хранимых процедур, но повторное использование плана запроса теперь значительно лучше, так что во многих случаях они почти одинаковы.
Если вы создаете динамический SQL на сервере, вы можете еще больше увеличить повторное использование плана запроса (и безопасность безопасности) с помощью sp_ExecuteSQL (а не только EXEC) для выполнения SQL.
Есть несколько преимуществ использования хранимых процедур:
Однако для SQL также есть преимущества:
Некоторая вещь, где я определенно использую SP, находится в (например) шаге переноса данных, то есть для массовой операции я могу использовать SqlBulkCopy для перемещения данных в промежуточную таблицу, затем хранимую процедуру для перемещения данных над. Кроме того, я довольно гибкий.
Как и в случае с программным обеспечением, проблема кэширования SQL более тонкая, чем кажется. Например, рассмотрим кэширование запроса ad-hoc SQL:
-- First, clear the cache
DBCC FREEPROCCACHE
-- Look at what executable plans are in cache
SELECT sc.*
FROM master.dbo.syscacheobjects AS sc
WHERE sc.cacheobjtype = 'Executable Plan'
-- Execute the following statement
SELECT t.*
FROM pubs.dbo.titles AS t
WHERE t.price = 19.99
-- Look at what executable plans are in cache and you'll
-- find that there a plan for a NUMERIC(4,2)
SELECT sc.*
FROM master.dbo.syscacheobjects AS sc
WHERE sc.cacheobjtype = 'Executable Plan'
-- If you execute the EXACT same statement with a 4,2
-- then you will get THAT plan. But if you execute with a 5,2
-- then you'll get a new plan. Try this:
SELECT t.*
FROM pubs.dbo.titles AS t
WHERE price = 199.99
-- Look again at the cached executable plans, and you'll see a NEW one...
SELECT sc.*
FROM master.dbo.syscacheobjects AS sc
WHERE sc.cacheobjtype = 'Executable Plan'
Однако вы можете использовать sp_executesql для ввода параметров и принудительного кэширования плана. Все последующие использования получат один и тот же план, но некоторым людям не нравится неясность такого подхода:
DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = N'SELECT t.* FROM dbo.titles AS t WHERE t.price = @price'
EXEC sp_executesql @ExecStr, N'@price money', 19.99
Теперь, если вы создаете аналогичный запрос как хранимую процедуру с параметром цены, план будет создан и кэширован в памяти (а не на диске) при первом выполнении и повторно использован независимо от значение параметра.
Тот факт, что план хранимой процедуры кэшируется в памяти, а не на диске, означает, что он выпадет из кэша при перезапуске сервера или из-за низкого повторного использования. Он также может выпадать из кеша, если данные, на которые зависит эта процедура, достаточно для того, чтобы статистика была недействительной. Это приводит к тому, что SQL Server делает недействительным план.
Мы обнаружили, что сохранение хранимых процедур - это то, что когда-то первоначально созданный разработчиком, они могут быть переданы экспертам DBA или Database Tuning для лучшего написания по соображениям производительности, если это необходимо.
DBA, очевидно, не нуждается в доступе к коду приложения для этого и может работать вместе с ожидающим выпуском приложения.
Конечно, то же самое относится к встроенным или встроенным SQL-запросам, но я считаю, что хранимые процедуры лучше подходят для этого кооперативного, более крупного командного режима работы.
Наибольшие преимущества хранимых процедур, на мой взгляд, следующие:
1/Они централизованы в системе управления базами данных, поэтому администраторы баз данных точно знают, что они делают, и могут оптимизировать базу данных в соответствии с ними. Специальные запросы от клиентов гораздо сложнее сделать это. Я видел базы данных, купленные на колени, потому что администраторы баз данных допускают неограниченные запросы. Они также заставляют клиентов думать о том, что им нужно.
2/Они могут выполнять очень сложную обработку на сервере, сводя к минимуму количество данных, отправленных по проводу. Это избавляет приложения от получения ведровой информации, принятия решения о ней, получения большего количества данных и т.д.
В любом случае, разрешите любые случайные запросы во время разработки, но перед тем, как приступить к производству, определите, какие действия БД следует перенести на сохраненные procs.
Являясь неполным администратором баз данных, я думаю, что это означает, что все они, но это не обязательно, если вы сохраняете контроль над тем, какие запросы могут отправлять приложения.
Франс Бума вызвал волну некоторое время назад с этой темой.
В SQL Server более важно понять, что любое увеличение производительности связано с тем, что кеш-запрос кэшируется. План запроса довольно дорог для компиляции, поэтому имеет смысл кэшировать их. EDIT: Ad-hoc SQL с меньшей вероятностью будет сопоставляться с кэшированным планом запросов. Динамический SQL может быть кэширован, если он запускается при вызове sp_executesql.
Если вы используете EXEC для запуска SQL, например'EXEC @dynamic_sql ', он будет кэшировать план, но не будет параметризовать (или нормализовать его), что означает, что если точно такой же запрос не будет отправлен, он не будет повторно использовать кэшированный плана запросов.
Хранимые процедуры, обеспечивающие преимущество в производительности, являются мифом, хранящимся в предыдущих версиях SQL Server. SQL Server > версия 7 рассматривает все запросы одинаково и кэширует планы выполнения для всех часто используемых запросов, независимо от их генезиса.
Интересно, сколько еще лет этот миф будет жить.
Одна из идей хранимой процедуры заключается в том, что она позволяет вам делать несколько вещей за одну поездку в базу данных.
MSSQL позволяет отправлять партии запросов в один запрос; это примерно эквивалентно.
Однако принцип тот же. В широкомасштабной распределенной системе задержка в базе данных (а не во время запроса на сервере) может быть проблемой производительности. Выдача меньшего количества запросов может принести пользу.
Таким образом, использование пакетов запросов или хранимых процедур против выдачи одиночных запросов является потенциальной победой в распределенной системе.
Идея хранения хранимых процедур выполняется быстрее, так это то, что они могут хранить кэшированные планы запросов, вроде предварительной компиляции хранимой процедуры. Поэтому, если процедура очень сложная, с большим количеством объединений между множеством таблиц, вы можете увидеть преимущество в скорости.
Я думаю, что сохраненные procs нужны больше, чем их скорость, они помогают абстрагироваться от схемы базы данных и помогают предотвратить атаки SQL-инъекций.
Плохо написанная хранимая процедура не даст повышения производительности. Точно так же, как плохой встроенный запрос будет иметь худшую производительность, чем хорошо продуманный.
Одна вещь, которую люди пропускают, состоит в том, что все эти хранимые процедуры CRUD заполнены coalesce, и если утверждения, и это очень медленно.
Если вы используете параметризованные операторы против коалесценции, используя хранимую процедуру, динамические утверждения собираются выиграть.