Do I Need to Call UPDATE STATISTICS?

To optimize execution and increase performance, the c-treeSQL Server engine requires information about the data without actually reading the data. In particular, the optimizer needs to know such things as how many rows are in a table and how many rows will be retrieved given a condition, among other things.

When indices are involved, the c-treeSQL Server is usually able to determine the requested information directly from the index in a very efficient manner. When indices are not involved then the c-treeSQL optimizer requires an alternative method to gain this information. Normally, the c-treeSQL Server uses either statistics collected from a previous statistics update, or heuristic methods to `guesstimate' this information.

It is possible, in some circumstances, for this information to become out of date or the heuristic is not precise enough. For instance, a large batch of updates to non-indexed data followed by an immediate query might cause this to occur. The c-treeSQL Server then relies upon the out of date statistics and can appear to execute sluggishly. Under these circumstances it can help to update the collection of statistics. This is done using the c-treeSQL UPDATE STATISTICS statement.

Please note, this operation commands a heavy load of activity on the system. FairCom suggests calling UPDATE STATISTICS only on the specific tables involved in complex queries when the query performance seems to degrade over time. The use of appropriate indexes can usually alleviate this periodic need.