代码中的查询非常缓慢，但在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＃版本查询的索引。

- Scott Chamberlain

---

你在 SSMS 中使用的用户和代码中使用的用户是一样的吗？ - bzlm

这个查询返回多少行？ - tom redfern

Answer 1

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

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

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

declare @clientID varchar(200)

由于数据类型优先级的规则，您查询中的表达式Where client_id = @clientID是不可SARG-able的，其中@clientID的类型为NVARCHAR（我认为client_id列的类型为VARCHAR）。因此，应用程序强制进行表扫描，而SSMS查询可以进行快速键查找。这是使用Parameters.AddWithValue时已知并理解的问题，并且在许多文章中已经讨论过，例如请参见数据访问代码如何影响数据库性能。一旦了解了问题，解决方案就很简单：

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

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

第一种解决方案更优，因为它不仅解决了SARG-ability问题，还解决了缓存污染问题。

我还建议您阅读应用程序中缓慢，在 SSMS 中快速？理解性能奥秘。

- Remus Rusanu

---

实际上，我使用了varchar，因为函数的接口将客户端ID作为字符串传递。我检查了模式，发现Client_ID实际上是一个整数。在我的代码中更改它，将传递的字符串转换为整数后再执行查询解决了问题。谢谢！ - Scott Chamberlain

Answer 2

遇到了同样的问题：

• 从代码中调用存储过程：30秒以上
• 从SSMS中调用相同的存储过程：毫秒级别。
• 从代码中在存储过程内部调用SQL：毫秒级别。

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

- Fiach Reid

---

这并没有真正回答问题。如果您有不同的问题，可以通过点击提问来提出。如果您想在此问题获得新的答案时得到通知，您可以关注此问题。一旦您拥有足够的声望，您还可以添加悬赏以吸引更多关注。- 来自审核 - Thom A

Answer 3

运行DBCC FREEPROCCACHE，如此处所建议的那样，只是为了确保问题不是由于旧的查询执行计划引起的。

- OutstandingBill

Answer 4

在你的C#连接上运行分析器 - 可能会有其他活动正在进行，而你并不知道。

- Jimbo

Answer 5

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

- user596075