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 [SNIP] Unless this is OL 5.xx or SE why are you not using fragmentation instead of these 12 independent tables? I'll tell you exactly what is happening. Several things: 1) You probably do not have STATISTICS updated to high or medium. 2) You do not say whether you have any indexes on the search columns. If not that's a major problem. 3) A UNION (you did not say UNION ALL) will have to sort the output data to eliminate duplicate rows this sort has to wait for all data to be fetched from all 12 queries which must run in series. Therefore I make the following recommendations: 1) Update STATISTICS using the rules in the 7.2 release notes (you can use my dostats.ec utility which implements those rules). 2) Add any indexes you may need. You may need several versions of each index with different lead columns because range queries will reduce the effectiveness of keys that follow any that are filtered by a range. 3) Add a month_num column and combine the tables into a single fragmented table fragmented by expression on the month number. The data will essentially stay in the same sub-tables as now but the engine and optimizer can parallelize querying all 12. The SQL for this would look something like (assuming all tables are currently in separate dbspaces): ALTER TABLE month_1 ADD (month_num SMALLINT DEFAULT 1);::: ALTER TABLE month_12 ADD (month_num SMALLINT DEFAULT 12); RENAME TABLE month_1 TO month_data; ALTER FRAGMENT ON TABLE month_data INIT FRAGMENT BY EXPRESSION month_num = 1 IN dbspace1; ALTER FRAGMENT ON TABLE month_data ATTACH month_2 AS month_num = 2 IN dbspace2;::: ALTER FRAGMENT ON TABLE month_data ATTACH month_12 AS month_num = 12 IN dbspace12; Not Great Alternative: A programmatic solution is to open 12 cursors, one on each table, and merge the data in your program (assuming 4GL or ESQL/C). Several efficient algorithms for merging sorted sinks are available just include and ORDER BY clause in each of the 12 queries. Art S. Kagel