ADO.NET超时但在SSMS中正常工作

Question

我正在尝试使用ADO.NET从SQL Server 2008R2运行查询。我正在使用CTE提供分页，并添加参数@Offset和@Limit，两者都是整数。

我根据用户输入构建参数化查询。最终输出如下：

;WITH Results_CTE AS (
    SELECT  ld.* , ROW_NUMBER() OVER (ORDER BY Key_Field) AS RowNum  
    FROM list..List_Data ld 
    WHERE VALUE_2010 IS NOT NULL  
    AND  Postcode LIKE @Postcode + '%' 
) SELECT * FROM Results_CTE 
    WHERE RowNum > @Offset AND RowNum < @Offset + @Limit 
OPTION (RECOMPILE) 

我正在使用一些like子句，这就是为什么我有OPTION RECOMPILE。如果我通过SSMS声明参数并这样运行：

declare @postcode varchar(10) = 'SW1 1AA';
declare @Offset int = 0;
declare @Limit int = 10;

我得到了非常快的响应时间（小于1秒）。然而，如果我尝试使用ADO.NET，它会花费很长时间。我已经尝试使用以下两种方式添加参数：

cmd.Parameters.AddWithValue("@Offset", startRowIndex) // times out
cmd.Parameters.AddWithValue("@Limit", limit)

cmd.Parameters.Add(New SqlParameter("@Offset", SqlDbType.BigInt)) // also times out
cmd.Parameters.Item("@Offset").Value = startRowIndex
cmd.Parameters.Add(New SqlParameter("@Limit", SqlDbType.BigInt))
cmd.Parameters.Item("@Limit").Value = limit

如果第一个查询返回的行数很少，并且我删除了@Offset和@Limit过滤器，我可以获得相当不错的响应时间。有没有办法加快速度并使用分页呢？

编辑：我正在通过以下方式将@postcode参数（.NET中的字符串）传递进去：

cmd.Parameters.AddWithValue("@Postcode", normalizedPostcode)

- Echilon

---

@close voter。这不是重复的问题。之前的问题是关于解释变量而不是参数的不同行为。SQL Server对这两者的处理方式是不同的。 - Martin Smith

你能展示一下传入 @postcode 参数的代码吗？ - Martin Smith

这是否确定是SQL超时了呢？你确定没有其他可能导致超时，比如说由于在执行操作时仍有大量连接处于开启状态之类的原因？ - Chris

如果我在.NET中删除“Offset”和“Limit”的子句，它每次都能正常工作，但仅适用于小数据集。 - Echilon

有趣；虽然不是一个真正的答案，但常见的尝试是查看两个用法之间的差异；例如，SSMS通常具有不同的“SET”选项（可以使某些索引无效），或者不同的隔离级别等。另外，您是否尝试过“OPTIMIZE FOR UNKNOWN”而不是“RECOMPILE”？ - Marc Gravell

Answer 1

你的 ADO.NET 代码传入了一个不同于你在 SSMS 中测试的参数类型，并且出现了隐式转换问题。请参考这篇文章。

不要使用：

cmd.Parameters.AddWithValue("@postcode", normalizedPostcode)

因为这会自动创建一个nvarchar参数，并且您将在执行计划中得到隐式转换，这意味着索引无法使用。相反，请传递一个明确创建的varchar类型参数。

cmd.Parameters.Add("@postcode", SqlDbType.Varchar, 10)

- Martin Smith

---

非常好的技巧。在.NET中精确调整参数到DBType可以获得更快的响应。 - Echilon

Answer 2

1) 对于 @postcode 参数，请指定长度。

cmd.Parameters.Add("@postcode", SqlDbType.VarChar, 10).Value = str

2) 重写查询语句：

;WITH Results_CTE AS (
    SELECT  ld.Key_Field, ROW_NUMBER() OVER (ORDER BY Key_Field) AS RowNum  
    FROM list..List_Data ld 
    WHERE VALUE_2010 IS NOT NULL  
    AND  Postcode LIKE @Postcode + '%' 
) SELECT * FROM Results_CTE a
INNER JOIN list..List_Data b ON a.Key_Field = Key_Field
WHERE RowNum > @Offset AND RowNum < @Offset + @Limit 
--OPTION (RECOMPILE) 

注意1：我假设Key_Field是List_Data表的主键（聚集索引）。

注意2：请检查您是否在VALUE_2010和Postcode字段上拥有索引。如果您使用的是SQL 2008+，则可以创建一个过滤索引：

--UNIQUE if Postcode has unique values for VALUE_2010 IS NOT NULL
CREATE [UNIQUE] INDEX aaa
ON MySchema.List_Data (Postcode)
WHERE VALUE_2010 IS NOT NULL  

- Bogdan Sahlean

---

这似乎可以提高几毫秒的速度，但具体效果因情况而异。为了保险起见，这样做也无妨。 - Echilon