简单查询超时仅在.net SqlDataAdapter 中

Question

好的，我的查询在 Sql Management Studio 中运行需要 2-5 秒。但是当我通过 .net 应用程序运行它时，它每次都超过 5 分钟的 CommandTimeout。

我知道 .net 代码可以工作，因为相同的代码（如下）可以很好地执行其他查询，并且可以提供结果。

public DataTable ExecuteQuery()
    {
        DataTable result = new DataTable();
        FoSqlConn con = new FoSqlConn(ConnectionToUse, ApplicationName); // connection string factory
        List<string> parameterNames = GetAllParametersFromQueryString(QueryString);
        using (SqlConnection sqlCon = new SqlConnection(con.GetConnectionString()))
        {
            using (SqlCommand cmd = new SqlCommand(QueryString, sqlCon))
            {
                if (DefaultTimeout.HasValue == true)
                {
                    cmd.CommandTimeout = DefaultTimeout.Value;
                }
                foreach (string paramName in parameterNames)
                {
                    if (Context.ParameterExists(paramName))
                    {
                        cmd.Parameters.AddWithValue(paramName, Context.GetParameterByName(paramName));
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue(paramName, DBNull.Value);
                    }
                }
                sqlCon.Open();
                using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                {
                    adapter.Fill(result);
                }
                if (sqlCon.State == ConnectionState.Open)
                {
                    con.CloseConnection();
                }
            }
        }

        return result;
    }

下面是查询，但表已重命名：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
IF (@StartDate IS NULL)
BEGIN 
    SET @StartDate = DATEADD(Day, -60, GETDATE())
END 

IF (@EndDate IS NULL)
BEGIN
    SET @EndDate = DATEADD(DAY, -1, GETDATE())
END

SELECT
    P.ProductionObjDimId
    ,Dim.ProductionObjSourceId
    ,Dim.Name as WellName
    ,Dim.CurrentStatus
    ,Dim.ApiCode
    ,Dim.CurrentType
    ,Dim.StateProvidenceCode
FROM 
    ProductionDetail as P with(nolock)

JOIN DataWarehouse.dbo.ProductionObjMetaData as M
ON  P.ProductionObjDimId = M.ProductionObjDimId
AND M.OperationType = 1

JOIN DataWarehouse.dbo.ProductionObjDim as Dim
ON P.ProductionObjDimId = Dim.ProductionObjDimId

LEFT OUTER JOIN ProductionForecastingLinksView as Forcast
ON  Dim.ProductionObjSourceId = Forcast.comp_sk
AND Forcast.StartDate <= @EndDate
AND Forcast.EndDate > @StartDate

LEFT OUTER JOIN DataWarehouse.dbo.ForecastingUpload as Upload
ON Forcast.propnum = Upload.ForecastingWellSourceId
AND Upload.StartDate <= @EndDate
AND Upload.EndDate > @StartDate
AND (Upload.UploadDaily = 1 OR Upload.UploadMonthly = 1)
WHERE 
    P.ProductionDate >= @StartDate
AND
    Upload.ForecastingWellSourceId IS NULL
GROUP BY
    P.ProductionObjDimId
    ,Forcast.propnum        
    ,Dim.ProductionObjSourceId
    ,Dim.Name
    ,Dim.CurrentStatus
    ,Dim.ApiCode
    ,Dim.CurrentType
    ,Dim.StateProvidenceCode
Having 
    SUM(P.GrossOilProduction) > 0
OR  SUM(P.GrossGasSale) > 0
order by WellName

请帮忙，我完全不知道为什么这个查询出现问题。

更新（旧的，发现虽然下面的内容很有趣，但它不会引起问题） 因此，我运行跟踪，查找查询，当我手动运行查询时它出现了，但是当我通过代码运行它时，它根本没有出现，并且得到了相同的错误消息。所以我仔细查看了连接字符串，发现了一些奇怪的东西。传递用户名和密码时，SqlConnection 对象的 ConnectionString 属性缺少密码。我不知道这是否意味着解决方案，但我现在很困惑。

更新 #2 我没有让跟踪运行足够长的时间。我能够捕捉到超长的通话。

exec sp_executesql N'SET TRANSACTION ISOLATION...[SAME CODE AS ABOVE]' ,@StartDate=NULL,@EndDate=NULL   

运行这个精确的查询，我得到了相同的结果（它实际上完成了，通过此方法运行它只需要 5 分钟以上，而不是直接运行查询 3 秒）。 注意 我也尝试使用指定为 nvarchar(4000) 的参数运行查询，但仅在 Sql Management Studio 中运行查询就可以正常工作。

更新 #3 我已经更新了查询中连接的所有表的统计信息，但没有成功。 sp_executeSQL 查询仍然需要接近 5 分钟（比重建统计信息之前少了大约 30 秒）。此时我不知所措。有什么想法吗？

更新#4终于找到了解决方案！问题是由于“参数嗅探”造成的，我在结果生成查询之前使用了 if 条件。执行计划引擎假设参数在作为 null 传递时会将查询命中为 null，但事实并非如此。它们总是有价值的。为了纠正这个问题，我删除了查询开头的 if 条件，并在使用参数的地方放置了 ISNULL 检查。这通知了执行计划我的意图，并且 sp_executeSQL 调用的执行速度与我的 Sql Management Studio 执行速度相同。谢谢大家的帮助！

Answer 1

尝试使用 SQL Profiler 进行测试。

检查 sql 命令是否在您认为延迟或没有延迟时到达数据库。

另外，检查数据库接收到的查询的实际文本，然后在 Management Studio 中运行该文本。数据库可能收到的内容与您所期望的不完全一样。

Answer 2

几乎可以肯定这是一个错误缓存的查询计划（这是典型的症状）。这通常是过时统计数据的结果。

我建议您更新统计信息。

检查统计信息是否“倾斜”或过时的一种方法是在打开实际执行计划的情况下运行查询，然后检查每个运算符中的估计行数与实际行数。

更新（回应评论）：您可以尝试重建所有索引。作为最后的手段，您可以尝试使用 AS RECOMPILE 标记存储过程，这基本上就是通过 SQL Server Management Studio (SSMS) 运行时发生的情况。这将最终确定它是否是不适当的缓存查询计划。如果是，则可以使用 OPTIMIZE FOR 标记存储过程。