Results 1 to 5 of 5
  1. #1
    Join Date
    May 2015
    Posts
    2

    Answered: Stored procedure dynamic where clause poor performance

    Hello all,

    Can someone explain me why the SQL below is taking too long to respond?
    I've built a Stored procedure with 5 input parameters and the idea is to dynamic use them.
    The table is large about 3 million records.

    SELECT column1,
    column2
    FROM tableA
    WHERE (PARAMETER IS NULL OR column3 = PARAMETER)


    Please note if I remove the condition "PARAMETER IS NULL OR" the search is extremely fast.

  2. Best Answer
    Posted by dav1mo

    "Or, You could dynamically build your cursor based on the inputs you received. Also, write the SQL for all possibilities then run the one that meets your inputs."


  3. #2
    Join Date
    Sep 2003
    Location
    United States
    Posts
    65
    Provided Answers: 1
    You might have done it - statistics.

    If that does not work - please post the access plan.
    >>
    >> Learn PHP/MySQL for free https://www.youtube.com/watch?v=mpQts3ezPVg
    >> Free Web Hosting with PHP, MySQL, Website Builder : http://www.000webhost.com/862861.html

  4. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    DB2 creates a statement access plan before it knows the actual parameter values by default.
    If you want to changes such a behavior for stored procedures, you can do the following:
    CALL SYSPROC.REBIND_ROUTINE_PACKAGE ('P','MYPROCSCHEMA.MYPROCNAME','REOPT ALWAYS')
    or call the following before creating the procedure in your session:
    CALL SYSPROC.SET_ROUTINE_OPTS('REOPT ALWAYS')
    Regards,
    Mark.

  5. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Or, You could dynamically build your cursor based on the inputs you received. Also, write the SQL for all possibilities then run the one that meets your inputs.
    Dave

  6. #5
    Join Date
    May 2015
    Posts
    2
    Hello All,

    Thank you for your replies.

    I've tried mark.b suggestion but It didn't work.
    The system has DB2 version V7R1. I changed QAQQINI parameter REOPTIMIZE_ACCESS_PLAN to *YES but it didn't work.

    I ended up with dav1mo suggestion.

    Thank you

Posting Permissions

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