CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Naha
Naha

Reputation: 536

Stored Procedure slower than query in SSMS

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.

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:

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:

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:

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

Upvotes: 2

Views: 5415

Answers (2)

Naha
Naha

Reputation: 536

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

Upvotes: 2

Sam
Sam

Reputation: 7313

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).

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.

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/

Upvotes: 3

Related Questions