Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: which code block has more overhead?

    Hi,

    I have a stored procedure with a flag param.. let's call it 'iscurrent'. Based on the value of iscurrent, which block of code would you use in your stored procedure?.. basically, I'm wondering if it makes sense to have a check of iscurrent at every iteration in the WHILE loop or execute a separate block completely when iscurrent = 1 ( it just makes the procedure longer and there is repeating of certain statements that are common to both cases, that is when iscurrent = 0 or 1) .. thanks!

    Sorry about the lack of indentation -- it's doesn't seem to work!

    SOLUTION 1 --
    IF iscurrent = 1 THEN
    WHILE (endInterval <= endTime) DO
    -- statement 1
    -- statement 2
    --- extra statements that need to be executed when iscurrent = 1
    END WHILE;

    ELSE
    WHILE (startTime <= endTime) DO
    -- statement 1
    -- statement 2

    END WHILE;
    END IF;

    OR


    SOLUTION 2 --

    WHILE (startTime <= endTime) DO
    -- statement 1
    -- statement 2
    IF iscurrent = 1 THEN
    ---- statement 3
    ELSE
    ---- statement 4
    END IF;

    END WHILE;
    Last edited by db2user24; 08-20-10 at 13:00.

  2. #2
    Join Date
    Nov 2008
    Posts
    48
    SOLUTION 1 looks better as if is executed only once

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    I think so too.. the checking of the flag at every iteration seems like it will add some overhead.. but solution 1 has a bunch of statements that are repeated in the IF and ELSE blocks so was wondering.. so the question is more lines of code, less overhead or less lines of code, more overhead..

  4. #4
    Join Date
    Apr 2009
    Posts
    42
    the checking of IF statement will actually not add sinificantly overhead. the problem your stored procedure running slow is that do not mix procedure languge like IF, WHILE etc with SQL like insert, update, select and delete. this causes context switch which means procedure language execute in an engine while SQL executed in another engine by db2. in your case, try to rephase the WHILE statement see if the SQL could be executed only once.

Posting Permissions

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