Hi,

I'm doing some test with different database systems. In my tests, Firebird
seems to be slow in getting the ResultSet. That takes times longer than with
the compared database systems. The measured times [ms] for the query's are
listed below (Firebird and one concurrent database).
I'm using JDBC (JayBird 1.01), Firebird 1.5 RC6 (WindowsXP and Linux) and
J2SE. Statement and ResultSet are used with default settings. I did the test
in different ways (using a fast and slow pc and mixed network access) and it
seems that the performance for statement.getResultSet() depends on the
server not the client. So performance for statement.getResultSet() seems to
be a problem of Firebird and not JayBird.

Is there a posibility to get better performance for getResultSet()? Is this
dependend of the character set the database is set up with? This test was
running with charset NONE.



I also did the test with 1.5 RC7, seems to be the same.
The times are the same if running local (without using network).So
networking (100 MBit) is not the the problem in this case.

Test description:

case 1 and case 3: execute query and call statement.getResultSet(), no
next() used.
case 2 and case 4: execute query, call statement.getResultSet() and iterate
the results using next() like shown in the following code fragment:
int counter = 0;
starttime = System.currentTimeMillis();
ResultSet resSet = statement.getResultSet();
while (resSet.next() == true) {counter++;}
endtime = System.currentTimeMillis();

case 1 and case 2 Firebird 1.5 RC6
case 3 and case 4 reference Database

The description for the query shows the query executed.
statement.getResult().next() for XXX Results shows how many results
were iterated using resSet.next() (only case 2 and case4). For case 1 and
case 3 that would be statement.getResult()

Some values show a jitter, this may be caused by J2SE.

--- FIREBIRD ---- ---- other DB ------
case 1 case 2 case 3 case 4 description for query
36 36 31 31 select * from ITEM where
INVOICEID = 90
96 103 1 64
statement.getResult().next() for 100 Results
29 29 18 19 select COUNT(PRICE) from
PRODUCT where PRICE > 100
12 13 0 1
statement.getResult().next() for 1 Results
31 31 57 58 select * from CUSTOMER
where ID = 90
106 107 0 1
statement.getResult().next() for 1 Results
28 28 49 50 select SUM(PRICE) from
PRODUCT
14 14 0 0
statement.getResult().next() for 1 Results
30 30 3636 3702 select COUNT(*) from ITEM
where ITEM.COST between 10 and 500
955 892 0 0
statement.getResult().next() for 1 Results
21 20 14 14 select FIRSTNAME from
CUSTOMER where FIRSTNAME like '%on%'
81 85 0 73
statement.getResult().next() for 100 Results
26 26 14 16 select * from PRODUCT
where ID = 90
6 6 0 1
statement.getResult().next() for 1 Results
48 48 152 150 select
LASTNAME,FIRSTNAME,STREET,CITY,NAME as ... no order by
1150 13994 0 48770 statement.getResult().next() for
10000 Results
50 51 30382 40549 select
LASTNAME,FIRSTNAME,STREET,CITY,NAME as .order by
1019 13456 0 5731 statement.getResult().next() for
10000 Results
50 50 55562 23849 select
LASTNAME,FIRSTNAME,STREET,CITY,NAME as .another order by
1028 14158 0 5837 statement.getResult().next() for
10000 Results
50 50 25989 23588 select
LASTNAME,FIRSTNAME,STREET,CITY,NAME as .still another order by
1338 13397 0 5852 statement.getResult().next() for
10000 Results
7530 7531 24021 23936 select
LASTNAME,FIRSTNAME,STREET,CITY,NAME as .once more another order by
721 9435 0 5789 statement.getResult().next()
for 10000 Results
7634 7617 24197 24226 select
LASTNAME,FIRSTNAME,STREET,CITY,NAME as .finally another order by
729 9480 0 5882 statement.getResult().next()
for 10000 Results
28 28 3521 3467 select SUM(COST) from ITEM
711 717 0 1
statement.getResult().next() for 1 Results
21 21 15 14 select NAME from PRODUCT
order by NAME
73 75 1 40
statement.getResult().next() for 100 Results
20 21 10 8 select NAME from PRODUCT
order by NAME
73 74 0 136
statement.getResult().next() for 100 Results
30 30 57 55 select * from CUSTOMER
order by LASTNAME
104 103 0 37
statement.getResult().next() for 100 Results
31 30 56 55 select * from CUSTOMER
order by FIRSTNAME
107 107 1 37
statement.getResult().next() for 100 Results
22 23 18 19 select COST from ITEM
where INVOICEID = 1 and PRODUCTID = 1
3 4 0 0
statement.getResult().next()
23 23 12 12 select TOTAL from INVOICE
where ID = 1
3 4 0 0
statement.getResult().next()
21 22 12 12 Updatecount: 1 Query:
update INVOICE set TOTAL = 15441 where INVOICE.ID = 1
20 20 18 19 Updatecount: 1 Query:
delete from ITEM where INVOICEID = 1 and PRODUCTID = 1
5 4 3 3 commit
35 34 18 17 select * from ITEM where
INVOICEID = 2
73 74 1 33
statement.getResult().next() for 100 Results
31 30 17 16 select * from CUSTOMER
where ID = 2
7 6 0 0
statement.getResult().next() for 1 Results
26 26 14 14 select * from PRODUCT
where ID = 2
6 6 0 0
statement.getResult().next() for 1 Results


Best regards
Tom




--
GMX Weihnachts-Special: Seychellen-Traumreise zu gewinnen!

Rentier entlaufen. Finden Sie Rudolph! Als Belohnung winken
tolle Preise. http://www.gmx.net/de/cgi/specialmail/

+++ GMX - die erste Adresse f�r Mail, Message, More! +++