Re: Stored Procedures and the Optimizer

By any chance are you passing the date values into your stored procedure as character? If you are it may cause the engine to convert the value to date for every row in the table - thus ignoring your index. Placing the values in date variables will allow it to use the index again. You might also want to check sqlexplain. Using dbaccess, try this: set explain on; update statistics for procedure <your proc name>; and check the sqexplain.out file to see what it thinks of your query. Good Luck! On Mon, 01 Apr 1996 15:59:22 -0800, Dick Counsell <dickc@wrq.com> wrote: >I have been playing with stored procedures (on Informix 7.11 UC1 >running on HP/UX 9.04) and have run into a problem I'm not sure >how to solve. My procedure includes a 'select' statement that >accesses our invoice table and links it to a few other tables. >At any one time I want to access the current month's data so I am >selecting 'WHERE invoice_date BETWEEN 3/1/96 AND 3/31/96'. > >This works fine when I have the date values hard-coded at the >time I create the procedure. The procedure is optimized >correctly and runs in about 30 seconds. > >However, I want to pass in the date value dynamically. When I >write the procedure to use variables for the date values (WHERE >invoice_date BETWEEN beg_date AND end_date) the sql is optimized >differently and the same procedure takes an hour to run with the >same date range. > >Is there a good way to have this part of the procedure optimized >at run time (when it knows the dates)? Or do you know another >way to get this optimized the way I want? I have thought of >having the stored procedure create a stored procedure, but even >if that's possible, it seems the code would be really klunky. ====================================================================== Craig Kickel | Unix/C/Database Consultant ckickel@cyberhighway.net | <This space intentionally left blank> ======================================================================