我有一个相当简单的查询,我不断收到超时(它需要三分钟才能完成,所以我尽早停止了,这样我就可以发布此问题),它在代码中运行的时间,但是当我在同一台计算机上运行相同的查询时在Sql Server Management Studio中,仅2532 ms当数据未缓存在服务器上且524 ms重复查询时,查询才会采用第一个查询。
2532 ms
524 ms
这是我的C#代码
using (var conn = new SqlConnection("Data Source=backend.example.com;Connect Timeout=5;Initial Catalog=Logs;Persist Security Info=True;User ID=backendAPI;Password=Redacted")) using (var ada = new SqlDataAdapter(String.Format(@" SELECT [PK_JOB],[CLIENT_ID],[STATUS],[LOG_NAME],dt FROM [ES_HISTORY] inner join [es_history_dt] on [PK_JOB] = [es_historyid] Where client_id = @clientID and dt > @dt and (job_type > 4 {0}) {1} Order by dt desc" , where.ToString(), (cbShowOnlyFailed.Checked ? "and Status = 1" : "")), conn)) { ada.SelectCommand.Parameters.AddWithValue("@clientID", ClientID); ada.SelectCommand.Parameters.AddWithValue("@dt", dtpFilter.Value); //ada.SelectCommand.CommandTimeout = 60; conn.Open(); Logs.Clear(); ada.Fill(Logs); //Time out exception for 30 sec limit. }
这是我在SSMS中运行的代码,我从ada.SelectCommand.CommandText中将其提取出来
declare @clientID varchar(200) set @clientID = '138' declare @dt datetime set @dt = '9/19/2011 12:00:00 AM' SELECT [PK_JOB],[CLIENT_ID],[STATUS],[LOG_NAME],dt FROM [ES_HISTORY] inner join [es_history_dt] on [PK_JOB] = [es_historyid] Where client_id = @clientID and dt > @dt and (job_type > 4 or job_type = 0 or job_type = 1 or job_type = 4 ) Order by dt desc
是什么造成时间差异的主要差异?
为了保持评论部分的清洁,我将在此处回答一些常见问题解答。
应用程序和ssms使用同一台计算机和登录名。
在我的示例查询中仅返回15行。但是,es_history包含11351699 rows和es_history_dt包含8588493 rows。这两张表的索引都很好,SSMS中的执行计划表示它们正在使用索引查找进行查找,因此它们是快速查找。该程序的行为好像没有在查询的C#版本中使用索引。
es_history
11351699 rows
es_history_dt
8588493 rows
您在SSMS中的代码与您在应用程序中运行的代码不同。您的应用程序中的这一行添加了NVARCHAR参数:
ada.SelectCommand.Parameters.AddWithValue("@clientID", ClientID);
而在SSMS脚本中,您将其声明为VARCHAR:
declare @clientID varchar(200)
由于数据类型优先的规则,Where client_id = @clientID查询中的表达式不是SARG-able的,其中@clientIDNVARCHAR 类型的表达式(我在信仰上取得了飞跃,并假设client_id列的类型为VARCHAR)。因此,该应用程序强制进行表扫描,其中SSMS查询可以执行快速键查找。这是使用Parameters.AddWithValue的一个众所周知的问题,之前在很多文章中都有讨论过,例如。请参见数据访问代码如何影响数据库性能。一旦了解了问题,解决方案就变得微不足道了:
Where client_id = @clientID
@clientID
client_id
添加参数与接受一个类型构造:Parameters.Add("@clientID", SqlDbType.Varchar, 200)(和 做 的明确的长度传递,以防止缓存污染,请查询性能和计划缓存问题时,未正确指定参数长度
Parameters.Add("@clientID", SqlDbType.Varchar, 200)
或 将参数强制转换为SQL文本:where client_id = cast(@clientID as varchar(200))。
where client_id = cast(@clientID as varchar(200))
第一种解决方案是优越的,因为它不仅可以解决SARG能力问题,而且还解决了缓存污染问题。
我还建议您阅读“应用程序中的慢速”,“ SSMS中的快速”?了解性能奥秘