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

发布于 2024-12-11 20:36:30 字数 1399 浏览 0 评论 0原文

我正在尝试使用 SQL Server 2008R2 从 ADO.NET 运行查询。我使用 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) 

我使用了一些类似的子句,这就是为什么我有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)

I'm trying to run a query from ADO.NET using SQL Server 2008R2. I'm using a CTE to provide paging and adding parameters for @Offset and @Limit, which are both integers.

I'm building up a parameterized query depending on user inputs. The final output is this:

;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) 

I'm using a few like clauses which is why I have OPTION RECOMPILE. If I declare the parameters via SSMS and run like so:

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

I get a very quick response time (less than 1s). If I try this with ADO.NET, however, it takes forever. I've tried adding the parameters with both of these:

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

If there are only a few rows returned by the first query though and I drop the @Offset and @Limit filtering, I get a decent response time. Is there a way I can speed this up and use paging?

EDIT: I'm passing in the @postcode parameter (which is a string in .NET via this:

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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

梓梦 2024-12-18 20:36:30

您的 ADO.NET 代码传入参数 与您在 SSMS 中测试的数据类型不同的数据类型,并且您会遇到隐式转换问题。

不要使用

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

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

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

Your ADO.NET code is passing in a parameter of a different datatype than the one you are testing in SSMS and you are getting implicit cast issues.

Don't use

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

as this will auto create an nvarchar parameter and you will be getting implicit casts in your execution plan meaning that an index cannot be used. Instead pass in an explicitly created parameter of varchar type instead.

cmd.Parameters.Add("@postcode", SqlDbType.Varchar, 10)
橘寄 2024-12-18 20:36:30

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_FieldList_Data 表的主键(聚集)。

注释 2:检查您是否有 VALUE_2010 和邮政编码字段的索引。如果您有 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  

1) For @postcode parameter please specify the length.

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

2) Rewrite the query:

;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) 

Note 1: I assume that Key_Field is primary key (clustered) for List_Data table.

Note 2: Check if you have an index on VALUE_2010 and Postcode fields. If you have SQL 2008+ then you can create an filtered index:

--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  
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文