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

Question

我有一个相当简单的查询，当它在代码中运行时，我不断超时（需要三分钟多才能完成，我提前停止了它，所以我可以发布这个问题），但是当我从同一台计算机运行相同的查询时在 Sql Server Management Studio 中，当数据未缓存在服务器上时，第一次查询仅需要 2532 毫秒，而重复查询则需要 524 毫秒。

这是我的 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 行，而 es_history_dt 包含 8588493 行。这两个表都有很好的索引，SSMS 中的执行计划表示它们正在使用索引查找进行查找，因此它们的查找速度很快。该程序的行为就好像它没有使用 C# 版本查询的索引。

Answer 1

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

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

而在 SSMS 脚本中，您将其声明为 VARCHAR：

declare @clientID varchar(200)

由于 数据类型优先级 查询中的 Where client_id = @clientID 表达式不支持 SARG，其中 @clientID 为类型 NVARCHAR（我的信念飞跃并假设 client_id 列的类型为 VARCHAR）。因此，应用程序强制进行表扫描，其中 SSMS 查询可以执行快速键查找。这是使用Parameters.AddWithValue 时众所周知且易于理解的问题，并且之前已在许多文章中讨论过，例如。请参阅数据访问代码如何影响数据库性能。一旦理解了问题，解决方案就很简单：

• 使用构造函数添加参数接受类型：Parameters.Add("@clientID", SqlDbType.Varchar, 200)（并且do传入显式长度以防止缓存污染，请参阅 未正确指定参数长度时的查询性能和计划缓存问题

• 或在 SQL 文本中强制转换参数：where client_id = cast( @clientID 为varchar(200))。

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

我还建议您阅读应用程序慢，SSMS 快？了解性能奥秘

Answer 2

按照建议运行DBCC FREEPROCCACHE这里，只是为了确保问题不是由于过时的查询执行计划造成的。

Answer 3

遇到同样的问题：

• 从代码调用存储过程：30 秒+
• 从 SSMS 调用相同存储过程：毫秒。
• 从代码中的存储过程中调用 SQL：毫秒。

解决方案：
删除存储过程，然后重新创建完全相同的存储过程，现在两者都以毫秒为单位返回。没有代码更改。

Answer 4

在您的 C# 连接上运行探查器 - 可能还有您不知道的其他活动正在发生。

Answer 5

当您手动运行查询时，从 SSMS 捕获执行计划；当您运行应用程序时，从 Profiler 捕获执行计划。比较和对比。