Re: Performance on table with > 10000 inserts a day

ssoman@omm.com wrote: > Hi all, A question : How do you manage a 14 million row table which grows at > the rate of 8000 - 14000 rows a day? This table has 7 indexes and is > obviously heavily used. Most online transactions are pretty effective since > the bulk of them are inserts. Retrieving a few rows is also effective, since > we have so many indexes to cater to them. The problem lies in the batch or > daily processing which takes hours. Some aggregate queries take 5 - 6 hours. > If I drop and re-create the indexes and re-run the query, it only takes 15 > minutes. Sounds like you forgot to do regular "update statistics". The optimizer relies heavily on up-to-date statistics, so you should run the "update statistics" command at regular intervals. Check the IIUG website for Art Kagel's "utils2_ak" package which contains "dostats.ec", a nifty program that will run the optimal combination of "update statistics" commands for your database or table. HTH, Richard -- +--------------------------+------------------------------------------+ | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 <-- NEW! | | Klinikum Grosshadern | FAX : +49-89-7095-8886 | | 81366 Munich, Germany | GSM : +49-172-8933578 | +--------------------------+------------------------------------------+