Когда лучше писать "ad hoc sql" и хранимые процедуры

У меня есть 100% ad hoc sql через мое приложение. Мой приятель рекомендовал мне преобразовать в хранимые процедуры для дополнительной производительности и безопасности. Это вызвало у меня вопрос, помимо скорости и безопасности есть ли другая причина придерживаться специальных запросов sql?

Ответ 1

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

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

Полезно иметь такие запросы в таком центральном месте, в зависимости от того, сколько разных приложений нуждается в доступе к данным, которые они представляют. Как правило, мне гораздо проще сохранять запросы, используемые резидентом приложения, в самом коде приложения.

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

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

Обновление 2: я сделал больше исследований. Судя по этому техническому документу MSDN, ответ зависит от того, что именно вы подразумеваете под "специальными" запросами. Например, простой запрос, подобный этому:

SELECT ID, DESC FROM tblSTUFF WHERE ITEM_COUNT > 5

... будет иметь план выполнения в кэше. Более того, поскольку запрос не содержит определенных дисквалифицирующих элементов (как, например, почти все, кроме простого SELECT из одной таблицы), SQL Server фактически "автоматически параметризует" запрос и заменяет буквальную константу "5" параметром, а также кэш план выполнения для параметризованной версии. Это означает, что если вы затем выполните этот специальный запрос:

SELECT ID, DESC FROM tblSTUFF WHERE ITEM_COUNT > 23

... он сможет использовать кэшированный план выполнения.

К сожалению, список дисквалифицирующих элементов запроса для автоматической параметризации является длинным (например, забудьте об использовании DISTINCT, TOP, UNION, GROUP BY, OR и т.д.), Поэтому вы действительно не можете рассчитывать на это для производительности.

Если у вас есть "супер сложный" запрос, который не будет автоматически параметризован, например:

SELECT ID, DESC FROM tblSTUFF WHERE ITEM_COUNT > 5 OR ITEM_COUNT < 23

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

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

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

int itemCount = 5;
string query = "DELETE FROM tblSTUFF WHERE ITEM_COUNT > " + 
        itemCount.ToString();

это неверно. Правильный путь (с использованием ADO.Net) будет выглядеть примерно так:

using (SqlConnection conn = new SqlConnection(connStr))
{
    SqlCommand com = new SqlCommand(conn);
    com.CommandType = CommandType.Text;
    com.CommandText = 
        "DELETE FROM tblSTUFF WHERE ITEM_COUNT > @ITEM_COUNT";
    int itemCount = 5;
    com.Parameters.AddWithValue("@ITEM_COUNT", itemCount);
    com.Prepare();
    com.ExecuteNonQuery();
}

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

Наконец, если под "специальными" запросами вы подразумеваете, что вы динамически создаете запросы с разными столбцами, таблицами, параметрами фильтрации и тому подобным, например, вот они:

SELECT ID, DESC FROM tblSTUFF WHERE ITEM_COUNT > 5

SELECT ID, FIRSTNAME, LASTNAME FROM tblPEEPS 
    WHERE AGE >= 18 AND LASTNAME LIKE '%What the'

SELECT ID, FIRSTNAME, LASTNAME FROM tblPEEPS 
    WHERE AGE >= 18 AND LASTNAME LIKE '%What the'
    ORDER BY LASTNAME DESC

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

Обновление 3: Вот единственная действительно хорошая причина, связанная с производительностью (в любом случае, я могу придумать) для использования хранимой процедуры. Если ваш запрос является долгосрочным, когда процесс составления плана выполнения занимает значительно больше времени, чем фактическое выполнение, и запрос вызывается только нечасто (например, ежемесячный отчет), то помещение его в хранимую процедуру может заставить SQL Server хранить скомпилированный план в кеше достаточно долго, чтобы он оставался в следующем месяце. Бьет меня, правда это или нет.

Ответ 2

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

Введите код, который вы найдете наиболее продуктивным.

"Сделайте это прямо, прежде чем сделать это быстрее". - Брайан Керниган

Ответ 3

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

Ответ 4

Есть несколько мифов, связанных с этой темой, которые вы должны устранить:

Миф 1: Сохраненные процедуры предварительно скомпилированы
http://scarydba.wordpress.com/2009/09/30/pre-compiled-stored-procedures-fact-or-myth/

Миф 2: специальные SQL-запросы не повторяют планы выполнения: http://scarydba.wordpress.com/2009/10/05/ad-hoc-queries-dont-reuse-execution-plans-myth-or-fact/

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

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

Оба являются столь же безопасными в отношении SQL-инъекции, если все введенные пользователем значения параметризуются.

Ответ 5

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

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

Относительно раундов:

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

Ответ 6

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

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

Ответ 7

У меня есть 100% ad hoc sql через мой выражение. Мой приятель рекомендуется конвертировать в процедуры для дополнительной производительности и безопасность.

Я бы не стал беспокоиться о производительности, пока не появятся фактические точки боли. Например, кто-то использует ваше приложение и жалуется, что он замедляется. Пока вы не достигнете этого момента, ваше время лучше потратить на улучшение вашего приложения.

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

Ответ 8

Я не вижу, когда запросы Ad-Hoc дадут какие-либо преимущества. Обсуждая с другом тот же вопрос, мы обнаружили следующее в пользу хранимых процедур (кроме очевидных проблем с кешированием/SQL-инъекциями):

1) Кодировка кода: если у вас есть какой-то волосатый код SQL, встроенный в ваше приложение, становится намного труднее читать/понимать. Гораздо проще иметь хорошую именованную конвенцию для хранимых процедур, которая точно определяет, что она делает, а не много кода. Это менее те же принципы, которые мы пытаемся использовать при рефакторинге.

2) Возможность улучшать ваше приложение без повторного развертывания: если вам нужно настроить свою логику из-за ошибок или низкой производительности, наличие SQL-кода, встроенного в ваше приложение, подразумевает, что вам необходимо его повторно развернуть (даже если ваш набор данных не использует " т). Если у вас есть это в хранимой процедуре, единственной вещью, которую необходимо переупаковать, является процедура. Кроме того, он дает изменения в DBA, чтобы сделать свою магию, улучшая общую производительность запроса, работая с этой процедурой. Это будет намного сложнее сделать, если вы работаете со встроенной версией.

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

Надеюсь, это поможет.

Приветствия, Вагнер.

Ответ 9

Мой ответ может быть слегка отключен от темы, но в любом случае:

Это может быть полезно при работе с представлениями. Например, Hibernate (который вы не используете) имеет довольно плохую поддержку просмотров. Когда мне нужно запросить представление с ним, я всегда использую raw SQL, поскольку это единственное, что работает.

Ответ 10

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

DECLARE @param varchar(50)
SELECT @param = ?
SELECT * FROM TABLE WHERE NAME = @param

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

Сложный SQL с несколькими функциями или курсорами может испортиться, если он используется ad-hoc, поскольку некоторые драйверы ODBC не знают, как обрабатывать несколько запросов запроса.
Отделяет бизнес-логику от вызовов базы данных. Это позволяет (разработчику приложения) ничего не знать о структуре базы данных и все еще разрабатывать ваше приложение, в то время как выделенный разработчик баз данных или SQL может точно настроить SQL.
Сохраненные процедуры предварительно скомпилированы, поэтому в течение многих повторений они будут быстрее, но ТОЛЬКО, если они вызываются очень часто (т.е. программа мониторинга)

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

Надеюсь, что это поможет

Ответ 11

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

Ответ 12

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

Точка для Ad-Hoc SQL:

Существует случай, когда Ad-Hoc SQL менее болезненна (изначально), что связано с большим (и динамическим) диапазоном параметров поиска. Скажем, например, у вас есть поиск по учетным записям и дополнительный расширенный поиск по счету, который содержит в 3 раза больше параметров. Некоторые из полей просты (Account #); другие могут быть очень болезненными (поиск подстроки в адресной строке 1!) И в худшем случае большинство параметров не требуются.

Это делает настройку производительности не тривиальной. В этом случае, имея так много комбинаций параметров, кэширование плана выполнения (мой опыт на MS SQL) будет иметь неприятные последствия. План выполнения, скажем, для предоставления учетной записи № и только для продавца № 1, потенциально может быть очень плохим для другого набора поиска, обеспечивая адресный поиск и бренды, которые они несут. Ad-Hoc SQL будет иметь побочный эффект перекомпиляции на основе различных наборов параметров, таким образом обойти проблему кэширования плана выполнения.

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

Точка для хранимой процедуры:

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

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

Ответ 13

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

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

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

Ответ 14

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

Ответ 15

Это может зависеть от того, кто еще использует db. Если только одно приложение использует db, то параметризованные запросы имеют преимущество сидения в источнике.

Если другие приложения используют db, тогда dba должен поместить общие хранимые процедуры в db.

Ответ 16

Нет "правильного" ответа. Это зависит от каждой ситуации.

Кто-нибудь упоминал об этом? Хранимые процедуры обычно возвращают каждое поле, и невозможно создать один для каждого варианта полей, которые вы хотите. Ad-hoc позволяет указать только те, которые вы хотите. Однако, если вы используете какие-либо объекты (пользовательские объекты, EF и т.д.), Вы, вероятно, все равно вернете все поля.

Ответ 17

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