CODEWITHSUNDEEP
sql-servert-sqlquery-optimizationsql-server-2019
Coskun Ozogul
Coskun Ozogul

Reputation: 2469

Stored procedure takes too long to execute after a few weeks

My stored procedure works fine for a while and than, I don't know why, it takes too long to execute.

There is nothing extraordinary in the procedure.

What is broken after a while, I couldn't find.

The only thing that I do is altering it without any change and bingo. It works fine again.

Any idea, please ?

Here is the stored procedure :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetMainClientList] 
    @userId AS varchar(50)
AS
BEGIN

IF(@userId = '0')
SELECT 
     q.AccountNumber,
     ...(15 other fields)
FROM 
    SRV_CustomCaptions cc
INNER JOIN 
    MyTable q ON cc.Capt_Code = q.CategorieClient
LEFT JOIN 
    TurnoverByType tt ON q.CompteClient = tt.Turn_AccountNumber 
WHERE
    cc.Capt_Family = 'pers_type'
ELSE
SELECT 
      q.AccountNumber,
     ...(15 other columns)
FROM 
    SRV_CustomCaptions cc
INNER JOIN 
    MyTable q ON cc.Capt_Code = q.CategorieClient
LEFT JOIN 
    TurnoverByType tt ON q.CompteClient = tt.Turn_AccountNumber 
WHERE
    cc.Capt_Family = 'pers_type'
    AND q.CodeRepresentant=@userId

END

Upvotes: 0

Views: 71

Answers (1)

mikkel
mikkel

Reputation: 493

While OPTION(RECOMPILE) would probably solve your issue, I would suggest reading this article: https://www.sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/ which explains why if/else blocks in a stored procedure can mess with your query execution, and how you can fix it.

Upvotes: 0

Related Questions