firebird-java - Test results: JayBird vs Interclient on blob select on Linux and Windows

Subject	Test results: JayBird vs Interclient on blob select on Linux and Windows
Author	Sergei P. Volin
Post date	2003-02-27T22:37:52Z

Hi,

I've got some results which I would like to discuss here because they make me very pessimistic on using JayBird on Linux

for selecting blobs and even on Windows for this purpose. With other things of JayBird I'm quite satisfied.

For test I create a 'TEST_TABLE' table with one field 'BLOB_FIELD' of BLOB SUB_TYPE 0 SEGMENT SIZE 1024

and inserted 150 records of approximately 50Kb size each with 7.5Mb totally.

With the test code which you will find below I select blob field into binary array by IC2.5 and JayBird RC3 on both Windows and Linux.

Windows XP server: Athlon XP 1000MHz, 512MB RAM

Linux Slackware8.1 server: Athlon2000+(1666MHz), 1024MB RAM

So the expected servers performance rate is about 1.6 in favor of Linux server.

On both servers:

Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.1_01-b01)
Java HotSpot(TM) Client VM (build 1.4.1_01-b01, mixed mode)

The measured times:

time1 - spent on connection and prepare statement;

time2 - spent on execute query;

time3 - spent on selecting blobs into array from ResultSet;

The most sensitive and the most interesting is time2 parameter. The best result was reached on Windows with IC2.5 - ~0ms.

The worse is on Linux with JayBird - 18415ms. Look difference - isn't amazing? I made 3 runs on Linux and 4 runs on Windows because of one extra value time1=11446ms for JayBird (perhaps this result for time1 should be ignored).

Here it is the results (all times in msec, comma separates thousands!):

Interclient 2.5/Windows JayBirdRC3/Windows

time1 time2 time3 time1 time2 time3

2244 0 1061 1452 (65%) 1702 (inf%) 301 (28% of IC)

1642 0 962 11446(697%) 1783 (inf%) 210 (22% of IC)

1622 10 912 1392 (86%) 1773 (17,730%) 200 (22% of IC)

1663 10 941 1402 (84%) 1613 (16,130%) 270 (29% of IC)

Interclient 2.5/Linux JayBirdRC3/Linux

time1 time2 time3 time1 time2 time3

333 6 283 317 18415 (306,917%) 140

333 5 282 317 18403 (368,060%) 140

332 6 280 317 18411 (306,850%) 140

My resume on selecting blobs:

1. IC2.5 is really platform independent. Excelent result both on Windows and Linux.

2. JayBird show worse results wrt IC2.5 in 170 times on Windows and more than 4500 times on Linux taking the server performance rate into account.

3. JayBird on Linux produced worse results than JayBird on Windows in ~17 times taking the server performance rate into account.

How do you like these? Any thoughts? May be I was wrong with some configuration parameters because I've never seen anything like this in the previous discussions? Then which one?

I will be very grateful for any hint because I'm a little in rush with finishing my project.

Thank you,

Sergei Volin.

Here it is the code:

--------------------------------------------------------

import java.sql.*;

public class test_blob
{
public static void main(String args[])
{
        for (int i=0; i<args.length; i++)
            System.out.println("args["+i+"] = "+args[i]);
        String databaseURL="", user="", password="", driverName = "";
        user = "sysdba";
        password = "password";
        if ("win".equals(args[0])) {
            if ("fb".equals(args[1])) {
                databaseURL = "jdbc:firebirdsql:localhost/3050:C:\\db\\test.gdb";
                driverName = "org.firebirdsql.jdbc.FBDriver";
            } else if ("ic".equals(args[1])) {
                databaseURL = "jdbc:interbase://localhost/C:\\db\\test.gdb";
                driverName = "interbase.interclient.Driver";
            }
        } else if ("linux".equals(args[0])) {
            if ("fb".equals(args[1])) {
                databaseURL = "jdbc:firebirdsql:localhost/3050:/home/admin/test.gdb";
                driverName = "org.firebirdsql.jdbc.FBDriver";
            } else if ("ic".equals(args[1])) {
                databaseURL = "jdbc:interbase://localhost/home/admin/test.gdb";
                driverName = "interbase.interclient.Driver";
            }
        }
        System.out.println(driverName);

     Connection con;
     PreparedStatement ps;
     ResultSet rs;
     int i=0;
     byte[] bf;
     long time0 = 0, time1 = 0, time2 = 0, time3 = 0, etime = 0;
     time0 = System.currentTimeMillis();

     try {
         Class.forName(driverName);
         con = DriverManager.getConnection(databaseURL,user,password);
         String sql = "SELECT blob_field FROM TEST_TABLE";
         ps = con.prepareStatement(sql);
         time1 = System.currentTimeMillis();
         etime = time1-time0;
         System.out.println("time1 = "+etime);

         rs = ps.executeQuery();
         time2 = System.currentTimeMillis();
         etime = time2-time1;
         System.out.println("time2 = "+etime);

         while ( rs.next() ) {
           i++;
           bf = rs.getBytes(1);
           //System.out.println(i+" "+bf.length);
         }
         time3 = System.currentTimeMillis();
         etime = time3-time2;
         System.out.println("time3 = "+etime);

         rs.close();
         ps.close();
         con.close();

        }
     catch ( ClassNotFoundException cnfe )
        {
          System.err.println("Couldn't locate the driver class: "+cnfe);
        }
     catch ( SQLException se )
        {
          System.err.println("Exception creating the database connection: "+se);
          se.printStackTrace(System.out);
        }
}
}