存储过程比 SSMS 中的查询慢 [英] Stored Procedure slower than query in SSMS

问题描述

更新:我已经解决了这个问题，参考下面的解决方案.

UPDATE: I have solved the problem, refer to solution below.

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

My stored procedure is slower than the SQL query. Both are executed in SSMS directly in the test. I need feedback why and how to solve. I can see that the queries are using different non-clustered index in the DB and I'm not sure why.

存储过程:

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 毫秒.DB 中 nummer 的数据类型是 nvarchar(300).这是 SQL:

UPDATE: If I change @3=N'%678' to @3=N'%78' and the datatype size for this variable to nvarchar(3) the search goes from >30 s to 200ms. The datatype of nummer in the DB is nvarchar(300). This is the 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 中的快速索引.示例如何强制使用特定索引:

Solution: It was problems with similiar/conflicting indexes and the solution was to drop one of them. How I solved it: With the SQL query in SSMS have a look at the Execution plan and which index object is used. Is it the same for the slow SP? If they use different indexes try to use the fast one in SP. Example how force use of specific index:

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

推荐答案

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

Solution: It was problems with similiar/conflicting indexes and the solution was to drop one of them. How I solved it: With the SQL query in SSMS have a look at the Execution plan and which index object is used. Is it the same for the slow SP? If they use different indexes try to use the fast one in SP. Example how force use of specific index:

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

这篇关于存储过程比 SSMS 中的查询慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！