Dapper动态参数在SQL WITH子句中使用时查询超时问题求助

问题 #

我通过C#的Dapper执行以下SQL查询：

var sqlQuery = @"
  ;WITH CalculatedData AS 
  (
      SELECT
          b.Guid1C AS guid,
          b.Name AS name,
          SUM(ct.TotalSold) AS total_Sold,
          SUM(ct.TotalIncome) as total_Income 
        ,SUM(ct.SelfCost) as self_Cost
        ,SUM(ct.TotalProfit) as total_Profit 
        ,CASE WHEN (SUM(ct.TotalProfit) > 0 AND SUM(ct.TotalIncome) > 0) THEN (SUM(ct.TotalProfit) / SUM(ct.TotalIncome) * 100) ELSE 0 END as gross_Profit_Margin 
    from CalculatedTransactions ct with(nolock)
    inner join 
        Brands b with(nolock) on ct.BrandGuid = b.Guid1C 
    where ct.TransactionDate >= @fromDate and ct.TransactionDate <= @toDate 
    group by b.Guid1C, b.Name
  )
                                                                 
   SELECT total_count = count(1) OVER(), full_total_icome = SUM(total_Income) OVER(), *
   FROM CalculatedData WHERE 1=1 
   ORDER BY name ASC 
   OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY";

传递参数的代码如下：

var @params = new DynamicParameters();
@params.Add("fromDate", from);
@params.Add("toDate", to);
@params.Add("skip", skip);
@params.Add("take", take);

var result = await connection.QueryAsync(sqlQuery, @params);

遇到的问题是仅在C#程序中执行时会超时，具体情况：

• 将查询打印后在SSMS中运行可正常执行
• 注释掉where子句后可正常执行
• 移除;WITH子句后可正常执行

更新：修改查询连接其他表时可正常运行，仅连接Brands表时出现问题。

可能的原因及解决方向 #

1. 参数类型不匹配导致执行计划低效 #

Dapper自动推断的参数类型（比如DateTime/DateTime2）可能和SSMS手动执行时的类型不一致，导致SQL Server生成了低效的执行计划。

• 解决：添加参数时显式指定SQL类型：

  @params.Add("fromDate", from, DbType.DateTime2);
  @params.Add("toDate", to, DbType.DateTime2);
  

2. 参数嗅探引发的执行计划缓存问题 #

SQL Server缓存的执行计划可能适配某次特殊参数（比如极小的日期范围），但不适用于当前传入的参数范围，而SSMS执行时可能强制生成了新计划。

• 解决：
  • 在查询末尾添加OPTION (RECOMPILE)，强制每次生成新执行计划：

    OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY OPTION (RECOMPILE);
    

  • 或使用OPTION (OPTIMIZE FOR (@fromDate UNKNOWN, @toDate UNKNOWN))生成通用执行计划。

3. Brands表缺少合适索引 #

连接Brands表时，关联字段或过滤字段无索引会导致关联效率低下：

• 检查Brands.Guid1C是否有主键/唯一索引（关联字段必备）；
• 检查CalculatedTransactions是否有TransactionDate + BrandGuid的复合索引，可快速过滤日期并关联品牌。

4. NOLOCK提示的副作用 #

WITH(NOLOCK)虽能避免锁等待，但可能让SQL Server选择低效执行计划，同时存在脏读风险。可尝试去掉该提示，测试超时是否消失后再评估是否保留。

5. Dapper默认超时时间过短 #

Dapper默认命令超时为30秒，SSMS默认无限制。可尝试显式设置更长超时：

var result = await connection.QueryAsync(sqlQuery, @params, commandTimeout: 60);

内容的提问来源于stack exchange，提问作者Arkadi