c# - 查询代码非常慢，但在 SSMS 中查询速度很快

Question

我有一个相当简单的查询，当它在代码中运行时，我不断超时(它需要三分钟多才能完成，我提前停止它所以我可以发布这个问题)，但是当我运行相同的查询时在 Sql Server Management Studio 中的同一台计算机查询将只取 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# 版本的索引。

Answer 1

您在 SSMS 中的代码与您在应用程序中运行的代码不同。您应用程序中的这一行添加了一个 NVARCHAR 参数:

 ada.SelectCommand.Parameters.AddWithValue("@clientID", ClientID);

在 SSMS 脚本中将其声明为 VARCHAR:

declare @clientID varchar(200)

由于Data Type Precedence的规则您查询中的 Where client_id = @clientID 表达式不是 SARG-able where @clientID 是 NVARCHAR 类型(我正在做一个信念的飞跃并假设 client_id 列的类型为 VARCHAR)。因此，应用程序会强制进行表扫描，SSMS 查询可以在其中进行快速键查找。这是使用 Parameters.AddWithValue 的一个众所周知和理解的问题，并且在之前的许多文章中已经讨论过，例如。见How Data Access Code Affects Database Performance .一旦理解了问题，解决方案就很简单了:

• 使用 constructor that accepts a type 添加参数: Parameters.Add("@clientID", SqlDbType.Varchar, 200)(并且确实传入显式长度以防止缓存污染，参见Query performance and plan cache issues when parameter length not specified correctly

• 或在 SQL 文本中转换参数:where client_id = cast(@clientID as varchar(200))。

第一个解决方案更优越，因为它除了解决 SARG 能力问题外，还解决了缓存污染问题。

我还建议您阅读 Slow in the Application, Fast in SSMS? Understanding Performance Mysteries