Re: DB performance : 45 seconds for a query ?

Michel You may want to check out the output of EXPLAIN for the query to see if your indexes are being used properly. To run the EXPLAIN, type "SET EXPLAIN ON;" in the line preceding your query. The output of the explain will be contained in a file sqexplain.out in your current directory. HTH Sujit ______________________________ Reply Separator _________________________________ Subject: DB performance : 45 seconds for a query ? Author: michel.dalle@usa.net (Michel Dalle) at internet Date: 12/23/1998 12:49 PM 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.