Re: Performance on table with > 10000 inserts a day

Richard, Actually, update statistics are run regularly ( low every day ) and ( medium,high,low on the weekends since they take so long ). The point is that this behaviour is seen even immediately after I run update statistics. One of my options is to re-create the indexes with a lower fill factor - Will that slow down my selects, and also the index would have to dropped and re- created after the pages fill up again. My second option is to isolate the indexes to a seperate dbspace as suggested by others . But my question is, is it worth it, since the table sees so many inserts to begin with. Thanks Sujata In article <36650862.715F3B5E@ana.med.uni-muenchen.de>, richard.spitz@ana.med.uni-muenchen.de wrote: > 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 | > +--------------------------+------------------------------------------+ > -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own