.NET 代码中的 SQL 查询速度较慢，但​​不是交互式的

Question

我们使用的 ORM 正在执行从 .NET 到 SQL Server 的 sp_executesql 存储过程的调用。

当从 .NET 调用存储过程时，我们收到超时异常。

查看Profiler，我可以看到该查询确实需要很长时间才能执行。

查询本质上是：

exec sp_executesql N'SELECT DISTINCT
FROM [OurDatabase].[dbo].[Contract] [LPLA_1] ) [LPA_L1]
LEFT JOIN [OurDatabase].[dbo].[Customer] [LPA_L2]  ON [LPA_L2].[Customer_ID]=[LPA_L1].[CustomerId] AND [LPA_L2].[Data]=[LPA_L1].[Data])
WHERE ( ( ( ( ( [LPA_L1].[DealerId] = @DealerId1)) 
AND ( [LPA_L2].[Last_Name] = @LastName2))))',N'@DealerId1 varchar(18),@LastName2 varchar(25)',@DealerId1='1234',@LastName2='SMITH'

对我来说令人困惑的部分是：如果我将超时的查询复制并粘贴到 SQL Management studio 中并以交互方式执行它，它执行得很好。

有谁知道为什么通过 .NET 代码执行相同的查询会花费更长的时间？ （我能够重现这一点——从代码执行的查询始终超时，并且以交互方式执行的查询始终工作正常。）

感谢任何帮助。谢谢！

Answer 1

我见过几次的一件事是，索引字段上的查询参数的 nvarchar 和 varchar 类型之间是否不匹配。如果您在数据库中使用 varchar 并且未在 .Net 中显式设置参数类型（默认情况下将采用 nvarchar），则可能会发生这种情况。

在这种情况下，Sql Server 将选择更正确的选项，而不是性能更好的选项。数据库将被迫将表中该列的每个值转换为 nvarchar（保证成功且不会丢失信息），而不是仅仅将参数转换为 varchar（这是一种可能会丢失信息的缩小转换） 。不仅速度慢，而且 Sql Server 将无法再使用该索引。不用说，查询将需要更长的时间来运行。

Answer 2

我有同样的麻烦，从 .net 执行的过程需要太多时间（并且不会返回很多行）。我向 sql 发送一个字符串：“executestored_procedure@parameter1=value1”，然后复制它并在 sqlmanagementstudio 上运行，但一切都运行良好。这种情况的严重之处在于，在我的查询中，我只是从参数值中添加或删除一个字母来导致它。我很困惑。

作为信息，我正在使用全文索引和临时表进行分页，但就像我说的，相同的查询（我确信）在 sql Management Studio 中完美运行。

Answer 3

刚刚有同样的问题。

重建索引解决了这个问题。

也许问题在于 nvarchar 与 varchar 列上的索引的参数类型......？

Answer 4

我认为这是因为 sp_executelsql 旨在重用已编译的查询计划，因此当同一个查询再次命中它时，它不会重新嗅探参数，因此它最终会使用一个可能非常慢的计划（会告诉你为什么较慢的查询计划）与当前参数值。 sp_executesql 似乎使用不同的方法来选择索引，显然与纯文本查询相比，这是一种损坏的方法。

不同之处在于 sp_executesql 下“公司（表）”的表更新是通过一系列嵌套循环提供的，而文本查询是通过一系列哈希匹配提供的。两个版本之间的视图构造看起来是相同的（这是我所期望的）。不幸的是，剩下的部分非常复杂，他们似乎在中间做着完全不同的事情；即使提取“实际”执行计划也不会给出查询的各个子组件的实际执行时间。确实，我看不出 sp_executesql 有任何理由选择不同的东西，但它可靠地构建了一个明显较慢的计划。

参数嗅探是一个解决方案，因此您应该重命名参数名称，或者甚至可以交换 where 子句中的列名称，这会导致 sp_executesql 重新创建查询计划，而不是使用旧的慢速计划，当然这不是解决方案，但它不会将较慢的计划缓存这么长时间。

问候。

Answer 5

这是我发现的。我有一个非常复杂的存储过程，它总是计算信息并将数据放入 8 列 17 行矩阵中，因为它每月由 Crystal Reports 调用/运行。产品数据库位于 96 GB 的超快服务器上！最近它被缩减为 32 GB 虚拟机。虽然缩小了 - 它确实使应用程序在很多方面运行得更慢 - 直到添加了一些索引。

然后，到了运行这个 17 行矩阵月报的时间……正如你可以想象的那样，它超时了！

过程调用非常简单 - 3 个参数。开始日期、结束日期和要筛选的地区 - 空等于全部。这两个日期作为一个字符从 Crystal Reports 传入，然后这些存储的 PROC 参数在整个疯狂的存储过程中随处使用。

17 行中的每一行 - 基本上使用WITH 语句和疯狂连接来查找数据行，然后再计数/透视结果......这在本文中并不重要。

所以这里它被简化了............

CREATE PROCEDURE [dbo].[prcMonthlyStats]
@bDate          datetime
,@eDate         datetime
,@districtStr   varchar(120)
AS
BEGIN
SET NOCOUNT ON;

所以

--the @bDate and @eDate params were DIRECTLY used throughout the 2000 lines of SQL,
--to filter data inside and out of WITH statements and various other selects!
--
--TIMES OUT!

故事

CREATE PROCEDURE [dbo].[prcMonthlyStats]
@bDateStr       datetime
,@eDateStr      datetime
,@districtStr   varchar(120)
AS
BEGIN
SET NOCOUNT ON;

--FIX!  Declare 2 date time variables and simply assign the 2 date time parameters to them.
DECLARE @bDate      datetime
DECLARE @eDate      datetime
DECLARE @district   varchar(120)

--SET THE VARIABLES FROM THE PARAMETERS PASSED IN!  
SET @bDate = @bDateStr
SET @eDate = @eDateStr
SET @district = @districtStr
.....

--PRESTO! The optimizer could once again use indexes as it should.

的寓意是 - 优化器能够通过使用声明的日期时间来完成它的事情。

Answer 6

DealerId 或 Lastname 是 nvarchar（与 varchar 参数的类型不同）吗？

这可能会导致整个索引的转换。如果您发现这种情况，请发表评论，我会更详细地解释。