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 8:35 PM > To: informix-list@iiug.org > Subject: Re: 4GL WITH REOPTIMIZATION > > > 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? Makes no difference. Regards Reinhard