On 3 Apr 2003 at 12:20, Mark Patterson wrote:

> back in feb I was asking about why a number of queries run in sequence seemed to
> run slower than the sum of the individual queries. I was asking for a colleague,
> who got back to me recently saying that he got a considerable speed up by
> dropping and recreating the indexes.
>
> Is this a common experience?

It's normal :-) You don't need to drop and recreate, just use ALTER INDEX
INACTIVE and then ALTER INDEX ACTIVE for the same effect.

Why the performance is better after that:

1) Index statistic is recomputed. It's computed only on index creation
(not very usable for empty database when you create the schema :-),
recreation by ALTER INDEX ACTIVE and by SET STATISTICS command. Index
statistic is important for optimizer and updated value could result in
different (not always better, but mostly far better) query execution
plan.

2) The whole index B-tree is recreated as balanced. A lot of inserts,
updates and deletes render the index B-tree structure less effective.

Golden rule to keep your index healthy: You should recreate the index B-
tree every time when about 30% of index keys was changed.

Best regards
Pavel Cisar

See you at the First European Firebird Conference in May in Fulda,
Germany
http://www.firebird-conference.com

http://www.ibphoenix.com
For all your upto date Firebird and
InterBase information