Re: PREPAREd statement optimization

malc_p@btinternet.com wrote: > A query. (4GL 7.20UD8 (both RDS & I4GL), IDS 9.30HC5, HP-UX 11i) > Does anyone know the process whereby the optimizer decides on the best > access path for a statement PREPAREd in 4GL? We've a query here in a > 4GL which is prepared with placemarkers and opened in a logic loop > later in the program, each time with different values in the (three) > placemarkers. > If you "SET EXPLAIN ON" in the 4GL, I note an sqexplain.out file is > created AT THE TIME OF the "PREPARE" statement. Each time the cursor is > OPENEd, no further sqexplain.out is produced. > I've done some test 4GLs and narrowed it down to the point where I > found that with a particular two sets of values run in one particular > order, the "OPEN" for the cursor is quick for the first set of values > and s-l-o-w for the second. If I reverse the order in which the values > are presented to the OPEN, it is quick both times. > e.g. > OPEN some_curs USING TODAY, "12/05/1999", 1000 - quick (< 1 second) > OPEN some_curs USING TODAY, "12/05/1999", 500 - s-l-o-w (2 minutes) > but: > OPEN some_curs USING TODAY, "12/05/1999", 500 - quick (1 second) > OPEN some_curs USING TODAY, "12/05/1999", 1000 - quick (< 1 second) > > ... where the values used are retrieved from other tables earlier on. > > I did a test by preparing separate statements with the absolute values > put in instead of placemarkers, and as expected the costs are different > (due to data skew etc). > So the query is, has the optimizer decided on a query plan when it got > the PREPARE so when a set of values that don't meet the optimization > goal are presented it uses the wrong path? > How can I change this? > Thanks > Malc > I've seen your plea for help - but have you looked in the manuals at OPEN? The WITH REOPTIMIZATION clause? If the query has placeholders, the optimizer doesn't have the information about the values, and it might in theory use different query plans for different values but selects a single query plan - on the basis of I'm not quite sure what - and then uses that same query plan each time. If you (can) use WITH REOPTIMIZATION, the optimizer has the values and may choose a different query plan because of the values. Without knowing what your query does with the values, it is hard to assess why there's a dramatic difference between the times; however, it is likely related to the distribution of the values for the data in the table - or possibly to unexpected correlations between values which the optimizers assumes are independent. Correlations throw the calculations off, and sometimes do so horribly. Question: does I4GL support OPEN WITH REOPTIMIZATION? Somehow, I think not - at least, not directly. Especially not 7.20 - you'd be in with a chance with 7.3x. If 7.3x supports WITH REOPTIMIZATION directly, that's great. If not, you would probably try an SQL block: SQL OPEN cursor WITH REOPTIMIZATION USING $var1, $var2, ... END SQL If that didn't work, you'd use a small piece of ESQL/C to collect the cursor name and the values off the I4GL stack and execute the OPEN in ESQL/C. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/