Re: 4GL WITH REOPTIMIZATION

> -----Original Message----- > From: informix-list-bounces@iiug.org > [mailto:informix-list-bounces@iiug.org]On Behalf Of Fernando Nunes > Sent: Wednesday, September 10, 2008 4:54 PM > To: informix-list@iiug.org > Subject: Re: 4GL WITH REOPTIMIZATION > > > 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 > > > Thank's Fernando. Answers see below: > I find it strange that the WITH REOPTIMIZATION doesn't change > things... A few > questions: > > - What is the 4GL datatype of the variables? The type of l_var1 and l_var2 is INTEGER, the type of l_x1 and l_x2 is DEFINED .. LIKE, so l_x1 is INTEGER and l_x2 is SMALLINT > - Do they agree with the column datatypes? Yes, see above > - How did you get the query plan for 4GL? SET EXPLAIN ON in the 4gl code. The sqexplain.out is written to the $HOME ofthe user who executed the program on the host where the Informix Server Instance reside. > - Assuming you get it from a sqexplain.out file, how many > queries do you get on > the file? 1 or 2? Only 1. > > 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

Habichtsberg, Reinhard wrote: >> -----Original Message----- >> From: informix-list-bounces@iiug.org >> [mailto:informix-list-bounces@iiug.org]On Behalf Of Fernando Nunes >> Sent: Wednesday, September 10, 2008 4:54 PM >> To: informix-list@iiug.org >> Subject: Re: 4GL WITH REOPTIMIZATION >> >> >> 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 >>> > > Thank's Fernando. Answers see below: > >> I find it strange that the WITH REOPTIMIZATION doesn't change >> things... A few >> questions: >> >> - What is the 4GL datatype of the variables? > > The type of l_var1 and l_var2 is INTEGER, the type of l_x1 and l_x2 is > DEFINED .. LIKE, so l_x1 is INTEGER and l_x2 is SMALLINT > >> - Do they agree with the column datatypes? > > Yes, see above > >> - How did you get the query plan for 4GL? > > SET EXPLAIN ON in the 4gl code. The sqexplain.out is written to the $HOME of> the user who executed the program on the host where the Informix Server > Instance reside. > >> - Assuming you get it from a sqexplain.out file, how many >> queries do you get on >> the file? 1 or 2? > > Only 1. > >> 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 Can you try that with the statement cache turned off? -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...