Post reply

Local Variables in a Stored Procedure

  • 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

  • December 4, 2003 at 1:27 pm

    #484692

    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

  • December 5, 2003 at 5:18 am

    #484772

    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...

  • December 5, 2003 at 6:57 am

    #484794

    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

  • December 5, 2003 at 8:11 am

    #484835

    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-

  • December 5, 2003 at 8:50 am

    #484857

    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

  • December 5, 2003 at 1:31 pm

    #484913

    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.

  • December 5, 2003 at 2:17 pm

    #484924

    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

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

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