Re: PowerBuilder 6 and Informix SE Query Problem

Art S. Kagel wrote: > Rajendra Singh wrote: > > > > I am running PowerBuilder 6 under Windows NT using the native Informix drivers > > (to connect to my database (SE) running under HP-UX 9.07). > > > > I have a simple SELECT statement as follows: > > SELECT a,b,c,d FROM table1 WHERE e = "X";> > > > When I run it in a PB application it takes a very long time to execute the > > statement even though there's an unique index on "e". When I run it on the > > server (using dbaccess), it returns almost instantly. > > > > I used "SET EXPLAIN" and found that when the PB application executes it, > > Informix is using a SEQUENTIAL SCAN instead of the index. Why is it doing this? > > > > I tried setting OPTIMIZATION to low, but that didn't help. I tried dropping and > > rebuilding the index, that didn't work either. I tried modifying the SQL as > > follows: > > SELECT a,b,c,d FROM table1 WHERE e = "X" AND e = "X" AND e = "X";> > (a suggestion from the Informix FAQ) > > ... but this didn't help. > > > > How can I force Informix to use the index that already exists on column "e"? > > Why is it ignoring the index? > > Does it use the index when you run the query in dbaccess? If not then > powerbuilder is somehow mucking with the query compare the two > sqexplain outputs to see if the query itself is somehow different from > what you asked for in PB. Also is the "X" a literal or a parameter, ie > PB host variable? That WILL make a difference and will show in the > sqexplain output as a question mark (?). > > Also have you updated statistics on the table? That can help. > > Art S. Kagel If the above does not help, you may insert a ORDER BY E clause. This will most probably trick out the optimizer and force it to use the index. At least it does in our applications (whether or not an UPDATE STATISTICS has been executed - what still should be considered as necessary). Regards -- Helmut Leininger Bull AG / Vienna Unix Support Email: Helmut.Leininger@bull.net This opinion is mine and not necessarily that of my employer. No guarantees whatsoever.