Re: IDS 7.30 on server and i4gl 6.04 on client

Hi, first of all there are differences between the optimizers of both versions. The new optimizer should always be a bit better than the older version. = Both versions use a Cost Based Decision Optimizer if - and only if you set the configuration parameter OPTCOMPIND in your configuration file to 2 ( 1 is also possible if you do not use the isolation level "repeatable read" ). In this case the optimizer will calculate whether it's better to use an index or it's better to do a sequential scan. This decision can't be done as long as you have no idea of the value your are looking for. Imagine an indexed column with 99 percent "Y" values and 1 percent "N" values. If your query looks like this: select * from t1 where f1 =3D ? you cannot create your strategy. If I would open the cursor with the value "Y", the optimizer should prefer the sequential scan. But if I open the cursor with the value "N", it would be better to use the index. The strategy depends on the value I'm looking for. Optimization must be deferred until we know the value (open statement). If you would enter a value instead of the question mark, the server would optimize immediateley (while it is prepared). Otherwise optimization will always be done when you "open" your cursor. This behaviour didn't change in version 7.x. Therefore I think the differences you found are based on differences in the configuration files or your environment settings. The OPTCOMPIND = environment variable is available since version 7.x and overwrites the setting of your config file. As long as you had version 4GL 6.x you couldn't use this environment variable. I do NOT believe that Informix has build two different optimizers ( one for the prepare and one for the open ). If you are just looking for a fast solution, try the following steps: 1. Run "UPDATE STATISTICS" for your database ( or the tables involved in the query ) and start your query again. Run Update Statistics again whenever you created a new index. 2. If this will not help, set your OPTCOMPIND configuration parameter to 0 and re-start your IDS. Try your query again. ( This will force the index usage ). 3. If the optimizer decided to use the wrong index, then ask the optimizer to use the correct one. Use the new optimizer directive: select {+INDEX(tablename,indexname)} * from table ... Bye Stefan Weideneder =D8yvind Gjerstad wrote: > = > Has anyone tried this combination? > = > We upgraded some of our servers from ODS 7.14 to IDS 7.30 this weekend. > We then discovered that one front-end running 4gl 6.04 (and esql/c 7.14) > had big troubles with some queries. When place-holders were used in > prepared statements in the 4gl or esql/c code, no indexes where used, > we got only sequential scans. > = > For instance: > = > create table tab1(a char(2), b char(15), c char(4), d char(35)); > create unique index i_tab1 on tab1(a,b,c);> = > . > . > in the esql/c-program (sample code, untestet but get the picture): > = > $prepare sel from > "select d from tab1 where a =3D ? and b =3D ? and c =3D ?"; > $declare c_sel cursor for sel; > $open c_sel using p_a, p_b, p_c; > = > We got sequential scan, whilst this query used the unique index: > = > sprintf(s,"select d from tab1 where a =3D '%s' and b =3D '%s' and c =3D ' = %s'", > p_a,p_b,p_c); > $prepare sel from $s; > $declare c_sel cursor for sel; > $open c_sel; > = > When the same program was compiled with esql/c 7.30 it used the index. > = > This was on HP-UX 10.20. > = > One more strange thing was that one front-end running 4gl 4.12 > was OK! > -- > =D8yvind Gjerstad Systems dept Tollpost-Globe AS N-6301 =C5ndalsnes/N = orway > E-mail: ogj@it.tollpost.no Phone: +47 7122 6663 Fax: +47 7122 669 = 4