I am in the process of upgrading from Interbase 6.0.1 to Firebird 1.5.2
and
I am seeing a substantial performance hit in our application. I put
together a small performance test to compare raw SQL performance of both

databases inserting, querying and updating a simple table to help
quantity /
resolve the problem. As far as I can tell, it looks like Firebird is
running at less than half the speed of Interbase OOTB....

I am running on XP Pro SP2, Pentium M 1.8Ghz with 1024MB Ram. I am
using
jaybird 1.55 on JDK 1.4.2_06 in both cases. I have included the code
and
the DDL below.

I am wondering if this is the expected behaviour, or if there are there
any
configuration parameters that I can tune to improve the situation. Any
help
would really be appreciated.

Thanks,

Dave

---

On Firebird 1.5.2 I get the following :

C:\home\main\bin>runclass DBSQLPerformanceProfiler test.fdb 5000
looped 5000 times in 15882 millis (314 loops per second)
looped 5000 times in 16336 millis (306 loops per second)
looped 5000 times in 16101 millis (310 loops per second)
looped 5000 times in 16007 millis (312 loops per second)
looped 5000 times in 15846 millis (315 loops per second)
looped 5000 times in 16311 millis (306 loops per second)
looped 5000 times in 16498 millis (303 loops per second)
looped 5000 times in 15857 millis (315 loops per second)
looped 5000 times in 15811 millis (316 loops per second)
looped 5000 times in 16765 millis (298 loops per second)


With Interbase 6.0.1 I see the following :

C:\home\main\bin>runclass DBSQLPerformanceProfiler test.fdb 5000
looped 5000 times in 5752 millis (869 loops per second)
looped 5000 times in 5611 millis (891 loops per second)
looped 5000 times in 5579 millis (896 loops per second)
looped 5000 times in 5751 millis (869 loops per second)
looped 5000 times in 5783 millis (864 loops per second)
looped 5000 times in 5736 millis (871 loops per second)
looped 5000 times in 5768 millis (866 loops per second)
looped 5000 times in 5736 millis (871 loops per second)
looped 5000 times in 5782 millis (864 loops per second)
looped 5000 times in 5751 millis (869 loops per second)


I also tried Interbase 7.5, and it seems to be slightly faster that
interbase 6.


---
The code
---
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.util.Date;

public class DBSQLPerformanceProfiler {

private static int totalEvents = 0;

public static void main(String args[]) throws Exception {

if(args.length == 2) {
int innerLoopCount = Integer.parseInt(args[1]);
int outterLoopCount = 10;

for(int i=0; i<outterLoopCount; i++) {
long time = generateSQLLocationEventPairs(args[0],
innerLoopCount *
i, innerLoopCount);
System.out.println("looped " + innerLoopCount + " times in " +
time
+ " millis (" + ((innerLoopCount * 1000) / time) + " loops per
second)");
}
} else {
usage();
}
}

public static void usage() {
System.err.println("DBSQLPerformanceProfiler <db path>
<iterations>");
}

private static long generateSQLLocationEventPairs(String dbPath, int
startingId, int eventPairs) throws Exception {
Connection con = getConnection(dbPath);
PreparedStatement update = con.prepareStatement("update TEST_TABLE
set
END_TIME = ? where ID = ?");
PreparedStatement select = con.prepareStatement("select
ID,NAME,START_TIME from TEST_TABLE where ID = ?");
PreparedStatement insert = con.prepareStatement("insert into
TEST_TABLE(ID,NAME,START_TIME) VALUES(?,?,?)");

try {
long start = System.currentTimeMillis();
for(int i=0; i<eventPairs; i++) {
insert.setInt(1, startingId + i);
insert.setString(2,
String.valueOf(System.currentTimeMillis()));
insert.setTimestamp(3, new Timestamp(new Date().getTime()));
insert.execute();

select.setInt(1, startingId + i);
select.execute();

update.setTimestamp(1, new Timestamp(new Date().getTime()));
update.setInt(2, startingId + i);
update.executeUpdate();
}

return System.currentTimeMillis() - start;

} finally {

update.close();
select.close();
insert.close();
con.close();
}
}

private static Connection getConnection(String dbPath) throws
Exception {
Class.forName("org.firebirdsql.jdbc.FBDriver");

return
DriverManager.getConnection("jdbc:firebirdsql:localhost/3050:"
+ dbPath,"sysdba","masterkey");
}
}

---
The DDL
---
CREATE TABLE "TEST_TABLE"
(
"ID" INTEGER NOT NULL,
"NAME" VARCHAR(255) NOT NULL,
"START_TIME" TIMESTAMP NOT NULL,
"END_TIME" TIMESTAMP,
PRIMARY KEY ("ID")
);



[Non-text portions of this message have been removed]