使用SQL参数，克服特设性能问题
背景
我一个小的网络公司，在过去的一年或两年经历了两个方面的工作人员以及网站流量的增长大量工作。作为增长的一部分，我们已经不得不花费大量的时间，专注于我们的数据库。因为我们缺乏一个DBA，管理已下降到我。
我们现在成为每天1至2万页的意见，并在过去的一年，它已经到了我的注意，在我们的数据库最大的CPU和内存的负载没有这么多，由于表现欠佳的查询（虽然有一些），而是很差格式化的查询。
我注意到，因为我阅读文章，也可以作为我在商业应用程序运行的代码（稍后），即席查询使用相当位。这一切，尽管多次警告，随处可见关于SQL注入式攻击以及使用存储过程的性能优势。
对于这篇文章中，我定义（而不是松散）作为字符串的内置SQL客户端应用程序，并提交给SQL Server的SQL命令的即席查询的目的。这在本质上是相当包罗万象，包括几乎一切，但存储过程命令。写得不好的存储过程可以不佳调整SQL语句以外的原因，CPU和记忆流失，但这些原因超出了本文的范围。我打算把重点放在如何未能在SQL语句中使用参数，可以是对资源的巨大消耗和削弱，即使是最强大的硬件。
澄清，这篇文章的背景下，大量的用户执行类似的单个语句，同时，而不是在一个会话（连接），以单一批次的statments。SQL Server的内部
任何编程语言一样，每一个查询提交到SQL Server必须解析和遵守，然后才可以运行。从一个高层次的，这涉及到检查语法以及验证在查询中使用的对象的存在。一旦完成，这是SQL Server现在它理解一个结构，可以使用它来检索请求的数据。为了提高效率，SQL Server存储在内存中（过程高速缓存）这种结构重用。现在，这个查询被提交下一次，SQL Server可以跳过解析和编译步骤。
我们可以看到在下面的代码示例的行动:SET STATISTICS TIME ON
SET STATISTICS IO OFF
USE AdventureWorks
go
DBCC FREEPROCCACHE
go
SELECT C.Title, C.FirstName, C.LastName, C.EmailAddress, C.Phone, E.*
FROM Person.Contact C
INNER JOIN HumanResources.Employee E ON E.ContactID = C.ContactID
WHERE C.LastName = 'Miller'
警告 - 使用命令DBCC FREEPROCCACHE不建议在生产系统中。这样做可能会导致SQL Server表现不佳，直到可以重建过程高速缓存。它的目的在这篇文章这里是展示SQL Server如何使用过程高速缓存中存储和重用即席查询报表。
如果您查看"; Messagesquot;在SQL Server管理Studio（SSMS）中的窗格中，你会发现下面的:{C}
除其他事项外，前行受影响的邮件，你会看到分析和编译时间。这些时间会有所不同，这显然不是一个复杂的查询，所以不希望看到任何显著。我的AMD Athlon 64 X2 4200运行SQL Server 2005 x64的2GB的内存在Windows XP x64的。如果您运行只有SELECT语句又是什么，你很可能会看到的是这样的:SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 9 ms.
(1 row(s) affected)
在CPU的下降和使用时间的原因是由于第二次运行该语句（不清除过程高速缓存的缓冲区）的声明是从缓存中检索。为了说明这一点在行动创建以下跟踪:/****************************************************/
/* Created by: SQL Server Profiler 2005 */
/* Date: 12/30/2006 10:01:02 AM */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc
-- extension will be appended to the filename automatically. If you are
-- writing from remote server to local drive, please use UNC path and make
-- sure server has write access to your network share
exec @rc = sp_trace_create @TraceID output,0,N'C:\ProcedureCache',
@maxfilesize,NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 38, 1, @on
exec sp_trace_setevent @TraceID, 38, 21, @on
exec sp_trace_setevent @TraceID, 38, 10, @on
exec sp_trace_setevent @TraceID, 38, 35, @on
exec sp_trace_setevent @TraceID, 38, 12, @on
exec sp_trace_setevent @TraceID, 35, 1, @on
exec sp_trace_setevent @TraceID, 35, 10, @on
exec sp_trace_setevent @TraceID, 35, 35, @on
exec sp_trace_setevent @TraceID, 35, 12, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'AdventureWorks'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
当你运行该脚本，确保复制TraceID的结果，这样你就可以停止跟踪，当你使用以下命令:--stop trace job
exec sp_trace_setstatus {PUT TRACEID HERE}
, 0
创建的跟踪后，请按照前两个步骤。然后打开你的跟踪文件（C:\ ProcedureCache.trc如果你从这个脚本使用的是默认值），你会看到几个跟踪事件。其中，你会看到一个SP:CacheInsert事件条目SELECT语句进一步下降为SP:相同的SELECT语句的CacheHit事件。这些事件表明，正是我刚才所描述的。即席SQL
即席SQL是非常灵活，往往比编写存储过程开发人员更熟悉，因为它允许开发人员简单地构造一个字符串（通常通过contatenation），并提交到数据库。特设可以使书面窗体查询"（QBF）过程很简单，用户输入的指示表，联接和参数都需要满足的要求。产生的SQL语句可以是这个样子的:SELECT C.Title, C.FirstName, C.LastName, C.EmailAddress, C.Phone, E.*
FROM Person.Contact C
INNER JOIN HumanResources.Employee E ON E.ContactID = C.ContactID
WHERE C.LastName = 'Smith'
如果再次运行该语句在SSMS中，并期待在分析和编译时间，你会看到的说法是重新编译。另外，如果你还在运行轨迹，你会看到的说法是补充，而不是被重用的过程高速缓存。这表明，除非查询（包括WHERE子句中的值）是完全一样，SQL Server必须重新编译的语句。在一个小的数据库，流量小，大部分会被忽视。但您的过程高速缓存上的一个较大的系统或显著的交通量会膨胀。这方面的一个副作用是，在缓冲区高速缓存的数据将被排挤出更多的数据直接从磁盘中读取，而不是从内存。这将导致一个严重的IO瓶颈和最有可能您的系统瘫痪。
几年前，我们购买了广告管理的ASP.Net应用程序。数据库大小超过20MB，但由于交通（100 - 200批/秒），它是能够带来不安装任何其他数据库的一个3GHz的Xeon处理器与2GB内存在服务器上的膝盖。当我们调查的问题，我们发现SQL服务器使用所有2GB的内存和CPU卡100％。
下面是我们的查询语句看起来可能从一个网络客户端的例子。string eName = "Smith";
DataSet data = new DataSet();
using(SqlConnection conn = new SqlConnection(
"Application Name=AdHocClient;Server=(local);" +
"Database=AdventureWorks;Trusted_Connection=Yes;"))
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT C.Title, C.FirstName, C.LastName, C.EmailAddress,");
sql.Append("C.Phone, E.*");
sql.Append(" FROM Person.Contact C");
sql.Append(" INNER JOIN HumanResources.Employee E ");
sql.Append(" ON E.ContactID = C.ContactID");
sql.AppendFormat(" WHERE C.LastName = '{0}'", eName);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql.ToString();
cmd.CommandType = CommandType.Text;
try
{
using(SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(data);
}
}
catch(SqlException ex)
{
//.... execption code .....
}
}
变量ENAME代表从用户输入的检索值。每一个过程是不同的输入语句的运行时编译和缓存。这将导致我刚才所描述的最终压倒了数据库服务器的条件。那么，我们如何解决这个问题，需要改变量最少？参数化SQL
存储过程是从客户端应用程序访问数据库推荐的方法 - 安全和性能方面的原因。但如果你已经写你的应用程序，并没有时间，将所有数据访问存储过程的另一种方法是参数化SQL语句。当你定义参数的语句是相同的，没有什么值被传递的。因此，如果我们采取上述过程，并改写如下:string eName = "Smith";
DataSet data = new DataSet();
using(SqlConnection conn = new SqlConnection(
"Application Name=ParameterizedClient;Server=(local);" +
"Database=AdventureWorks;Trusted_Connection=Yes;"))
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT C.Title, C.FirstName, C.LastName, C.EmailAddress, ");
sql.Append("C.Phone, E.*");
sql.Append(" FROM Person.Contact C");
sql.Append(" INNER JOIN HumanResources.Employee E ");
sql.Append(" ON E.ContactID = C.ContactID");
//replace contatenation of variable with parameter name
sql.Append(" WHERE C.LastName = @LastName");
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql.ToString();
cmd.CommandType = CommandType.Text;
//Define SqlParameter object and assign value to the parameter
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50);
cmd.Parameters["@LastName"].Value = eName;
try
{
using(SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(data);
}
}
catch(SqlException ex)
{
//.... execption code .....
}
}
现在，当您的客户端应用程序运行这个过程中，SQL服务器将始终使用的过程高速缓存中的编译版本，以防止腹胀的缓存，并允许更多的系统内存缓冲区高速缓存的使用，这也将提高您的系统性能和应用scaleablility。SqlCommand.Prepare（）
一位成员问weither使用SqlCommand对象的准备（）方法，它会更有效。在这篇文章的背景下，答案是，没有。 prepare（）方法调用sp_prepexec，一个未公开的存储过程。从本质上讲，它会创建一个临时存储过程为本届会议，这是当前连接被关闭时销毁。这使得被称为查询就好像它是一个存储过程，直到连接被关闭。使用准备（）的好处是，它降低了网络流量，在客户端应用程序定义的，因为整个命令文本是在网络上只发送一次。第一次调用prepare（）方法执行命令后，它看起来像这样:declare @p1 int
set @p1=10001
exec sp_prepexec @p1 output,N'SELECT C.Title, C.FirstName, C.LastName,
C.EmailAddress, C.Phone,E.*
FROM Person.Contact C
INNER JOIN HumanResources.Employee E ON E.ContactID = C.ContactID
WHERE C.LastName = @LastName
',@EmployeeID=15073
select @p1
注意它是如何声明@ P1，随着命令的结果作为标量值返回。 SqlCommand对象，然后在后续请求可以发送以下命令:exec sp_execute 10001,@EmployeeID=12991
它之所以没有更有效地使用准备（）方法是在这种情况下，因为除非使用相同的连接对象是仍然开放，将请求发送到SQL Server始终sp_prepexec呼叫。因此，这增加了创建临时存储过程，以及与该存储过程的处理开销，但你没有得到减少交通的好处。这种开销将创建一个SQL Server上产生很大的影响，然而这是不可能的，但我建议省略了调用准备（），除非它的需要。
准备（）方法将大放异彩SqlCommand对象时将调用在同一会期内（通常是在一个循环）反复。没有准备（）相比，它要快得多。我不会提供任何基准比较。我离开你，读者。但在示例项目中，我有一种方法，这将允许你做一些自己的测试。示例项目
本文附带的示例项目运行一个存储过程从AdventureWorks数据库和整个文本的存储过程传递一个命令对象为文本的模拟与一个单一的参数更复杂的查询。有两个版本的查询，特设版本和一个参数的版本。每一个运行10000次，与作为参数的随机整数。
我建议同时运行的PerfMon示例项目正在运行。 ％处理器时间，我用的柜台流程 - SQLSERVER中，SQL Server:SQL Statistics.Batch请求/秒和SQL Server计划缓存 - 缓存页面。这里是我的结果:运行时间CPU的请求/秒缓存页面 - 开始高速缓存页 - 完特设115证交会95％10960043684参数10秒59％
1153260
正如你可以看到，不带参数，这个过程可能需要10倍更长的时间来运行，CPU运行速度高出近2倍和过程高速缓存使用8K页的数目是200倍。最后一个计数器（8K页）将继续恶化不再在应用程序运行的参数化查询时，它只会增加一个版本的计划缓存。相反，专案版本将增加传递给它的每一个独特的可变的一个新版本的计划。结论
虽然存储过程数据访问客户端推荐的方法，它不总是可行的改写一切，如果你的代码已经使用即席SQL。然而，你可以使用，你可以从存储过程的参数几乎相同的性能优势。因此，如果你还没有做，所以确实不应该有任何借口，不参数化查询的。您的应用程序将更好的规模将更加稳定。修订历史1/10/2007增加了文章的上下文的澄清新增SqlCommand.Prepare（）第添加定时器示例项目删除样本项目，并在本文的示例代码使用的串联代替SqlDataAdapter.Fill（SqlCommand.ExecuteReader（））在示例项目添加的方法示例项目来演示使用SqlCommand.Prepare（）
