Ann W. Harrison wrote:

> Do you have an index with lots of duplicates - that produces
> the symptoms you're seeing - very slow CPU bound garbage
> collection. Disabling that index, or making it a compound
> index with a second portion that is more selective (i.e.
> sex, student_number rather than just sex) should correct
> the problem. As does Firebird 2.
>

aah .. i think we might have a winner here ...
(and just for my defense, i didn't design that database :-)


ACCOUNTRECORD (141)

Index FK_ACCOUNTR_ACCI_ACCR_ACCOUNTI (3)
Depth: 3, leaf buckets: 3433, nodes: 2308586
Average data length: 0.00, total dup: 2279388, max dup: 3912
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 3432

Index FK_ACCOUNTR_ACCR_ACCR_ACCOUNTR (4)
Depth: 3, leaf buckets: 3430, nodes: 2308586
Average data length: 0.00, total dup: 2308437, max dup: 1866983
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 3429

Index RDB$PRIMARY62 (0)
Depth: 3, leaf buckets: 4093, nodes: 2308586
Average data length: 1.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 4092


well, the second index obviously is the problem here; removing that
constraint from the database provides a significant speedup ...

however, just dropping that constraint (and losing referential integrity)
is probably not an option ... but i'll talk to our developers and see if
they can redesign something in the db in question ...


thanks,
alex.