At 04:30 AM 2/1/2001 +0000, ac.hi@... wrote:

>In a database with containing only one table (details see below) I
>insert first around 20000 records and afterwards I delete half of
>them. After deleting this records I have performance problems
>accessing the table. It is best shown when I do a GBAK:
>
>GBAK-Time after inserting before deleting: 6min
>GBAK-Time after deleting: 25min

You're seeing the effect of garbage collection. Try gbak with the
-g switch (to inhibit garbage collection). It will be the same
speed in both cases.

>During GBAK of the deleted version I see Interbase using a lot of CPU
>time but not accessing hard disk very often. This is different to the
>non deleted version which is doing mainly I/O stuff. The additional
>time for the deleted version is used when GBAK writes the table with
>the deleted records.

The low level of I/O suggests that the garbage collect problem is
compounded by an index with lots of duplicates. In that case,
deactivating and reactivating the indexes after you've committed
the delete will make everything much faster. You probably need
only delete the index test_idx, which has a total of four values
in 20,000 instances.

<minor rant>
Why do people test databases by storing and deleting large
amounts of artificial data? It seems so unlikely in any
real situation
</minor rant>

In a real application, if you need an index on the code field
(values 0:3) define a compound index of code, test_id. The
extra term makes the index unique, greatly improving garbage
collect performance.


Regards,

Ann
www.ibphoenix.com
We have answers.