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

Ok, so I have this query that takes 2-5 seconds to run within Sql Management Studio. But when I am running it via my .net application, It exceeds the CommandTimeout of 5 minutes, every time.

好的,所以我有这个查询需要2-5秒才能在Sql Management Studio中运行。但是当我通过我的.net应用程序运行它时,它每次超过5分钟的CommandTimeout。

I know the .net code works, because this same code (below) is executing other queries fine, and is delivering results.

我知道.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;
    }

Below is the query, but with tables renamed:

下面是查询,但重命名表:

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

Please help, I am at a total loss for why this one query is having issues.

请帮助,我完全不知道为什么这个查询有问题。

UPDATE (old, found while the below is interesting, it is not causing the problem) So I ran the trace, looking for the query, it showed up when I ran the query manually, but when I ran it through the code, it wasn't appearing at all, and got the same error message. So i really looked at the connection string, and I noticed something strange. While the user name and password were being passed, the SqlConnection object's ConnectionString property was missing the Password. I don't know if that points to a solution, but I am very confused now.

更新(旧的,发现虽然以下是有趣的,它不会导致问题)所以我运行跟踪,寻找查询,它出现时,我手动运行查询,但当我通过代码运行它,它不是根本没出现,并得到了相同的错误信息。所以我真的看了连接字符串,我发现了一些奇怪的东西。在传递用户名和密码时,SqlConnection对象的ConnectionString属性缺少密码。我不知道这是否指向解决方案,但我现在很困惑。

UPDATE #2 I didn't let the trace run long enough. I was able to capture the call that was super long.

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

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

Running this exact query, I am getting the same result (it actually completes, it just takes 5+ minutes running it via this method, rather than the 3 seconds running the query directly). Note I did try to run the query with the parameters specified as nvarchar(4000) as well, but just running the query in Sql management studio works fine.

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

UPDATE #3 I have updated the statistics for all the tables that are joined within the query, no luck. The sp_executeSQL query still takes close to 5 minutes (its about 30 seconds less than before the rebuild of the statistics). At this point I am at a loss. Any Ideas?

更新#3我已经更新了查询中加入的所有表的统计信息,没有运气。 sp_executeSQL查询仍然需要接近5分钟(比重建统计信息之前少约30秒)。在这一点上,我不知所措。有任何想法吗?

UPDATE #4 finally found the solution! the problem was due to "Parameter Sniffing" I was using if conditions prior to my result generating query. the execution plan engine was assuming that the parameters, when passed as null, would hit the query as null, which is not the case. They always would have a value. To correct the problem, i removed the if conditions at the beginning of the query, and placed ISNULL checks where ever the parameter was used. this notified the execution plan of my intentions, and the sp_executeSQL call executed the same speed as my Sql Management Studio execution. Thank you all for your help!

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

2 个解决方案

#1

Try testing with SQL Profiler.

尝试使用SQL Profiler进行测试。

Check to see if the sql command is making it to the database when you think it is and isn't being delayed.

检查sql命令是否在您认为是并且没有被延迟时进入数据库。

Also, check the actual text of the query as received by the database and then run that text in Management Studio. It's possible the database is receiving something that is not quite what you are expecting.

此外,检查数据库收到的查询的实际文本,然后在Management Studio中运行该文本。数据库可能正在接收一些与您期望不同的东西。

#2

It is almost certainly an incorrectly cached query plan (this is the classic symptom). This is often the result of out of date statistics.

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

I suggest you update statistics.

我建议你更新统计数据。

One way to check whether statistics are 'skewed' or out of date, is to run your query with the Actual Execution plan turned on, and then examine the estimated rows versus actual rows in each operator.

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

UPDATE (in response to comments): You could try rebuilding all your indexes. As a last resort you could try marking the Stored Procedure with AS RECOMPILE, which is essentially what happens when you run through SQL Server Management Studio (SSMS). This would conclusively determine if it is an inappropriate cached query plan. If it is, it may be possible to mark up the stored proc with OPTIMIZE FOR.

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