September, 2002
FirstSQL Database Benchmark Tests for Disk and In-Memory Processing - For JVMs from Sun 1.4.1 and BEA JRockit 1.4.0
Test consist of simple inserts, complex inserts, simple queries, complex queries. autoCommit=false
Platform = 1,333 Mhz Pentium w/784 MB RAM - Windows 2000
ms=milliseconds TPS=Transactions per second
FirstSQL/J - Disk mode                      
Bench1 Sun 1.4.1 Sun 1.4.1 J Rockit J Rockit Bench2Insert Sun 1.4.1 Sun 1.4.1 J Rockit J Rockit Bench3 Sun 1.4.1 J Rockit
Inserts ms TPS ms TPS Complex Inserts ms TPS ms TPS Runs ms ms
10000 992 10080 536 18656 20000 46972 426 50470 396 1 3074 5380
10000 991 10090 544 18382       avg. 20 2326 2072
        Bench2Query      
    Simple 10000 1352 7396 1608 6218      
          Complex 20000 4607 2170 6085 1643      
FirstSQL/J - Memory mode
Bench1 Sun 1.4.1 Sun 1.4.1 J Rockit J Rockit Bench2Insert Sun 1.4.1 Sun 1.4.1 J Rockit J Rockit Bench3 Sun 1.4.1 J Rockit
Inserts ms TPS ms TPS Complex Inserts ms TPS ms TPS Runs ms ms
10000 872 11467 492 20325 20000 4156 4812 2854 7007 1 1082 3603
10000 852 11737 496 20161   4166 4800 2872 6963 avg. 20 500 628
        Bench2Query          
      Simple 10000 600 16666 486 20576      
          Complex 20000 2664 3753 3447 2901      
Leading Java RDBMS - Disk mode
Bench1 Sun 1.4.1 Sun 1.4.1 J Rockit J Rockit Bench2Insert Sun 1.4.1 Sun 1.4.1 J Rockit J Rockit Bench3 Sun 1.4.1 J Rockit
Inserts ms TPS ms TPS Complex Inserts ms TPS ms TPS Runs ms ms
10000 2624 3810 1707 5585 20000 26198 763 24372 820 1 1542 2348
10000 2664 3753 1814 5512   26037 768 20328 983 avg. 20 498 429
        Bench2Query          
      Simple 10000 2524 3961 2077 4814  
          Complex 20000 518906 19 364311 27      
Test Description and SQL

Bench1 insert 10,000 rows.
Insert into bench1(id) values(?);

Bench2 insert 20,000 rows..
Insert into userprofile (sppid, cli, status, id, bearercapbitmap) values (?, ?, ?, ?, ?);
Insert into usercosprofile(userprofileid, bearercap, costprofileid) values(?, ?, ?);

Bench2 Simple Query selects 10,000 rows.
Select userprofile.sppid from userprofile where cli = ? and userprofile.sppid = 1;

Bench2 Complex Query selects 20,000 rows.
Select up.sppid, up.cli, up.status, up.bearercapbitmap, ucp.bearercap, ucp.cosprofileid, cp.name,
cr.restrictionid, cr.overrideid, crs.timerangeid, crs.addressid, crs.calltypeid
from userprofile up, usercosprofile ucp, cosprofile cp, cosrule cr, cosrulespec crs
where up.cli= ? and up.sppid= 1 and up.id=ucp.userprofileid
and ucp.cosprofileid=cp.id and ucp.cosprofileid=cr.cosprofileid
and (cr.restrictionid=crs.id or cr.overrideid=crs.id);

Bench3Run is designed to mimic a database used for on-line order processing in ecommerce.
It runs a set of online operations: It measures the time it takes to accept and prepare
200 orders from customers via the web.
Add a customer
Add an order for a customer
Ship (partial) order for a customer
Change customer address info
Change quantity for a customer order line
Delete a customer
Delete an order
Query customer info
Query customer orders

Each run processes a typical mix of theses operations with emphasis on adding orders.
The resulting time in milliseconds is shown for a run or for an average if multiple runs are executed at once.

Bench3Run executes the following SQL commands randomly:

select id from surrogate_key where table_name='customers'
update surrogate_key set id = id + 1 where table_name='customers'
insert into customers values(?,?,?,?,?,?,current_timestamp)
select max(ord_no) from orders where cust_no = ?
select max(line_no) from ord_lines where cust_no = ? and ord_no = ?
insert into orders values(?,?,current_timestamp)
insert into ord_lines values(?,?,?,?,?)
select max(ship_no) from shipments where cust_no = ? and ord_no = ?
insert into shipments values(?,?,?,current_timestamp)
insert into ship_lines values(?,?,?,?,?,?)
select * from customers where cust_no = ?
delete from customers where cust_no = ?
delete from orders where cust_no = ? and ord_no = ?
update customers set address = ? where cust_no = ?
update ord_lines set qty = qty + 1
where cust_no = ? and ord_no = ? and line_no = ?
select cast(cust_no as smallint) as cust_no,
cast(name as char(32)) as name,
cast((select count(*) from orders
where customers.cust_no = orders.cust_no) as smallint)
as orders
from customers where cust_no=?
select cast(cust_no as smallint) as cust_no,
cast(ord_no as smallint) as ord_no,
cast(line_no as smallint) as line_no,
cast(item as varchar(24)) as item,
cast(qty as smallint) as qty,
cast((select sum(qty) from ship_lines
where ord_lines.cust_no = ship_lines.cust_no
and ord_lines.ord_no = ship_lines.ord_no
and ord_lines.line_no = ship_lines.ord_line) as smallint)
as shipped
from ord_lines
where cust_no = ?
order by ord_no, line_no;