PERFROMANCE ON A STORE PROCEDURE

Question

Post reply

PERFROMANCE ON A STORE PROCEDURE

Susane

SSC-Addicted

Points: 491
More actions
July 13, 2006 at 5:04 pm

#112858

I have store procedure which has like 40 parameters and everything seems to be optional

If I write a store procedure something like below
Create procedure dbo.sp_test
@PERIOD CHAR(8),
@CUSTOMER CHAR(15) = -1,
@STATE CHAR(2) = ' ',
@ CANCELLED CHAR(1) = ' ',
@LOCATION CHAR(25) = ‘ ‘,
@SOURCE CHAR(2) = ‘ ‘,
.
.
.
.
.
.
.
.
.
.

Select * from test
Where PERIOD = ‘curr_mon’
AND ((@CUSTOMER = -1)or (X.PS_CUSTOMER_NUM = @CUSTOMER) or (@CUSTOMER IS NULL AND S.CUSTOMER IS NULL))
AND ((@SOURCE = ' ')or (SOURCE = @SOURCE) or (@SOURCE IS NULL AND SOURCE IS NULL))
AND ((@LOCATION = ' ')OR(LOCATION = @LOCATION) OR (@LOCATION IS NULL AND LOCATION IS NULL))
………

This sp is taking a long time to run I mean its taking more then a minute to run

Is there any best way to rewrite this

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Andy-312723 Old Hand Points: 311 More actions · Answer 1

@LOCATION CHAR(25) = ‘ ‘,

AND ((@LOCATION = ' ')OR(LOCATION = @LOCATION) OR (@LOCATION IS NULL AND LOCATION IS NULL))

I would think that your last OR condition is never going to be hit because if you pass NULL to your query then its going to be replaced with ' '. Therefore your always doing an extra condition.

I would personally do this.... Which probably isn't the fastest way either...

@Location VARCHAR(25) = null,

AND ( ISNULL(@Location,Location) = Location)

Philip Yale-193937 SSCarpal Tunnel Points: 4603 More actions · Answer 2

I'd write this using dynamic SQL.

DECLARE @sql = nvarchar(4000)

SET @sql = 'Select * from test Where PERIOD = ''curr_mon'''

IF @CUSTOMER is not null

SET @sql = @sql + ' AND ((' + @CUSTOMER + '= -1)or (X.PS_CUSTOMER_NUM =' + @CUSTOMER + ') '

IF @SOURCE IS NOT NULL

SET @sql = @sql + ' AND ((' + @SOURCE + ' = '' '')or (SOURCE =' + @SOURCE + ')'

IF @LOCATION IS NOT NULL ..... etc.

exec sp_executesql @sql

I've converted similar SPs before, and the increase in performance has been huge, since the resulting query only references those columns that are relevent.

Zubeyir SSCrazy Points: 2559 More actions · Answer 3

Making the "or" query into a dynamic statement is a good idea for this case but you should always keep in mind that the optimiser will have to optimise your query since it changes every time you run it so the dynamic statement.

michanne SSCrazy Points: 2329 More actions · Answer 4

You have more than one option but you need to test them for best performance. It seems to depend on the number of variables you have to make optional. Sometimes dynamicsql is best and sometimes a case is better:

Select * from test

Where PERIOD = ‘curr_mon’

AND location = case when @location = '' then location else @location end

and Source = case when @source = '' then source else @source end

The above will only work if there is no chance of entering an empty string as an actual value.

It also helps if you can get an understanding of the underlying business logic. For example if @customer is always called only with @location you can exit the query with and if statement. I.E if @customer and @location are not null/empty then run this query and exit else do blah blah blah.