Когда это лучше для записи “специального sql” по сравнению с хранимыми процедурами [дубликат]
Этот вопрос уже имеет ответ здесь:
- Что является за и против к удержанию SQL, Сохраненный Procs по сравнению с Кодом [закрыл] 47 ответов
У меня есть 100%-й специальный sql через мое приложение. Мой приятель рекомендовал, чтобы я преобразовал в хранимые процедуры для дополнительной производительности и безопасности. Это подняло вопрос в моем уме помимо скорости, и безопасность является там какой-либо другой причиной придерживаться специальных запросов SQL?
17 ответов
SQL Server кэширует планы выполнения для специальных запросов, поэтому (без учета времени, затраченного на первый вызов) оба подхода будут идентичны с точки зрения скорости.
В общем, использование хранимых процедур означает взятие части кода, необходимого вашему приложению (запросы T-SQL), и помещение его в место, которое не находится под контролем источника (это может быть, но обычно не ) и где это может быть изменено другими без вашего ведома.
Размещение запросов в таком центральном месте, как это , может быть полезным, в зависимости от того, сколько различных приложений нуждаются в доступе к данным, которые они представляют. Я обычно считаю, что гораздо проще сохранить запросы, используемые приложением, резидентными в самом коде приложения.
В середине 1990-х годов общепринятое мнение гласило, что хранимые процедуры в SQL Server были способом использовать в ситуациях, критических для производительности, и в то время это определенно было. Однако причины этого CW давно не действовали.
Обновление: Кроме того, часто в дебатах о жизнеспособности хранимых процедур необходимость предотвращения SQL-инъекций вызывается для защиты процедур. Конечно, никто в здравом уме не думает, что сборка специальных запросов посредством конкатенации строк является правильным занятием (хотя это подвергнет вас атаке SQL-инъекции только в том случае, если вы конкатенируете пользовательский ввод ).Очевидно, что специальные запросы должны быть параметризованы не только для предотвращения чудовищной атаки sql-инъекций, но и просто для того, чтобы в целом упростить вашу жизнь как программиста (если вам не нравится выяснять, когда использовать одиночный цитаты вокруг ваших ценностей).
Обновление 2: Я провел дополнительное исследование. Основываясь на в этом техническом документе MSDN , кажется, что ответ зависит от того, что именно вы подразумеваете под «ad-hoc» в своих запросах. Например, простой запрос вроде этого:
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
...он по-прежнему будет кэшироваться точным текстом запроса, поэтому, если ваше приложение вызывает этот запрос несколько раз с одними и теми же буквальными «жестко запрограммированными» значениями, каждый запрос после первого будет повторно использовать кэшированный план выполнения (и, таким образом, будет таким, как быстро, как хранимая процедура).
Если литеральные значения изменяются (на основе действий пользователя, например, таких как фильтрация или сортировка просматриваемых данных), запросы не получат выгоды от кэширования (за исключением случаев, когда они случайно точно совпадают с последним запросом).
Чтобы извлечь выгоду из кэширования с помощью «специальных» запросов, нужно их параметризовать. Создание запроса на лету на C #, например:
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 хранить скомпилированный план в кеше достаточно долго, чтобы он оставался примерно в следующем месяце.Впрочем, лучше меня, правда это или нет.
Специальные запросы дают вам гибкость в логике вашего приложения, но вы почти всегда платите за производительность.
Если вас беспокоит производительность, я, вероятно, согласен с вашим другом, что вам следует изучить сохраненные процессы или какую-либо более статическую форму запроса, чтобы позволить базе данных «предварительно оптимизировать» запрос или разрешить слой кэширования (если он существует) для потенциального кэширования результатов запроса.
Если вы каждый раз генерируете запрос «на лету», база данных, скорее всего, вообще не сможет помочь вам с производительностью.
Ничего подобного хранимые процедуры, которые делают их волшебным образом быстрее или безопаснее. Бывают случаи, когда хорошо спроектированная хранимая процедура может быть быстрее для определенных типов задач, но обратное верно и для специального SQL.
Кодируйте так, как вы считаете наиболее продуктивным.
«Сделайте это правильно, прежде чем вы сделаете это быстрее». - Брайан Керниган
Это может зависеть от того, кто еще использует базу данных. Если только одно приложение использует базу данных, то параметризованные запросы имеют то преимущество, что они находятся в источнике.
Если другие приложения используют базу данных, она должна помещать в нее общие хранимые процедуры.
По правде говоря, внедрение SQL можно предотвратить путем параметризации ваших запросов (например, посмотрите параметры ODBCP), и ваши запросы могут быть построены так, что эти параметры не могут быть введены SQL. Например ...
DECLARE @param varchar(50)
SELECT @param = ?
SELECT * FROM TABLE WHERE NAME = @param
- это безопасный метод выполнения внутренних запросов с параметрами ODBC. Однако у использования хранимых процедур есть некоторые преимущества:
- Сложный SQL с несколькими функциями или курсорами может привести к ошибкам, если используется специальным образом, поскольку некоторые драйверы ODBC не знают, как обрабатывать множественные запросы запросов
- Это отделяет бизнес-логику от вызовов базы данных. Это позволяет вам (разработчику приложения) ничего не знать о структуре базы данных и при этом разрабатывать свое приложение, в то время как специализированный администратор баз данных или разработчик SQL может точно настроить SQL.
- Хранимые процедуры предварительно скомпилированы, поэтому при многократном повторении они будут быстрее, но ТОЛЬКО если они будут вызываться очень часто (например, программа мониторинга)
Когда все сказано и сделано, это дизайнерское решение. Не принимайте во внимание переносимость, вы можете просто попросить разработчика SQL дать вам сценарии для применения процедур магазина и в любом случае запускать их при установке программы :)
Надеюсь, это поможет
Есть пара мифов, связанных с этой темой, от которых вы должны избавиться:
Миф 1: Хранимые процедуры предварительно скомпилированы
http://scarydba.wordpress.com/ 2009/09/30 / pre-compiled-хранимые процедуры-факт-или-миф /
Миф 2: Специальные запросы SQL не используют повторно планы выполнения: http: //scarydba.wordpress.com / 2009/10/05 / ad-hoc-questions-dont-reuse-execution-sizes-ми-или-факт /
IMHO процессы имеют преимущество, когда вам абсолютно необходимо заблокировать базу данных. В этих ситуациях вы можете использовать учетную запись, которая имеет права только на выполнение хранимых процедур. Кроме того, они могут обеспечить уровень абстракции между вашим приложением и базой данных с точки зрения администратора базы данных.
Аналогичным образом, динамический SQL лучше подходит в ситуациях, когда в запросе может потребоваться что-то изменить и сделать ... ну ... динамическим. Или, если вы знаете, что вам нужно выполнить перенос на несколько баз данных.
Оба варианта столь же безопасны в отношении SQL-инъекции, если все вводимые пользователем значения параметризованы.
Я не вижу, когда специальные запросы могут дать какие-либо преимущества. Обсуждая с другом этот же вопрос, мы обнаружили следующее в пользу хранимых процедур (помимо очевидных проблем с кешированием / SQL-инъекцией):
1) Читаемость кода: если у вас есть какой-то сложный код SQL, встроенный в ваше приложение, он становится намного труднее читать / понимать. Гораздо проще иметь хорошее соглашение об именах для хранимых процедур, которое точно говорит, что они делают, вместо большого количества кода. Это более менее те же принципы, которые мы пытаемся использовать при рефакторинге.
2) Возможность улучшить ваше приложение без повторного развертывания: если вам нужно настроить свою логику из-за ошибок или низкой производительности, наличие кода SQL, встроенного в ваше приложение, подразумевает, что вам необходимо повторно развернуть его (даже если ваш набор данных не t изменить). Если он у вас есть в хранимой процедуре, единственное, что вам нужно повторно развернуть, - это процедуру. Кроме того, он дает изменения администратору базы данных, чтобы он мог творить чудеса, улучшая общую производительность запроса, работая с процедурой. Это будет намного сложнее сделать, если вы работаете со встроенной версией.
3) Сетевой трафик: если вы передаете много кода SQL, вы увеличиваете размер вашего сообщения (или вызовов RPC), передаваемого по сети, что может привести к снижению производительности из-за запросов. Особенно, если каждый раз к одному и тому же звонит много пользователей.
Надеюсь, это поможет.
Ура, Вагнер.
В моем приложении используется 100% специальный sql. Мой приятель порекомендовал мне преобразовать в хранимые процедуры для повышения производительности и безопасности.
Я не буду беспокоиться о производительности, пока не появятся реальные болевые точки. Например, кто-то использует ваше приложение и жалуется, что оно работает медленно. Пока вы не достигнете этого момента, лучше всего потратить время на улучшение приложения.
В сфере безопасности вы должны сбалансировать усилия и риск. Если ваш сайт не хранит ничего ценного, даже SQL-инъекция представляет собой вполне приемлемый риск, что доказано большим количеством веб-сайтов :)
Если вы не пишете хранимые процедуры, изучите параметризованные запросы . Если вы создаете SQL самостоятельно, включая конкатенацию параметров, вы вызываете атаку SQL-инъекцией .
Мой ответ может быть немного не по теме, но в любом случае:
Это может быть полезно при работе с представлениями. Например, Hibernate (который вы не используете) имеет довольно плохую поддержку представлений. Когда мне нужно запросить представление с его помощью, я всегда использую необработанный SQL, поскольку это единственное, что работает.
- Вам не нужно писать хранимую процедуру
- Обычно специальный SQL работает достаточно быстро. Вы можете использовать параметризованные запросы для увеличения скорости.
- Вы можете скомпилировать SQL на основе строк в "скомпилированный" SQL, а затем выполнить его, что намного быстрее.
- Обычно узким местом производительности для SQL являются запросы, не относящиеся ни к одному из вышеперечисленных.
Одним из преимуществ хранимых процедур является то, что данные не нужно передавать по сети для промежуточных вычислений. Если в результате множества вычислений получается одно значение, то хранимая процедура будет быстрее.
Переносимость, когда вам нужно переключиться на новый SQL-сервер и у вас нет доступа к старому.
Я работал над несколькими проектами, в которых исходный разработчик не давал доступ к серверу после того, как его уволили, поэтому нам пришлось самостоятельно переписывать многие запросы, потому что они были заблокированы в хранимых процедурах, что мы не сделали. нет разрешения на просмотр. Если бы все запросы были в исходном коде, это было бы намного проще.
В этой теме уже много говорилось о производительности, кэшировании и безопасности, и я не буду повторяться. Есть несколько вещей, которые я еще не прочитал в этой теме, это вопросы переносимости и перенаправления.
- Если вы заинтересованы в максимальной переносимости вашего приложения на разные языки программирования, то хранимые процедуры - хорошая идея: чем больше программной логики вы храните в базе данных вне приложения, тем меньше вам придется перекодировать при переходе на другой фреймворк или язык. Кроме того, код для вызова хранимой процедуры намного меньше, чем сам необработанный SQL, поэтому интерфейс базы данных в коде вашего приложения будет занимать меньше места.
- Если вам нужна одна и та же логика в нескольких приложениях, то хранимые процедуры - это удобная возможность иметь одно определение этой логики, которое может быть повторно использовано в других приложениях. Однако это преимущество часто преувеличивается, поскольку вы также можете изолировать эту логику в библиотеке, которая будет использоваться в разных приложениях. Конечно, если приложения написаны на разных языках, то хранимые процедуры действительно приносят пользу, поскольку, вероятно, проще вызвать процедуру через интерфейс db вашего языка, чем обращаться к библиотеке, написанной на другом языке.
- Если вы заинтересованы в переносимости РСУБД, то хранимые процедуры, скорее всего, станут одной из ваших самых больших проблем. Основные функции всех основных и второстепенных РСУБД довольно схожи. Наибольшие различия можно найти в синтаксисе и доступной встроенной функциональности для хранимых процедур.
Что касается обхода:
- Если в вашем приложении много многоэтапных транзакций или вообще функций, требующих выполнения нескольких SQL-запросов, то производительность может повыситься, если вы поместите эти несколько запросов в хранимую процедуру. Причина в том, что вызов хранимой процедуры (и, возможно, возврат из нее нескольких результатов) - это всего лишь один запрос. При использовании необработанного SQL у вас есть (по крайней мере) один цикл на каждый оператор SQL.
Говоря из своего опыта, я бы поставил точку преимущества для каждого метода.
Точка для Ad-Hoc SQL:
Есть случай, когда Ad-Hoc SQL менее болезненный (изначально), который включает большой (и динамический) диапазон параметров поиска. Скажем, например, у вас есть поиск по аккаунту и сопутствующий расширенный поиск по аккаунту, который содержит в 3 раза больше параметров. Некоторые из полей просты (Account #); другие могут быть очень болезненными (поиск подстроки в адресной строке 1!) А в худшем случае большинство параметров не требуются.
Это делает настройку производительности нетривиальной. В этом случае, имея так много комбинаций параметров, кэширование плана выполнения (мой опыт на MS SQL) приведет к обратному результату. План выполнения для, скажем, предоставления только счета # и продавца #, потенциально будет очень плохим для другого набора поиска, предоставляющего поиск адресов и брендов, которые они носят. Ad-Hoc SQL будет иметь побочный эффект перекомпиляции на основе различных наборов предоставленных параметров, таким образом обходя проблему кэширования плана выполнения.
Конечно, все вышесказанное можно сделать и с помощью хранимой процедуры, но для того, чтобы обойти проблему кэширования плана выполнения, вам придется запускать команду перекомпиляции внутри хранимой процедуры. Можно также утверждать, что динамическая конструкция SQL может быть помещена в хранимую процедуру, но это просто перемещение Ad-Hoc SQL в другое место; все равно Ad-Hoc SQL.
Точка зрения на хранимую процедуру:
Хранимые процедуры можно рассматривать как API. Если вы работали с корпоративными средами, есть вероятность, что различные приложения будут делать совершенно одинаковые вещи. Например, в таблицу событий могут быть вставлены данные из различных программ, включая бухгалтерский учет, продажи, аудит, управление взаимоотношениями с клиентами и т.д. Эти программы могут поддерживаться разными разработчиками. Эти программы могут обслуживаться не одной и той же группой людей (например, разными подразделениями), но в конечном итоге они будут иметь доступ к одной и той же базовой базе данных.
В этом случае использование Ad-Hoc SQL будет кошмаром управления исходным кодом, поскольку это приведет к появлению нескольких версий Ad-Hoc SQL, выполняющих одну и ту же функциональность, причем каждая версия может иметь различные побочные эффекты. В настоящее время я имею дело с такой ситуацией, и это не очень весело. Хранимые процедуры в этом случае можно использовать повторно, таким образом, имея централизованное управление кодами базы данных.
Нет "правильного" ответа. Это зависит от каждой ситуации.
Кто-нибудь упоминал об этом? Хранимые процедуры обычно возвращают каждое поле, и невозможно создать по одному для каждого желаемого варианта полей. Ad-hoc позволяет указать только те, которые вам нужны. Однако, если вы используете какие-либо сущности (настраиваемые объекты, EF и т. Д.), Вы, вероятно, все равно вернете все поля.
Вероятно, преимущества в производительности не будет, но для удобства обслуживания вы можете рассмотреть возможность использования чего-то вроде LINQ2SQL, чтобы избежать синтаксических ошибок в вашем SQL.