Wednesday, January 10, 2007

Statistics autoupdate

Several days ago I saw poll about auto-updating index statistics feature for Firebird at firebirdnews.org. Here is the results link.

Well, 28% of voters think that this will be good. Interesting, that I can't say percent of systems where this feature will make performance worse. And can't say also, what percent of those 28% this will affect.

Right now this is not a hard question to make auto-update of index statistics by yourself. You can use gidx tool from
gtools, AT or cron + isql running your handmade script, and so on.
But, the main question is when and how often this auto-update feature must start by itself.

IBAnalyst 2.0 have some thoughts about it in its help file, in Additional Q&A section (10-th Q/A). Here is the summary:
Depending on your applications activity record count in some tables may vary more than 50%. So, statistics update must be made at the moment when data in such tables is very close to mostly useful value. Example: if you reload some table with data several times per day, you need to refresh index statistics for that table only when table is populated with data, not empty. Because, I'm sure, you want performance when you run queries on non-empty table.
But, only you know when your application deletes data from table and fills it with new data. And there will be the best moment to apply SET STATISTICS command.

p.s. Note that index statistics is being built by index. And index keys does not contain transaction numbers, so SET STATISTICS can't understand are those keys belong to deleted data or not.
p.p.s. running SET STATISTICS on all indices in 2gb database takes about 35-45 seconds on AMD 64 3500 and SATA HDD.

No comments: