Re: Performance on table with > 10000 inserts a day

ssoman@omm.com wrote: > > 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. Have you tried the updates under SET EXPLAIN ON? Is it using the index you think that it is using? Since dropping the index and rebuilding solves the problem I propose that it is not, but as the index grows over time it gains levels and soon the optimizer decides that the number of levels will cause selecting another index to perform better even though it does not because of the updates. Recreating the index builds a more efficient version of the index with fewer levels. Select * from sysindexes and look at nlevels and nleaves for the indexes on that table. Is the nlevels relatively good, compared to other indexes on the table, after the rebuild and is it worse when the updates slow down? If so stop running the UPDATE STATISTICS LOW and add DISTRIBUTIONS ONLY to the MEDIUM and LOW runs after rebuilding the index. It is the LOW (and the LOW part of HIGH) that is updating the index level stats in sysindexes and messing you up. You can quickly test this if the problem is happening now execute the following: UPDATE sysindexes SET nlevels = 1 WHERE idxname = "my_troublesome_idx"; This is guaranteed to be harmless and was suggested by a senior Informix engineer to solve a similar problem at our site. It seems that nlevels is ONLY used by the optimizer to estimate the number of I/Os indexed access will cost using one index versus another. This should fix the problem without having to rebuild the index until the next UPDATE STATISTICS LOW is run. BTW those UPDATE STATISTICS LOW are not needed every day, only when the number of rows changes significantly, at 0.04%->0.1% growth per day I'd say that happens about once a year or two. Also the MEDIUM and HIGHs are needed if the data distribution is not valid, ie the ratio of one key value to another no longer holds. Does this happen weekly? I doubt it. I suspect that your data is inserted rather symetrically so that even these STATS updates could be reduced to monthly or quarterly unless say a whole new key was added. If there is one column, like a serial number column, that really does have stats go out of sync constantly then consider updating HIGH or MEDIUM for that column ONLY with DISTRIBUTIONS ONLY on a daily basis. Art S. Kagel