ADO.NET 超时但在 SSMS 中工作正常
我正在尝试使用 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技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您的 ADO.NET 代码传入参数 与您在 SSMS 中测试的数据类型不同的数据类型,并且您会遇到隐式转换问题。
不要使用
,因为这会自动创建一个
nvarchar
参数,并且您将在执行计划中获得隐式转换,这意味着无法使用索引。相反,传入显式创建的varchar
类型参数。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
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 ofvarchar
type instead.1) 对于@postcode参数请指定长度。
cmd.Parameters.Add("@postcode", SqlDbType.VarChar, 10).Value = str
2) 重写查询:
注释 1:我假设
Key_Field
是List_Data
表的主键(聚集)。注释 2:检查您是否有 VALUE_2010 和邮政编码字段的索引。如果您有 SQL 2008+,那么您可以创建过滤索引:
1) For @postcode parameter please specify the length.
cmd.Parameters.Add("@postcode", SqlDbType.VarChar, 10).Value = str
2) Rewrite the query:
Note 1: I assume that
Key_Field
is primary key (clustered) forList_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: