David McPaul wrote:

> Some thoughts.
>
> How up to date are your statistics?
>
> Obviously if we can remove the sequential scans we should improve
> performance

I HATE that attitude. It is completely false. Sequential scans are not bad,
if they are appropriate. If you are going to read ALL records from a table,
then the fastest method will be a sequential scan. Also, a dynamic hash
join may be the fastest way to join 2 large tables.

> So what is the size of these 2 tables?

This, and "how many records are you expecting back?", would be good
starting points.

As to the actual problem, I defer to Peter Marley. He is an expert on
extracting and processing data from a Baan database. As he alluded to, Baan
implements a non-relational design in Informix tables, and it makes the
query optimiser as good as useless. For that reason, the use of statistics
has little impact, unless you create your own indexes.

The only thing I would add to Peter's comments is, make sure OPTCOMPIND is
set to 0.

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /|
| Mydas Solutions Ltd http://MydasSolutions.com |///// / //|
| +-----------------------------------+//// / ///|
| |We value your comments, which have |/// / ////|
| |been recorded and automatically |// / /////|
| |emailed back to us for our records.|/ ////////|
+----------------------+-----------------------------------+-----------+

sending to informix-list