--- In firebird-support@yahoogroups.com, grostoon <grostoon@y...> wrote:

>
> Fine Pavel, so my test was not that foolish. It at least shows me

that FB2 has some good improvements over FB1.5 no ?

>
> Thank you,
>
> Toon.
>

Hello Toon,

I have been following this thread with some interest, and I don't
think the foolish call was particularily warranted.

If your typical database table contains 150,0000 records 'aaaa', and
150,000 'bbbb', and a typical operation was to copy those 300,000
records comprising of two unique values, then your test was not
foolish at all, but would rather show Firebird to be inadequate to
cope with your need.

If that was really the case, then your database could be normalised,
but I doubt this is really what you are trying to test.

Your tables contain names and ages. There will be duplicates, but I am
sure that your system contains more than two names and two ages. What
you have stumbled upon is a bug in the Firebird garbage collection.
When there is an index with massive amounts of duplicates (as in this
test case), Firebird 1.5 does a lot more work than it should. You have
already seen that Firebird 2 does already handle this better, and it
is still in alpha.

What I worries me is that you may expect that this is typical of the
behaviour of Firebird. I want to assure you, that given more usual
data, the performance improvements are astronomical.

Here is a test you can run. Change your script slightly

-----

create table tbl1 (name char(10), age integer, dummy integer);
create table tbl2 (name char(10), age integer, dummy integer);

create generator gen_dummy;

set term ^;

create trigger tbl1_bi for tbl1
active before insert position 1
as
begin
new.dummy = gen_id(gen_dummy,1);
end
^

create trigger tbl2_bi for tbl2
active before insert position 1
as
begin
new.dummy = gen_id(gen_dummy,1);
end
^

set term ;
^

create index idx1 on tbl1 (name, dummy);
create index idx2 on tbl2 (name, dummy);
create index idx3 on tbl1 (age, dummy);
create index idx4 on tbl2 (age, dummy);

insert into tbl1 values ('aaaa', 1);
insert into tbl1 values ('bbbb', -20);
insert into tbl2 select * from tbl1;
insert into tbl1 select * from tbl2;
commit;
insert into tbl2 select * from tbl1;
insert into tbl1 select * from tbl2;
commit;
... sequence repeated until there are about 300000 rows in tbl1 and tbl2
insert into tbl2 select * from tbl1;
insert into tbl1 select * from tbl2;
commit;

then your selects and deletes

-----

Now apart from the select * doing a table scan (something that is
unavoidable in a MGA database), what is the performance like?

Adam