的SqlCommand超时即使在SQL工作室相同的查询速度快 [英] SqlCommand timeout even though in SQL Studio same query is fast

问题描述

我有我的想法是，我从我的小日志处理应用程序中执行一个简单的查询。这种方法的目的是为了简单地得到最高的日期值了日志表：

I have what I thought was a simple query that I execute from my little log-processing application. The aim of this method is to simply get the highest date value out of the log table:

private DateTime GetLastEntryDate(string serverName, string siteName)
{
    DateTime dt = DateTime.MinValue;
    using (var con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["LogParserDB"].ConnectionString))
    {
        con.Open();
        using (var cmd = new SqlCommand("SELECT MAX(date) FROM iislogs WHERE host=@Host AND site=@Site", con))
        {
            cmd.CommandTimeout = 120;
            cmd.Parameters.AddWithValue("Host", serverName);
            cmd.Parameters.AddWithValue("Site", siteName);
            var result = cmd.ExecuteScalar();
            if (result != DBNull.Value)
            {
                dt = (DateTime)result;
            }
        }
    }
    return dt;
}

有上表中的一些指标，但我不知道这是相关的，因为我得到的问题是，当我运行这段代码，它抛出的的ExecuteScalar 行2分钟后超时。

There are some indexes on the table, but I'm not sure if that's relevant, because the problem I'm getting is that when I run this code, it throws a timeout after 2 minutes on the ExecuteScalar line.

如果我复制和粘贴查询到SSMS使用相同的参数，它完成于00:00:04，甚至00:00:00（如果我刚刚更新的统计数据）。

If I copy and paste that query into SSMS with the same parameters, it completes in 00:00:04, or even 00:00:00 (if I've just updated stats).

SELECT MAX(date) FROM iislogs WHERE host='servername' AND site='W3SVC1'

我已经检查拦网，我不能看到这样的事情 - 这是数据库的只有的被访问的这。一个应用程序，它不是多线程之类的东西。

I have checked for blocking, and I can't see anything like that - that database is only being accessed by this one app, and it's not multi-threaded or anything like that.

更新：
有趣的是，当我运行准确的查询通过探查所捕获，它也需要在SSMS很长的时间：

Update: Interestingly, when I run the exact query as captured by Profiler, it also takes a long time in SSMS:

exec sp_executesql N'SELECT MAX(date) FROM iislogs WHERE host=@Host AND site=@Site',N'@Host nvarchar(13),@Site nvarchar(6)',@Host=N'servername',@Site=N'W3SVC1'

实际列 VARCHAR（50）和 VARCHAR（10）分别。

推荐答案

您在SSMS执行的查询

The query you execute in SSMS

SELECT MAX(date)
FROM   iislogs
WHERE  host = 'servername'
       AND site = 'W3SVC1' 

时的不一样，你的应用程序执行的。

Is not the same as the one executed by your application.

EXEC sp_executesql
  N'SELECT MAX(date) FROM iislogs WHERE host=@Host AND site=@Site',
  N'@Host nvarchar(13),@Site nvarchar(6)',
  @Host=N'servername',
  @Site=N'W3SVC1' 

第一个具有 VARCHAR 字符串。第二个具有为nvarchar 参数。您的列数据类型其实都是 VARCHAR 。

The first one has varchar string literals. The second one has nvarchar parameters. Your column datatypes are in fact varchar.

为nvarchar 有更高的数据类型优先级比 VARCHAR 所以你迫使列的隐式转换。这将使得在列没用任何索引。

nvarchar has higher datatype precedence than varchar so you are forcing an implicit cast of the column. This will render any indexes on the column useless.

在应用程序中的数据类型参数更改为 VARCHAR

Change the parameter datatypes in the application to varchar

这篇关于的SqlCommand超时即使在SQL工作室相同的查询速度快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！