sql - 存储过程比 SSMS 中的查询慢

Question

更新:我已经解决了这个问题，请引用下面的解决方案。

我的存储过程比 SQL 查询慢。两者都在测试中直接在 SSMS 中执行。我需要反馈为什么以及如何解决。我可以看到查询在数据库中使用了不同的非聚集索引，我不确定为什么。

存储过程:

exec sp_executesql N'SELECT TOP 25 
[data_unit_id], [creation_date], [name], [parent_data_unit_id], [data_unit_identity_unique_name], [receiving_flow_status], [sending_flow_status], [target_connector], [closed], [nummer], [date], [receiver_endpoint], [receiver_name], [reference_document_id], [sender_endpoint], [sender_id], [sender_name], [receiver_email], [creditnote_total], [tax_number], [order_reference], [type], [responce_text] 
 FROM metadata 
 WHERE 
   (  (  creation_date >= @1 )  AND  (  closed = @2 AND  nummer LIKE @3 )  AND  creation_date <= @4 AND  creation_date >= @5 )  
 ORDER BY [creation_date] DESC 

',N'@1 bigint,@2 nvarchar(5),@3 nvarchar(4),@4 bigint,@5 bigint',@1=130288572000000000,@2=N'False',@3=N'%156',@4=130295155780753712,@5=130289107780753712

SQL查询:

SELECT TOP 25 
     [data_unit_id], [creation_date], [name], [parent_data_unit_id],   
     [data_unit_identity_unique_name], [receiving_flow_status], [sending_flow_status], 
     [target_connector], [closed], [nummer], [date], [receiver_endpoint], [receiver_name], 
     [reference_document_id], [sender_endpoint], [sender_id], [sender_name], [receiver_email], 
     [creditnote_total], [tax_number], [order_reference], [type], [responce_text] 
FROM metadata 
WHERE 
   ((creation_date >= 130288572000000000)
    AND (closed = 'False' AND nummer LIKE '%156')  
    AND creation_date <= 130295155780753712 
    AND creation_date >= 130289107780753712 
   )  
ORDER BY 
   [creation_date] DESC 

更新:如果我将 @3=N'%678' 更改为 @3=N'%78' 并将该变量的数据类型大小更改为 nvarchar(3)，则搜索时间将从 >30 秒变为 200 毫秒。数据库中 nummer 的数据类型是 nvarchar(300)。这是 SQL:

exec sp_executesql N'SELECT TOP 25 
    [data_unit_id], [creation_date], [name], [parent_data_unit_id], [data_unit_identity_unique_name], [receiving_flow_status], [sending_flow_status], [target_connector], [closed], [nummer], [date], [receiver_endpoint], [receiver_name], [reference_document_id], [sender_endpoint], [sender_id], [sender_name], [receiver_email], [creditnote_total], [tax_number], [order_reference], [type], [responce_text] 
     FROM metadata 
     WHERE 
       (  (  creation_date >= @1 )  AND  (  closed = @2 AND  nummer LIKE @3 )  AND  creation_date <= @4 AND  creation_date >= @5 )  
     ORDER BY [creation_date] DESC 

    ',N'@1 bigint,@2 nvarchar(5),@3 nvarchar(3),@4 bigint,@5 bigint',@1=130288572000000000,@2=N'False',@3=N'%56',@4=130295155780753712,@5=130289107780753712

解决方案:这是相似/冲突索引的问题，解决方案是删除其中一个。我是如何解决的:使用 SSMS 中的 SQL 查询查看执行计划以及使用的索引对象。慢速SP也一样吗？如果他们使用不同的索引，请尝试使用 SP 中的快速索引。如何强制使用特定索引的示例:

SELECT *
FROM MyTable WITH (INDEX(IndexName))
WHERE MyIndexedColumn = 0

Answer 1

SQL 可能存在通常称为参数嗅探的问题。一种解决方案是在过程中声明变量并将参数分配给它们:(我不知道为什么会这样，但我过去使用这种方法取得了一些成功)。

CREATE PROCEDURE [dbo].[SprocName]
@Parameter1     DATETIME,
@Parameter2         VARCHAR(30), .......

DECLARE @1 DATETIME
SET @1 = @Parameter1

DECLARE @2 VARCHAR(30)
SET @2 = @Parameter2

另一种方法是在查询结束时使用 OPTION (RECOMPILE)。

但是，这些并不是在所有情况下都有效。如果它们不起作用，那么您最好查看您的执行计划并优化您的存储过程。

---

这里有一篇关于参数嗅探的好书。

http://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/