Хранимые процедуры действительно повышают производительность в MS SQL/.NET?

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

при создании динамического SQL в сервере можно далее увеличить повторное использование плана запросов (и инжекционная безопасность) при помощи sp_ExecuteSQL (а не просто ДОЛЖНОСТНОЕ ЛИЦО) для выполнения SQL.

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

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

Однако существуют преимущества для SQL также:

• Это позволяет инструментам ORM (LINQ, например) писать компонуемые запросы (страница 4, заказанная Нечто, Панелью) на лету
• , Это позволяет Вам писать динамический код на более выразительном языке (TSQL действительно не предназначается, чтобы использоваться для записи TSQL!)
• у Вас обычно есть лучшие инструменты

Некоторая вещь, где я определенно использовал бы SP, находится на (например), шаге миграции данных - т.е. для объемной операции я мог бы использовать SqlBulkCopy, чтобы продвинуть данные в таблицу подготовки, затем хранимая процедура перемещать данные. Кроме того, я довольно гибок.

Как распространено с программным обеспечением, проблема кэширования SQL является более тонкой, чем это смотрит. Например, давайте посмотрим на кэширование специальный запрос 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's 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 делать недействительным план.

Самые большие преимущества хранимых процедур, по-моему:

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

2/Они могут сделать очень сложную обработку в сервере, минимизирующем объем данных, отправленный через провод. Это сохраняет приложения от получения bucketload данных, решение чего-то от него, затем получение большего количества данных, и так далее.

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

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

Хранимые процедуры, обеспечивающие выигрыш в производительности, являются мифом, отложенным от более ранних версий SQL Server. SQL Server> версия 7 рассматривает все запросы то же и будет кэшировать планы выполнения относительно всех наиболее часто используемых запросов, неважно, их происхождение.

интересно, на насколько большем количестве лет этот миф будет жить.

Frans Bouma вызвал движение некоторое время назад с этой темой.

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

при использовании ДОЛЖНОСТНОГО ЛИЦА для выполнения SQL, например.'EXEC @dynamic_sql', он будет кэшировать план, но не параметризует (или нормализует его), означая, что, если точно тот же запрос не будет отправлен, он не снова использует кэшируемый план запросов.

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

DBA, очевидно, не нуждается в доступе к коду приложения, чтобы сделать это и может работать вместе с незаконченным выпуском приложения.

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

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

MSSQL позволяет пакетам запроса быть отправленными в единственном запросе; это примерно эквивалентно.

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

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

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

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

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

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

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