RE: Performance issues on tables with >10000 inserts a day

We have tables which have about 500,000 inserts a day, on average (v7.24). These tables are queried heavily as well (by the inserting application as well as by independent query apps). To ensure the best response time, we do precisely the following 1. During table creation [tables get created weekly], ensure accurate extent sizing. 2. Spread the tables [6 of them] across disks 3. Daily Update stats - medium, distributions only. That's OLTP. The information pumped into these tables is taken nightly, minus a little filtering, into DSS (v7.24). The table there keeps the last 2 years worth of information - so, it also needs to be purged as well (done weekly, I believe). It currently has about 200m rows in it with 3 indexes. Update Stats (distributions only) is run weekly. We have no obvious query problems in either of the databases. Have you tried using 'SET EXPLAIN ON' to investigate the query path differences when the query runs quick as against when it runs slow? What about fiddling with OPTCOMPIND? [Our OLTP using 0, but DSS does better with 2]. Maybe all you are missing is what Laurent suggested - Update Stats. Rudy -----Original Message----- From: "Laurent COLLIGNON" [SMTP:laurentc@worldnet.fr] Sent: December 6, 1998 10:31 AM To: "informix-list@iiug.org" [SMTP:informix-list@iiug.org] Subject: Re: Performance issues on tables with >10000 inserts a day Excuse my stupid answer but ... why not trying a UPDATE STATISTICS HIGH on this table ... ssoman@omm.com wrote: > Hi all, I have some high use tables which get 10,000 or over inserts per > day. Also, most of these tables have 5 or more indexes. My problem is dealing > with performance on these tables. Online queries returning single or a few > rows are not a problem, but the batch processess take a few hours, with > single aggregate queries taking over 5 hours. If I drop and re-create all > indexes, then the same query takes 10 minutes. The table was already > re-organized into its own dbspace and is down to 4 chunks ( with index ), so > the data is as organized as can be ( for now ). So, my question is, how does > one go about managing a monster like this? Is it worth the time at all, since > the index re-creation itself could take hours. Can the index ever be > effective with so many inserts ?? > > Sujata > > -----------== Posted via Deja News, The Discussion Network ==---------- > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own