什么时候最好编写＆QUOT;即席SQL＆QUOT; VS存储过程 [英] When is it better to write "ad hoc sql" vs stored procedures
问题描述
我要通过我的应用程序100％即席SQL。我的一个朋友建议我转换为额外的性能和安全性的存储过程。这提出了一个问题，在我的心中，除了速度和安全性没有任何其他理由而坚持使用即席SQL查询？
I have 100% ad hoc sql through out my application. A buddy of mine recommended that I convert to stored procedures for the extra performance and security. This brought up a question in my mind, besides speed and security is there any other reason to stick with ad hoc sql queries?
推荐答案
SQL服务器缓存的即席查询执行计划，所以（扣除第一次调用所花费的时间），这两种方法将是相同的在速度方面
SQL Server caches the execution plans for ad-hoc queries, so (discounting the time taken by the first call) the two approaches will be identical in terms of speed.
在一般情况下，使用存储过程意味着要通过你的应用程序（T-SQL查询）所需的code的一部分，并把它在一个地方，是不是源代码管理下（它的即可的是，但通常的不的），并在那里可以被他人不知情的情况下进行更改。
In general, the use of stored procedures means taking a portion of the code needed by your application (the T-SQL queries) and putting it in a place that is not under source control (it can be, but usually isn't) and where it can be altered by others without your knowledge.
在有这样一个中心位置查询的可能的是一件好事，这取决于许多不同的应用需要如何获得他们重新present的数据。我一般觉得更容易保持由应用程序驻留在应用$ C $使用查询C本身。
Having the queries in a central place like this may be a good thing, depending upon how many different applications need access to the data they represent. I generally find it much easier to keep the queries used by an application resident in the application code itself.
在90年代中期，传统智慧说，在SQL Server存储过程均性能关键的情况下要走的路，而当时他们肯定是。这个CW背后的原因一直没有有效的很长一段时间，但是。
In the mid-1990's, the conventional wisdom said that stored procedures in SQL Server were the way to go in performance-critical situations, and at the time they definitely were. The reasons behind this CW have not been valid for a long time, however.
更新：同时，经常在存储过程的可行性讨论，需要prevent SQL注入是在特效的防守调用。当然，没有人在心智认为，通过字符串连接组装即席查询是做正确的事（虽然这只会暴露你到一个SQL注入攻击，如果你串联的用户输入的） 。显然，即席查询应该是参数化，不仅要prevent妖怪下的床的SQL注入攻击，也只是为了让你的生活，作为一个程序员通常更容易（除非你喜欢有弄清楚的时候使用在你的价值观单引号）。
Update: Also, frequently in debates over the viability of stored procedures, the need to prevent SQL injection is invoked in defense of procs. Surely, no one in their right mind thinks that assembling ad hoc queries through string concatenation is the correct thing to do (although this will only expose you to a SQL injection attack if you're concatenating user input). Obviously ad hoc queries should be parameterized, not only to prevent the monster-under-the-bed of a sql injection attack, but also just to make your life as a programmer generally easier (unless you enjoy having to figure out when to use single quotes around your values).
更新2：我做更多的研究。基于这个MSDN白皮书，现在看来，答案取决于你的意思是广告-hoc与您的查询，没错。例如，一个简单的查询是这样的：
Update 2: I have done more research. Based on this MSDN white paper, it appears that the answer depends on what you mean by "ad-hoc" with your queries, exactly. For example, a simple query like this:
SELECT ID, DESC FROM tblSTUFF WHERE ITEM_COUNT > 5
... 将的有它的执行计划缓存。此外，因为查询不包含某些不合格的元素（不是一个简单的SELECT其他类似的几乎任何从一个表），SQL Server将实际上是自动参数化的查询和替换常量5有一个参数，和高速缓存对于参数化版本的执行计划。这意味着，如果你再执行的将此的即席查询：
... will have its execution plan cached. Moreover, because the query does not contain certain disqualifying elements (like nearly anything other than a simple SELECT from one table), SQL Server will actually "auto-parameterize" the query and replace the literal constant "5" with a parameter, and cache the execution plan for the parameterized version. This means that if you then execute this ad-hoc query:
SELECT ID, DESC FROM tblSTUFF WHERE ITEM_COUNT > 23
...这将能够使用缓存的执行计划。
... it will be able to use the cached execution plan.
不幸的是，不合格查询要素自动参数化的名单很长（例如，忘记使用 DISTINCT ， TOP ， UNION ， GROUP BY ，或等），所以你真的不能指望这样的表现。
Unfortunately, the list of disqualifying query elements for auto-parameterization is long (for example, forget about using DISTINCT, TOP, UNION, GROUP BY, OR etc.), so you really cannot count on this for performance.
如果你有一个超级复杂的查询，将不会自动参数，如：
If you do have a "super complex" query that won't be auto-parameterized, like:
SELECT ID, DESC FROM tblSTUFF WHERE ITEM_COUNT > 5 OR ITEM_COUNT < 23
...它仍然会被查询的确切内容进行缓存，因此，如果您的应用程序调用该查询使用相同的文字硬codeD的价值观多次，第一次将重新使用后，每个查询缓存的执行计划（并因此作为一个存储过程的速度）。
... it will still be cached by the exact text of the query, so if your application calls this query with the same literal "hard-coded" values repeatedly, each query after the first will re-use the cached execution plan (and thus be as fast as a stored proc).
如果文字值的变化（基于用户的操作，例如，如筛选或排序查看的数据），那么查询将不会从缓存中受益（除偶尔当他们意外地匹配最近查询完全一致）。
If the literal values change (based on user actions, for example, like filtering or sorting viewed data), then the queries will not benefit from caching (except occasionally when they accidentally match a recent query exactly).
从缓存与临时的查询中受益的方式是他们的参数。建立在C＃飞这样的查询：
The way to benefit from caching with "ad-hoc" queries is to parameterize them. Creating a query on the fly in C# like this:
int itemCount = 5;
string query = "DELETE FROM tblSTUFF WHERE ITEM_COUNT > " +
itemCount.ToString();
不正确。 （使用ADO.Net）正确的方法是这样的：
is incorrect. The correct way (using ADO.Net) would be something like this:
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();
}
查询不包含文字，并且已经使用相同的参数化的语句将使用高速缓存的计划（即使调用不同的参数值）完全参数化的，因此后续的查询。请注意，这里的code是几乎一样的code你会使用调用存储过程呢（是将CommandType和CommandText中唯一的区别），所以它有点归结到你想要的文字该查询活（应用程序中的code或存储过程）。
The query contains no literals and is already fully parameterized, so subsequent queries using the identical parameterized statement would use the cached plan (even if called with different parameter values). Note that the code here is virtually the same as the code you would use for calling a stored procedure anyway (the only difference being the CommandType and the CommandText), so it somewhat comes down to where you want the text of that query to "live" (in your application code or in a stored procedure).
最后，如果你的意思是你是动态构建具有不同的列，表的查询，过滤参数和诸如此类的东西，如可能这些临时的查询：
Finally, if by "ad-hoc" queries you mean you're dynamically constructing queries with different columns, tables, filtering parameters and whatnot, like maybe these:
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
...那么你pretty多的不能的做用存储过程（不包括 EXEC 破解这不在上流社会中被读的），所以关键是静音。
... then you pretty much can't do this with stored procedures (without the EXEC hack which is not to be spoken of in polite society), so the point is mute.
更新3：这里是唯一真正良好的性能相关的的原因（我能想到的，反正）使用存储过程。如果您的查询是一个长期运行的之一，编译执行计划的过程需要显著长于实际执行，并且查询只偶尔称为（如月度报告，例如），然后把它在存储过程中可能使SQL Server保持编译计划在缓存中足够长的时间它仍然是围绕下个月。击败我，如果这是真的还是假的，虽然。
Update 3: Here is the only really good performance-related reason (that I can think of, anyway) for using a stored procedure. If your query is a long-running one where the process of compiling the execution plan takes significantly longer than the actual execution, and the query is only called infrequently (like a monthly report, for example), then putting it in a stored procedure might make SQL Server keep the compiled plan in the cache long enough for it to still be around next month. Beats me if that's true or not, though.
这篇关于什么时候最好编写＆QUOT;即席SQL＆QUOT; VS存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
