Re: Periodic Data in SQL

Helmut.Neumann wrote: > > Hello, Try to break up your tables on natural bopndaries such as days or months i.e. mytab_971201, mytab_971202, ... this will keep you from creating a large table (I think large tables do not efficiently use indexes) and ensure a) inserts are fast b) purging is a simple fast drop operation. The only proble is your queries are now complicated by multiple tables. - Jon > I have a problem designing a database table with indexes, which should > contain a short time history of periodic data (short time means 2-4 weeks). > The enity table is designed as follows: > > create table my_object (> id integer not null, -- ID of object > descr varchar(255), -- Description of object > -- ... > primary key (id) > ); > > This table is populated with 3000 objects. > The corresponding history table looks like: > > create table my_history (> time integer, -- unix time stamp > id integer, -- ID of the object, referencing my_object(id) > attr smallint -- Some kind of data > __ ... > ); > > In this history table the combination of time and id is unique. The history > table is filled by an automatic process with measured or calculated data > with > 3000 data sets per minute (for each object there is one dataset per minute). > Once a day another process is running to delete "old" datasets from the > history table to prevent the database from growing too much. This process > executes a SQL statement like: > > delete from my_history where time < XY;> where XY is a unix time-stamp. > > Some application programs query the history table like > > select * from my_history where time = XY;> > or > > select * from my_history where id = Z and time > XY > order by id,time desc;> > For this queries the following indexes would increase performance: > > create index my_history_1 on my_history (time, id); > create index my_history_2 on my_history (id,time desc);> > One problem is: > If I create just the history table without any index (or constraint) the > insertion of data to the table is very fast (about 200000 data sets per > minute > on my system). If I create the index my_history_1 the insertion is also fast > (about 150000 data sets per minute). If I create the second index > "my_history_2" instead or in addition to "my_history_2" the insertion is > fast > at the beginning (when the table is small) and slows down dramatically to > values like 500 data sets per minute, when the table becomes bigger (200000 > data sets). I think the problem is, that the data sets, which are inserted, > are > in the order of index "my_history_1", but not in order of index > "my_history_2". > So there is a lot of rebalancing to be done for index "my_history_2". > > Another problem is, that even if I only create the first index > "my_history_1" > the insertion slows down dramatically, when the delete process starts > working. > It slows down in a way, that insertion of 3000 new data sets and deletetion > of > 3000 old data sets needs more than one minute of real time, also due to the > rebalancing of the index, I suppose. > > Has anybody an idea to solve these problems in Informix-SQL (maybe by using > table fragmentation, or by splitting the table my_history into more than one > table, or ...) > > Thanks in advance, > > Please reply ONLY to this newsgroup AND to: > werner.scholtes@heuboe.de