SP is very slow, what to do - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SP is very slow, what to do

Author

Topic

khufiamalik
Posting Yak Master

120 Posts

Posted - 2009-07-10 : 07:22:28

Hello All,

I have a SP that has mutiple IF statements and in every IF Statement I have a SELECT query.

At a time only 1 IF condition will be true.

When I run this SP , it is taking about 15 seconds but when I remove all IF statements except the 1 statment, SP takes No time,

How can I resolve this

SP has 2 Parameters.
IF_Statement_Key Varchar(50)
KeyAsString INT (50)

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-10 : 08:08:26

Is the IF statement checking simple boolean flag or is there a query?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-10 : 08:18:28

Post the code used in the procedure

Madhivanan

Failing to plan is Planning to fail

khufiamalik
Posting Yak Master

120 Posts

Posted - 2009-07-10 : 09:38:10

CreatePROCEDURE [dbo].[PPH_ADM_GET_LOOKUP_VALUES]
@Reference_Code varchar(50) = NULL
,@Key_Value varchar(MAX) = NULL
AS
BEGIN

IF @Reference_Code = 'COUNTRY'
BEGIN
SELECT Country_ID AS [value], Country_Name AS [description]
FROM dbo.ADM_Country WITH (NOLOCK)
WHERE Active_Ind = 1
Order by 2
END
else IF @Reference_Code = 'PHONE_CODE'
BEGIN

SELECT '' AS [description], 0 as value

END
-- ELSE IF .................... More than 150n Conditions
END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-10 : 09:49:51

More than 150 conditions?
Each condition results in a different query?

I would do the following:
- analyze if there are similiar querys that can intelligent be altered to one query.
- create stored procedures for each query.
- Make decision in front end app which sp is to call.

et voilá

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-10 : 09:50:32

Nested IFs are VERY slow. I think it starts to show already at six levels deep.
What is the purpose with this SP?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-10 : 09:55:48

In your case, dynamic sql may actually be faster in the multipurpose SP of yours.

CREATE PROCEDURE dbo.PPH_ADM_GET_LOOKUP_VALUES
(
	@Reference_Code varchar(50) = NULL,
	@Key_Value VARCHAR(MAX) = NULL 
)
AS 

SET NOCOUNT ON

DECLARE	@Sample TABLE
	(
		kv VARCHAR(20),
		sqlstring VARCHAR(MAX)
	)

INSERT	@Sample
SELECT	'country', 'SELECT Country_ID AS [value], Country_Name AS [description] FROM dbo.ADM_Country WITH (NOLOCK) WHERE Active_Ind = 1 Order by 2' union all
SELECT	'PHONE_CODE', 'SELECT phone_ID AS [value], phone_Name AS [description] FROM dbo.ADM_Phone WITH (NOLOCK) WHERE Active_Ind = 1 Order by 2'

DECLARE	@SQL VARCHAR(MAX)

SELECT	@SQL = sqlstring
FROM	@Sample
WHERE	kv = @Reference_Code

EXEC (@sql)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources