DB performance : 45 seconds for a query ?

Hi, I have a frustrating question concerning Informix performance (on a UNIX system). The basic database structure is pretty simple : there is 1 type of table with 6 (significant) fields : - reference nr. - date - from - to - amount - currency There are 12 tables of this type : one for each of the last 12 months. Each table should hold about 3 to 4 million records. There is an index for each field. Users want to be able to query this database based on : - the reference number - the date - the amount (larger than xxx, smaller than xxx, equal to xxx) etc. For the moment, I implemented the queries based on a simple union of 12 select statements. The problem is that, even with 11 empty tables and 1 table containing only 1 milllion records, the execution of a query of the type 'amount > xxx' takes about 45 seconds... Are there any (obvious or less obvious) improvements that I could make to the database structure, queries etc. in order to get acceptable response times ? I already tried putting everything in a stored procedure, but that didn't really help.. Thanks for any pointers, Michel.

Hi Michel This sounds like ugly database-design. You sould create only one table whith a month field. Optimizing a query for one table is far easier than with union. You should add this month field in every index your mentioned and access through than field. Even with about 40 million records access-time should be better. But I'm sure you forgot update statistics. This will improve performance far more. And you should read the documentation for 'set explain on'. So you can get the access path. It should have tell you, than the engine made a full table scan instead of using your index. Michel Dalle wrote: > Hi, > > I have a frustrating question concerning Informix performance (on a UNIX > system). > > The basic database structure is pretty simple : there is 1 type of table with > 6 (significant) fields : > - reference nr. > - date > - from > - to > - amount > - currency > > There are 12 tables of this type : one for each of the last 12 months. Each > table should hold about 3 to 4 million records. There is an index for > each field. > ... _________________________________________________________________ Tommi Maekitalo Dr. Eckhardt + Partner GmbH