Stored Proc performance slow

Question

select  * from   FOO.MBR_DETAILS where BAR= 'BAZ' and MBR_No = '123'

execution time = 0.25 seconds

CREATE PROCEDURE My.MEMBER_SEARCH
(
        i_BAR varchar(3),
        i_member_surname varchar(50),
        i_member_code varchar(10),
        i_member_given_name varchar(50)
)

    RESULT SETS 1
    LANGUAGE SQL
BEGIN
   DECLARE c1 cursor with return for

          select *
          FROM FOO.MBR_DETAILS m
          WHERE
            BAR= i_BAR
            and  (i_member_code = '' or  m.MBR_No = i_member_code)
            and (i_member_surname = '' or  m.surname = i_member_surname)
            and (i_member_given_name = '' or  m.given_names  LIKE  '%'||i_member_given_name||'%');


    OPEN c1;
END

call My.MEMBER_SEARCH('BAZ','','123','')

execution time = 1.9 seconds

I thought both queries should have a similar time as i_member_surname and i_member_given_name are both empty they would not be evaulated.

Answer 1

The solution is to enable REOPT ALWAYS for any stored procedure that runs a flexible, parameter-driven search.

The REOPT ALWAYS option will force the optimizer to analyze the input parameter values and come up with a new access plan every time the procedure is executed, instead of just once when the procedure is compiled. Although REOPT ALWAYS adds a few extra milliseconds of optimizer overhead for each and every execution of the stored procedure, that is most likely faster than continually reusing the one-size-fits-all access plan that the optimizer guessed at while initially compiling the stored procedure.