Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    16

    Unanswered: Stored Procedure

    Hi

    I need ur help
    I have created an SP in the following way.
    But this SP involves a lot of IF statement.
    I think these many IF statements could be a performance issue.
    Can anyone tell me if its possible to achieve the same with the CASE statement or any other way which will not be a performance issue

    The data in the table from the column retrieved is stored in this fashion
    01,02,03 the column in the table is of varchar(100)
    and the table going to be updated is also has the same varchar(100) column and it will be store in this fashion 11,12,13

    Create Procedure confirm_delete
    @app_id Int,
    as
    Begin
    Declare @trancnt int,
    @errnum int,
    @desctemp varchar(100),
    @desc_temp varchar(100)
    select @trancnt = @@trancount
    if @trancnt = 0
    begin transaction confirm_delete
    else
    save transaction confirm_delete
    If Exists(Select "X" from app
    where app_id = @app_id)
    Begin
    Select @desc=desc from rvw where app_id = @app_id
    If( charindex("01",@desc) > 0 ) select @desc_temp = "11,"
    If( charindex("02",@desc) > 0 ) select @desc_temp = "12," + @desc_temp
    If( charindex("03",@desc) > 0 ) select @desc_temp = "13," + @desc_temp
    If( charindex("04",@desc) > 0 ) select @desc_temp = "14," + @desc_temp
    If( charindex("05",@desc) > 0 ) select @desc_temp = "15," + @desc_temp
    If( charindex("06",@desc) > 0 ) select @desc_temp = "16," + @desc_temp
    If( charindex("07",@desc) > 0 ) select @desc_temp = "17," + @desc_temp
    If( charindex("08",@desc) > 0 ) select @desc_temp = "18," + @desc_temp
    If( charindex("09",@desc) > 0 ) select @desc_temp = "19," + @desc_temp
    If( charindex("10",@desc) > 0 ) select @desc_temp = "20," + @desc_temp
    update rvw
    set desc = @desc_temp
    where app_id = @app_id
    End
    Select @errnum = @@error
    If @@transtate not in ( 0,1 ) or @errnum != 0 or @errnum_rvw != 0
    Begin
    rollback transaction confirm_delete
    End

    End

    Regards
    Shalu
    Regards
    Shalu

  2. #2
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: Stored Procedure

    I doubt it's the "if" statements causing performance problems, unless were talking milli-seconds since they don't access any pages. Everything the if statement is working on is a variable or a constant.

    Consider moving the following code outside the transaction...

    If Exists(Select "X" from app
    where app_id = @app_id)
    Begin
    Select @desc=desc from rvw where app_id = @app_id
    If( charindex("01",@desc) > 0 ) select @desc_temp = "11,"
    ...
    If( charindex("10",@desc) > 0 ) select @desc_temp = "20," + @desc_temp

    This will reduce the length of the time transaction is active and reduce contention within the database.

    Verify the select and update statements are using indexes efficiently.

    Richard

  3. #3
    Join Date
    Jul 2003
    Posts
    16
    Thanks Richard
    Regards
    Shalu

Posting Permissions

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