Re: 4GL WITH REOPTIMIZATION

Strange ... IDS isn't supposed to do that... Force the index with hint to optimizer... Usually IDS 9.4 (FC7 in HP-UX is ours) behave correctly but sometimes it doesn't choose the best plan (statistics updates and all the related stuff). In that cases we force the plan.... most of the times (...well always) an ORDERED directive will do the trick... J. 2008/9/10 Habichtsberg, Reinhard <RHabichtsberg@arz-emmendingen.de> > Hi all > > IDS 9.40FC9W2 > I-4GL 7.32 > > I need some help with prepared queries. The belonging application asks the > user to check some buttons and if checked choose a value which corresponds > to the button. You see the output of sqexplain. > > The first query is a simulation without prepare and fix values started with > dbaccess. It runs fast and uses the index. > > QUERY: > ------ > SELECT x1,x2 FROM xtable WHERE 1 = 1 AND (0= 0 OR x1= 1510239221 ) AND > (0=> 1 OR x2 = 230 ) ORDER BY x1 > > > Estimated Cost: 122 > Estimated # of Rows Returned: 222 > Temporary Files Required For: Order By > > 1) informix.xtable: INDEX PATH > > (1) Index Keys: x2 (Serial, fragments: ALL) > Lower Index Filter: informix.xtable.x2= 230 > > > The second query is constructed in a 4GL-Programm. It is prepared, the > cursor is opened WITH OPTIMIZATION. All the same the query is slow and the > index is not used. > > QUERY: > ------ > SELECT x1,x2 FROM xtable WHERE 1 = 1 AND (0= ? OR x1 = ? ) AND (0= ? OR> x2 = ? ) ORDER BY x1 > > Estimated Cost: 1235042 > Estimated # of Rows Returned: 43378 > Temporary Files Required For: Order By > > 1) informix.xtable: SEQUENTIAL SCAN > > Filters: ((0 = 0 OR informix.xtable.x1 = 1510239221 ) AND (0 = 1 OR > informix.xtable.x2 = 230 ) ) > > Please see the 4GL - Code: > > LET lc_SQL_Anw = " SELECT x1, x2 ", > " FROM xtable", > " WHERE 1 = 1 ", > " AND (0= ? OR x1 = ? ) ", > " AND (0= ? OR x2 = ? ) ", > " ORDER BY x1 " > > > PREPARE SEL_X FROM lc_SQL_Anw > > > DECLARE c_x CURSOR FOR SEL_X > > > INITIALIZE l_xtable.x1 TO NULL > INITIALIZE l_xtable.x2 TO NULL > LET l_var1 = 0 > LET l_var2 = 1 > LET l_x1 = 1510239221 > LET l_x2 = 230 > > -- In production the values of l_var1, l_var2, l_x1 and l_x2 will be user > input > > OPEN c_x USING l_var1, l_x1, l_var2, l_x2 WITH REOPTIMIZATION > > FETCH c_x INTO l_xtable.x1, l_xtable.x2 > while (sqlca.sqlcode = 0) > display l_xtable.x1, l_xtable.x2 > FETCH c_x INTO l_xtable.x1, l_xtable.x2 > end while > CLOSE c_x > > The 4gl-Code is only for testing. Later on it will be done with JAVA and > the > INFORMIX-jdbc-driver. > > REOPTIMIZATION doesn't seem to change anything. Any suggestion how to > optimize the query? > > TIA > Reinhard > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >