代码查询速度极慢，但在 SSMS 中查询速度很快 [英] Query extremely slow in code but fast in SSMS

问题描述

我有一个相当简单的查询，当它在代码中运行时，我不断收到超时(需要超过三分钟才能完成，我很早就停止了它以便我可以发布这个问题)，但是当我从在 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"))使用 (var ada = new SqlDataAdapter(String.Format(@"SELECT [PK_JOB],[CLIENT_ID],[STATUS],[LOG_NAME],dt来自 [ES_HISTORY][PK_JOB] = [es_historyid] 上的内部连接 ​​[es_history_dt]其中 client_id = @clientID 和 dt >@dt 和 (job_type > 4 {0}) {1}按 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();日志.清除();ada.Fill(日志)；//30 秒限制的超时异常.}

这是我在 SSMS 中运行的代码，我是从 ada.SelectCommand.CommandText 中提取的

声明@clientID varchar(200)设置@clientID = '138'声明@dt 日期时间设置@dt = '9/19/2011 12:00:00 AM'SELECT [PK_JOB],[CLIENT_ID],[STATUS],[LOG_NAME],dt来自 [ES_HISTORY][PK_JOB] = [es_historyid] 上的内部连接 ​​[es_history_dt]其中 client_id = @clientID 和 dt >@dt and (job_type > 4 or job_type = 0 or job_type = 1 or job_type = 4 )按 dt desc 排序

是什么导致了时间差异的主要差异?

<小时>

为了保持评论区干净，我会在这里回答一些常见问题.

应用程序和 ssms 使用相同的计算机和登录.

在我的示例查询中只返回了 15 行.但是，es_history 包含 11351699 行，es_history_dt 包含 8588493 行.两个表都建立了良好的索引，SSMS 中的执行计划表示它们正在使用索引查找进行查找，因此它们是快速查找.程序表现得好像它没有使用查询的 C# 版本的索引.

解决方案

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

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

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

声明@clientID varchar(200)

由于数据类型优先权的规则，查询中的 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 as varchar(200)).

第一种方案更胜一筹，因为它除了解决 SARG 能力问题之外，还解决了缓存污染问题.

我还建议您阅读 应用程序缓慢，SSMS 快速?了解性能之谜

I have a fairly simple query that I keep getting timeouts (it takes over three minutes to complete, I stopped it early so I could post this question) on when it is running in code, however when I run the same query from the same computer in Sql Server Management Studio the query will only take 2532 ms the first query when the data is not cached on the server and 524 ms for repeated queries.

Here is my c# code

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.
}

here is my code I am running in SSMS, I pulled it right from 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

What is causing the major discrepancy for the difference in time?

---

To keep the comment section clean, I will answer some FAQ's here.

The same computer and logon is used for both the application and ssms.

Only 15 rows are returned in my example query. However, es_history contains 11351699 rows and es_history_dt contains 8588493 rows. Both tables are well indexed and the execution plan in SSMS says they are using index seeks for the look-ups so they are fast lookups. The program is behaving as if it is not using the indexes for the C# version of the query.

解决方案

Your code in SSMS is not the same code you run in your application. This line in your application adds a NVARCHAR parameter:

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

while in the SSMS script you declare it as VARCHAR:

declare @clientID varchar(200)

Due to the rules of Data Type Precedence the Where client_id = @clientID expression in your query is not SARG-able where @clientID is of type NVARCHAR (I'm making a leap of faith and assume that client_id column is of type VARCHAR). The application thus forces a table scan where the SSMS query can do a quick key seek. This is a well know and understood issue with using Parameters.AddWithValue and has been discussed in many articles before, eg. see How Data Access Code Affects Database Performance. Once the problem is understood, the solutions are trivial:

• add parameters with the constructor that accepts a type: Parameters.Add("@clientID", SqlDbType.Varchar, 200) (and do pass in the explicit length to prevent cache pollution, see Query performance and plan cache issues when parameter length not specified correctly

• or cast the parameter in the SQL text: where client_id = cast(@clientID as varchar(200)).

The first solution is superior because it solves the cache pollution problem in addition to the SARG-ability problem.

I would also recommend you read Slow in the Application, Fast in SSMS? Understanding Performance Mysteries

这篇关于代码查询速度极慢，但在 SSMS 中查询速度很快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！