SE Performance during DELETE

> Hi all, > > Some advice please. I'm running SE 7.13 on DRS/NX. Unix box is a > standard pentium with 128Mb RAM (I know - I know!). I have a large table > which contains ~ 3 million rows. Each month, 1 million are added, and the > oldest 1 million deleted. This is the largest table in a 15 table > database. Table has primary key on 1 serial column, 1 non-unique index on > 5 columns, and 7 3-column referential constraints referencing 6 other > tables. > > The delete is managed by a stored procedure. The s/p is written to delete > rows from the table in chunks of 10,000 based on the serial value. Each > 10,000 record chunk is bound within it's own transaction to avoid a long > transaction. > > Problem is, we haven't actually seen the s/p complete yet - the longest > run was 4 days until we got fed up. (My interim solution is to unload > what I want to keep, drop & recreate the database and dbload it up again. > This only takes about 6 hours.) > > Is this the sort of performance I can expect from SE? I know there aren't > many "tuning" options, and from examining the log, I think this is caused > by the large number of referential constraints present. Is there anything > I can do short of dropping the referential constraints ? e.g. tuning Unix, > maintaining better statistics? (at present I only do an update stats low > on all tables - anything else takes an age). > > I would like to hear of any experience of terrible delete performance. > Any hints or tips would be much appreciated. > > Greg Archibald.

Update statistics better than just 'low'. Art Kagel's dostats programwould help, even if you are not at 7.24 yet. You can update stats on the first field of indexes by tables(column), on the smaller tables that should not take too long. -- --------------------------------------------------------- Steven Hauser email: hause011@tc.umn.edu URL: http://www.tc.umn.edu/~hause011 ---------------------------------------------------------

Dostats.ec depends on the sysmaster database so I do not think it will work for and SE engine. With some painful work you could cobble it up. Better to get one of the excellent stats scripts that are also on the Repository. They are not quite as thorough as dostats or as flexible but they usually get the job done. Art S. Kagel Steven Hauser wrote: > > Update statistics better than just 'low'. Art Kagel's dostats program> would help, even if you are not at 7.24 yet. You can update stats on > the first field of indexes by tables(column), on the smaller tables that > should not take too long. > > -- > --------------------------------------------------------- > Steven Hauser > email: hause011@tc.umn.edu URL: http://www.tc.umn.edu/~hause011 > ---------------------------------------------------------