Sorry :-( I there is a typo in the:

select count(*) from abc where J > 500; ....
FIREBIRD = 30.26 sec

I haven't got the actual results at home, will check on Monday but am sure
it was 0.3 sec not 30. I also remember there not being any differences in
the times on indexed or non-indexed columns. It was purely to do with the
number of rows counted.

Also the "Select ID from abc;" was meant to be " Select a from abc;".

All the times where the averages over 10 consecutive runs of the statement,
with all the data being collected from the ResultSet in the first case and
added up, just to check that it actually got all the data.

Hypersonic is caching most of the table in memory. The hypersonic guys have
told me that in default configuration which I was testing it caches 50,000
rows. Hence its large memory footprint compared to firebird. Is there a way
of getting firebird to do more caching? My usage profile is mostly selects
and few updates other than initial batch insert. The total dataset is only
about 30-50Mb at the moment and mill probably not grow over 100Mb, so should
be feasible to have the whole thing in memory on must people machines these
days.

Also I am running in a single threaded, single user environment(A desktop
application) so is there is any options that I can turn off with firebird to
speed things up?

Thanks

Jasper Potts

________________________________________
From: Paul Beach [mailto:pbeach@...]
Sent: 06 August 2004 17:24
To: Firebird-Java@yahoogroups.com
Subject: {SPAM 03.7} RE: [Firebird-Java] Bechmark Results

> CREATE CACHED TABLE abc
>   a           INTEGER IDENTITY PRIMARY KEY ,
>   b           VARCHAR(1024),
>   c           VARCHAR(1024),
>   d           VARCHAR(1024),
>   e           SMALLINT,
>   f           DATETIME,
>   g           DATETIME,
>   h           DATETIME,
>   i           VARCHAR(255),
>   j           INTEGER,
>   k           INTEGER,
>   l           INTEGER,
>   m           CHAR(1));
> CREATE INDEX jindex ON abc (j);

Whats a cached table? One that resides in memory?

> Inserting 75000 rows, Single transaction, ~50Mb data.
> HYPERSONIC = 13 sec
> FIREBIRD = 20.3 sec
> MYSQL = 20.9 sec

makes sense.

> Select ID from abc; returning all 75000 integer IDs
> HYPERSONIC = 1.66 sec
> FIREBIRD = 3.46 sec
> MYSQL = 0.2 sec

How many times did you repeat this, Firebird data would get
cached on the first select and should run faster using the
same query on the second select. Single timings of selects
are meaningless, especially if casched means load the table
into memory.

> Select count(*) from abc where J > 500; returning count of 68000
> (index on column J)
> HYPERSONIC = 1.41 sec
> FIREBIRD = 30.26 sec
> MYSQL = 0.06 sec
>
> Select count(*) from abc where K > 500; returning count of 55000 (no
> index on column K)
> HYPERSONIC = 1.54 sec
> FIREBIRD = 0.25 sec
> MYSQL = 0.06 sec

> Seem like firebird is quite quick at count(*) but slow at selects
> returning large numbers of rows. There dosn't seem to be a way of
> doing "LIMIT" to select only a range of the results either in firebird
> which both the others can do. Any tips?

Confused, Firebird should be awful at a count, since it cannot use an
index, and must visit each row in the table to work out what version it
is. Don't know how you got the above but experience tells me that
Firebird should have given you exactly the same result, index or not.

And you should repeat the query at least twice. And then compare
the results.

Paul



Yahoo! Groups Sponsor
ADVERTISEMENT




________________________________________
Yahoo! Groups Links
• To visit your group on the web, go to:
http://groups.yahoo.com/group/Firebird-Java/
 
• To unsubscribe from this group, send an email to:
Firebird-Java-unsubscribe@yahoogroups.com
 
• Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.