Subject Re: Basic tests shows very poor performance
Author Svein Erling Tysvær
Hi Toon!

I'd still like to see a similar test where the data weren't simply
duplicated. Indexed duplicates have always been an easy way to make
Firebird crawl. And that was why I compared it to a space shuttle (use
it for something it was not designed to handle very well).

At least add a primary key (simply use a generator and before insert
trigger) to the table and hook this onto the end of every other index.
I'm not saying Firebird will shine, but 2430 seconds only shows the
"well known secret" that Firebird is lousy at handling massive number
of duplicates in indexes.


--- In, grostoon wrote:
> Hi all,
> Ok, I did the test with postgresql 7.3, mysql 2.23, FB 1.53 and
> FB2.0.
> I remember you that I have 2 cloned tables tbl1 and tbl2 (name
> char(10), age integer) and 4 indexes : idx1 on tbl1 (name), idx2 on
> tbl1 (age), idx3 on tbl2 (name) and idx4 on tbl2(age).
> At the begining, I insert ('aaaa', 1) in tbl1 and ('bbbb', -20) in
> tbl2 then in do insert into tbl2 select * from tbl1 and insert into
> tbl1 select * from tbl2 until I have 317811 rows in tbl1. Then I
> delete all rows in tbl1 and I do a select count(*) from tbl1.
> ----------------------
> all inserts : 16.7 s
> select count(*) tbl1 : 0.5 s
> select count(*) tbl1 : 0.6 s
> select count(*) tbl1 : 0.6 s
> delete from tbl1 : 1.68 s
> select count(*) tbl1 : 2430 s !!!
> select count(*) tbl1 : 0.0 s
> select count(*) tbl1 : 0.0 s
> My conclusion is that there was really something wrong in FB1.5 that
> has been changed in FB 2.0 and then, FB performances are very closed
> or even better (insert, delete) than those of postgres and mysql (of
> course just for this basic scenario).
> So there was no reason to laugh and compare what I said with
> "testing how fast a space shuttle takes to move the first 100
> meters". Seriously, spending 45 minutes to discover that a table is
> actually empty for a database of less than 50Mo, it's all but a
> normal behaviour even for a scenario that, I admit it, is not
> representative of real database access...