我有一个，我不断收到超时非常简单的查询（以完成它需要在三分钟，我很早就停止了它，所以我可以张贴此问题），当它在code上运行，但是当我运行从同一计算机在SQL Server Management Studio中相同的查询，查询只需要 2532毫秒第一次查询数据时，没有缓存在服务器上， 524毫秒重复查询。

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.

下面是我的C＃code

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

这是我的code，我在SSMS中运行的，我把它直接从ada.SelectCommand.CommandText

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.

在同一台计算机和登录用于应用程序和SSMS。

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

只有15行，在我的例子中查询返回的。然而， es_history 包含 11351699行和 es_history_dt 包含 8588493行。两个表以及索引和SSMS的执行计划说，他们正在使用索引搜索的查询窗口，使他们快速查找。该方案是表现得好像它没有使用索引为C＃版本的查询。

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.

推荐答案

您code在SSMS中是不一样的code，你在你的应用程序中运行。这条线在应用程序中增加了一个NVARCHAR参数：

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);

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

while in the SSMS script you declare it as VARCHAR:

declare @clientID varchar(200)

由于数据类型为precedence 中的其中CLIENT_ID = @clientID EX pression在您的查询是不是特区政府，能够在这里 @clientID 是类型为nvarchar（我让信仰的飞跃，并假定 CLIENT_ID 列是VARCHAR类型）。因此，应用程序强制表扫描，其中SSMS查询可以做一个快捷键所追求的。这是一个众所周知的，并使用Parameters.AddWithValue，在很多文章已经讨论过，如理解的问题。看到数据访问code如何影响数据库性能。一旦问题。据了解，该解决方案是微不足道的：

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:

与构造函数接受一个类型添加参数： Parameters.Add（@ clientID的，SqlDbType.Varchar，200）（和做传递明确的长度，以prevent缓存污染，请参阅Query如果没有正确指定的性能和计划缓存问题参数长度

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

或的投参数在SQL文本：，其中CLIENT_ID =投（@clientID为varchar（200））

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.

我也建议您阅读缓慢的应用，快速的SSMS？了解性能的神秘