Re: DB performance : 45 seconds for a query ?

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. > >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. Did you try to handle out what is going on using "SET EXPLAIN ON" ? If the query path is wrong and don't includes the "amount" index you could try update statistics on these tables. About design issues - I would not divide these data among 12 tables. Instead I would use just one table (additionally you could fragment this table by date). Hope it helps, Octav -- Octav Chiriac Phone: (373) 2 21 20 96 NetInfo S.R.L. Fax: (373) 2 21 36 59 Chisinau (373) 2 24 00 83 Moldova, Republic of mailto:com@netinfo-moldova.com