Re: Update Statistics Low, Medium & High
>In our system, we discover sometimes some tables in index searching is much >faster while we update their statistics low or medium instead of high. > >We have an idea that update all tables high statistics with reference to the >server admin. guide. I heard a discussion the other day which indicated that when you update statistics "high" it causes the optimizer to evaluate every possible query path. This evaluation might take longer than the actual query--it depends on the indexes and the query. On the "medium" or "low" settings the optimizer evaluates only the most likely paths, and chooses one quickly. Though I could not find a definitive explanation of this exact point in the documentation, a picture might look like: |---------------------------------|------------| evaluation time query time UPDATE STATISTICS HIGH vs |----------------|----------------| evaluation time query time UPDATE STATISTICS MEDIUM This illustrates how the total query time could be less with a less thorough evaluation. With the advent of the OnLine Dynamic Server the "UPDATE STATISTICS" command comes with a host of possibly confusing options. Though it is still something of a black art to coax the maximum performance from an application, you can find extensive discussions of the "UPDATE STATISTICS" dilemma in (at least) the following places: Release Notes, v 7.2 (2 full pages) Informix-OnLine Dynamic Server Performance Guide version 7.2, December 1995 Informix-OnLine Performance Tuning Informix Press, ISBN 0-13-124322-5, 1995 These are, of course, in addition to the usual "SQL Syntax/Reference/Tutorial" and "Admin Guide" references. You can get some valuable in-person training from the "Informix-OnLine Dynamic Server Performance Tuning" class. (See the web page at www.informix.com for scheduling details.) Ad nauseum, ______________________________________________________ Clem Akins (aka clema@informix.com) Informix Software, Inc (Standard disclaimers apply) International Technical Support Last seen: back in Menlo Park, California