存储过程比ssm中的查询慢。

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

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

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.

我的存储过程比SQL查询慢。它们都在测试中直接在ssm中执行。我需要反馈为什么和如何解决。我可以看到查询在DB中使用不同的非聚集索引，我不知道为什么。

Stored procedure:

存储过程:

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 query:

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

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:

更新:如果我将@3=N'%678'更改为@3=N'%78'，并将此变量的数据类型大小更改为nvarchar(3)，搜索将从>30 s变为200ms。DB中的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

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:

解决方案:这是相似/冲突索引的问题，解决方案是删除其中一个。解决方法:使用SSMS中的SQL查询查看执行计划和使用哪个索引对象。慢SP也是一样吗?如果他们使用不同的索引，尽量使用SP中的快速索引。

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

2 个解决方案

#1

SQL can have a problem often referred to as Parameter sniffing. One solution to this is to declare variables within the procedure and assign the parameters to them: (I don't know why this works, but I have had some success with this method in the past).

SQL可以有一个经常被称为参数嗅探的问题。一种解决方法是在过程中声明变量并为它们分配参数:(我不知道为什么这样做，但是我过去在这个方法上取得了一些成功)。

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

DECLARE @1 DATETIME
SET @1 = @Parameter1

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

another is to use OPTION (RECOMPILE) at the end of your query.

另一种方法是在查询结束时使用选项(重新编译)。

However, these don't work in all instances. If they don't work then you will be best to look at your execution plans and optimise your sproc.

但是，这些方法在所有实例中都不起作用。如果它们不工作，那么您最好查看您的执行计划并优化sproc。

here is a good read around parameter sniffing.

下面是关于参数嗅探的详细阅读。

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

#2

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

#1

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

DECLARE @1 DATETIME
SET @1 = @Parameter1

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