Javier wrote:

>
> Ok, but how do I restore the statistics? Using "update" statements?

You do not "restore the statistics". The statistic that gets updated
by a SET STATISTICS call is the selectivity of the index. Low value =
high selectivity = Good; high value = low selectivity = bad. The
value immediately after the call reflects the state of the index at
that point. So, at that point, the optimizer has the best chance to
prepare the most efficient plan by deciding which indexes will be the
most helpful.

After that, the actual condition of an index selected for the plan may
degrade gradually as rows are inserted and deleted. When you start to
notice a decline in the performance of queries over a table with a
high level of inserts and deletes, it is probably time to run SET
STATISTICS again. Selectivity is not static - it changes
infinitessimally with each insertion and deletion, or dramatically
with a major bulk insert or delete. Eventually, the statistic may
tend to become too outdated to obtain the same level of performance as
when the numbers were fresh.

The optimizer always calculates a plan based on the most recently
calculated statistics. What you can do is run the queries in isql with
SET PLANONLY while the statistics are fresh. That will tell you the
optimizer's choices based on the current selectivity and record count.

You can save that plan in a text file. If you decide from your test
results that you like it, you can copy it into your application
queries using the optional PLAN clause. If you supply a plan in your
select query, the optimizer will not try to create one. That might or
might not work positively over time, of course.

On the whole, the optimizer is going to produce the most effective
plan, since it is aware of the current size if the table and may be
able to make dynamic adjustments that your static plan cannot take
into account.

Hth.
Helen