Local Variables in a Stored Procedure

Question

Post reply

Local Variables in a Stored Procedure

azarcr

SSC Enthusiast

Points: 190
More actions
December 4, 2003 at 8:13 am

#107419

I have a stored procedure which uses about 30 local variables declared at the begining of a stored procedure and then set to a value for reference throughout the code. For example:
DECLARE @l_deleted_status INTEGER
SET @ldeleted_status = 50025
SELECT * FROM TBL_TRANSACTION
WHERE TBL_STATUS = @ldeleted_status = 50025
This is a simple example but represents the basis of the code. When I run the procedure it takes about 25 minutes to execute. When I replace all of the @l_ values throughout the procedure with just the values, for example:
SELECT * FROM TBL_TRANSACTION
WHERE TBL_STATUS = 50025
The procedure consistently runs in about 2 minutes. Does anyone have any ideas as to why?
Thanks
Edited by - azarcr on 12/05/2003 06:42:09 AM

Viewing 8 posts - 1 through 7 (of 7 total)

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

ThomasH SSCertifiable Points: 7437 More actions · Answer 1

How long does it take if you execute it

as a script instead of SP? Using @Vars,

and using constants.

If both are ~ 2 mins, then the SP should

be recompiled prior to execution.

Once you understand the BITs, all the pieces come together

thornj@battelle.org SSC Rookie Points: 47 More actions · Answer 2

The example you show does not declare the variable types, for example:

DECLARE @l_deleted_status INT

I really dont know if this will make a difference but it might...

lambje SSCrazy Points: 2085 More actions · Answer 3

I really wouldn't expect to see much, if any, difference. One question though, why does your WHERE clause read

WHERE TBL_STATUS = @ldeleted_status = 50025

and not

WHERE TBL_STATUS = @ldeleted_status

? Not certain what thornj was talking about as I see your declaration. His code is more typical, though, where INTEGER is specified as INT. Actually, if you hadn't declared the var it would have thrown an error.

The other thing, probably as a result of having worked with Sybase for so long, is that I typically use SELECT @var = nnn as opposed to SET. I honestly don't know what the difference would be, if any, as I've never taken the time to read up on it. You could try a SELECT and see if it makes any difference.

Good luck

cle9 SSC Enthusiast Points: 111 More actions · Answer 4

azarcr,

Do you have an index created in the table for "TBL_STATUS" ? if not, try creating an

index for this column and see if it will help speed up a bit.

Good Luck.

CL-

kkeeffe SSC Eights! Points: 989 More actions · Answer 5

I remember having this problem too - particularly when using dates. My guess at that time was that "knowing" the date value allowed the query optimizer to do something that it couldn't do when the value was unknown - maybe looking thru the index up front?.

I'd love to know more about this.

regards

k2

kstefan Say Hey Kid Points: 667 More actions · Answer 6

I think it is because the query optimizer cannot decide as accuratately with a variable as with a value. Is is described in this article http://www.microsoft.com/sql/techinfo/tips/development/July16.asp

which applies to SQL 7.0. It may well apply to SQL 2000 as well.

ThomasH SSCertifiable Points: 7437 More actions · Answer 7

I'm just curious, but if your SP did a Dynamic SQL (EXEC) with variables all replaced with constants, would your code run in the faster 2 min. time frame?

This way, SQL Server will recompile the script using the embedded constants.

P.S. Just curious, I know there are concerns using Dynamic SQL. I have had instances where the straight inline SQL code ran 10-100 times slower then the identicle code placed within an EXEC (). It seems most dramatic when the contents of some tables are getting massively changed in the earlier part of the SP.

Once you understand the BITs, all the pieces come together