Using variables slows down query

Question

Post reply

Using variables slows down query

tember

SSC Enthusiast

Points: 103
More actions
April 11, 2002 at 12:41 pm

#158187

I have a stored procedure that contains the following lines of code:
if @PROD_CD = 22
begin
select @NEW_MAX_DRAW_NBR = max(DRAW_NBR) from TABLEA where PROD_CD = 22
select @OLD_MAX_DRAW_NBR = MAX_DRAW_NBR from TABLEB where PROD_CD = 22
end
else
begin
select @NEW_MAX_DRAW_NBR = max(DRAW_NBR) from TABLEA where PROD_CD = @PROD_CD
select @OLD_MAX_DRAW_NBR = MAX_DRAW_NBR from TABLEB where PROD_CD = @PROD_CD
end
PROD_CD 22 has over 500,000 rows in TABLEA.
As you can see, each branch of the 'if 'statement is essentially the same. This 'if' is in the middle of a small loop that executes for about 10 different PROD_CDs. We hard-coded PROD_CD for the value 22 because the execution time improved from ~3 seconds to less than 1/2 a second. Why would using a hard-coded value, compared to a variable (which is an int) make such a difference?
TIA
September

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

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

Eve SSCrazy Points: 2480 More actions · Answer 1

Have you tried to use 'sp_executesql' or execute(sql) instead of direct select statement

Antares686 SSC Guru Points: 125444 More actions · Answer 2

It shouldn't make any difference. Did you switch back to make sure was not due to caching? Also did you compare the execution plans?

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

tember SSC Enthusiast Points: 103 More actions · Answer 3

We didn't compare execution plans - we just had the exec statement in query analyzer and ran it. It took over 3 seconds when referencing the @PROD_CD variable and was instantaneous when using the hard-coded 22. When doing the comparison, and we did it a few times - the only thing we would change was the PROD_CD = @PROD_CD expression. A definitive answer is not that important, we are leaving the 'if' stmt in, but it a very curious performance problem. Can someone else try this out and see if they have the same problem?

September

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

If you post some DDL, I will make a test table and try it.

Steve Jones

steve@dkranch.net

mkg_1232000 Old Hand Points: 307 More actions · Answer 5

You haven't mentioned about any indexes on the tableA.

Can you try with clustered index on (PROD_CD,DRAW_NBR)

Then you can get rid of the MAX function ..