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

Hi all, Thanks to all who replied to my question. Following is a brief summary and a resolution that I have reached based on all your comments. 1. I have a table with > 10000 inserts a day ( 7 indexes ). Online query performance is pretty good, but DSS is not as good. The problem here is that this is a third party software, and as such I do not have the option of moving the summary into a DSS table. 2. Update Stats are run regularly, but maybe not efficiently 3. When I drop and re-create the indexes, the query runs 20 times faster. I re-investigated the query path for the query that runs quick vs when it runs slow, and here are the results. a. The quick query uses a sequential scan and the slow query uses an index. b. There is no really good index for that particular query ( which is a correlated sub-query ), and hence the index chosen does no good. c. As was suggested by a couple of you the update statistics low messes up the index level stats in sysindexes and hence the wrong path is being chosen. ( ref. a previous message in this thread by Art on an excellent explanation ) At this point, I have two alternatives ( besides telling the third party to fix the problem ! ) 1. Drop and re-create the indexes, and run update stats medium and low distributions only ( atleast for that index ) and run the "low" once every so often 2. Change the query to a join, and create an index for that particular clause 3. Upgrade to 7.3 and force a sequential scan! Thanks to all of you for your responce, and hope this summary and follow- up helps some others like me. In article <74gm5a$4qu$1@news.xmission.com>, "Fernandes, Rudolf" <RFernandes@purolator.com> wrote: > > 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 > -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own