4GL WITH REOPTIMIZATION

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= ? ORx2 = ? ) 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

Habichtsberg, Reinhard wrote: > 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 > I find it strange that the WITH REOPTIMIZATION doesn't change things... A few questions: - What is the 4GL datatype of the variables? - Do they agree with the column datatypes? - How did you get the query plan for 4GL? - Assuming you get it from a sqexplain.out file, how many queries do you get on the file? 1 or 2? And a few more comments: - I believe "WITH REOPTIMIZATION" is not allowed with JAVA. Please check. Maybe I'm wrong or things have changed - You could use optimizer hints in the query - You could use external optimizer hints I don't like the last two options... But you may want to consider them. Regards -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...