Re: DB performance : 45 seconds for a query ?

What do you get if you ( cross an elephant with a giraffe ?? ) run UPDATE STATISTICS on the tables ?? Do you have any referential integrity or constraints on any of the tables ?? Sean Michel Dalle wrote in message <75qhnh$e67$1@news.mch.sbs.de>... >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.