Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    11

    Unanswered: Stored procedure performance

    Hi,
    I am having one procedure as follows:
    -------------------------------------------
    Create procedure TestProc1
    ( @param1 int
    if (@param1 is not null)
    select * from tableA where code=@param1
    else
    select * from tableA where 1 = 2
    end
    -------------------------------------
    Create procedure TestProc2
    ( @param1 int )
    select * from tableA where code=@param1
    ---------------------------------------------
    Index exists on tableA for the column code.

    My question is, which will be the most optimzed procedure Testproc1 or TestProc2? And why?
    Can some please help me to know this.

  2. #2
    Join Date
    Aug 2004
    Posts
    6
    I believe TestProc2 would be optimized since it would not have to evaluate a conditional before returning a resultset. With TestProc1, you must evaluate your conditional prior to determining which select statement to execute. In TestProc2, your only step is to execute the query.

  3. #3
    Join Date
    Jan 2004
    Location
    Shanghai,China
    Posts
    76
    I think the differential between proc1 and proc2 is procedure code parse.
    For proce1, sybase must maintain a decision tree.But the SQL execution plan of two procedures is same.

  4. #4
    Join Date
    Oct 2003
    Posts
    11
    Thank you both of you. My understanding was that TestProc1 would be optimized, reason; it does not have to even scan the index if the parameter is null.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •