Subject Re: [Firebird-Java] Driver Speed Tests
Author Blas Rodriguez Somoza
Post date 2002-10-16T16:40:41Z
The problem with inserts when using autocommit is a known one. When Type 4 Driver is used in autocommit mode, it starts a transaction and commits for every command, when Interclient uses commit retaining and doing so, don't need to start transactions for every command.
 
The select problem is a new one and that is why it is important to find it.
 
The first tests are the results using your code, the June and October tests are from jDataMaster tool, and are the ones I use to measure the performance.
 
There can be other type of performance problem but very difficult to solve, Interclient and our driver don't use the same communication with the database, and it seems that with long records Interclient performs better. This can be the difference between the inserts with your code (198% of IC) and the inserts with the tests (162%) that use a short record.
----- Original Message -----
Sent: Wednesday, October 16, 2002 4:58 PM
Subject: Re: [Firebird-Java] Driver Speed Tests

When i look at your result with the new tests it is not only a problem with selects,
inserts are also a problem (190% & 198% of Interclient perfomance)
What is the difference between your top tests and below printed results from October ?
 
gruß
Carsten
----- Original Message -----
Sent: Wednesday, October 16, 2002 4:37 PM
Subject: Re: [Firebird-Java] Driver Speed Tests

Hello
 
    I try with your test and the conclusion is that there is a serious performance problem in selects, but it is not related with character sets.
 
    I create a new test from the one you send removing character set and run the tests in Interclient and our driver. The results are
 
    Interclient
        Insert with character set = 11500
        Insert without char set   = 11984
        Select with char set      = 2156
        Select without char set = 1703
 
    Firebird Driver
        Insert with character set = 22834 (198% of IC)
        Insert without char set   = 22812 (190% of IC)
        Select with char set      = 3813 (177% of IC)
        Select without char set = 3188  (187% of IC)
 
    As you can see, the problem is not in the character set use. 
 
    I compare the results of the my June tests with the actual ones. The machine is not the same, so the absolute value is not meaningful. Also the June tests use JDK1.3.1 and now use JDK1.4.1
 
    Interclient (June)
        Insert with Autocommit = 8510
        Select with Autocommit = 3052
        Insert with transaction = 6650
        Select with transaction = 3990
 
    Firebird (June)
        Insert with Autocommit = 14307 (168% of IC)
        Select with Autocommit = 3056 (100% of IC)
        Insert with transaction = 5522 (83% of IC)
        Select with transaction = 3819 (95% of IC)
 
    Interclient (October)
        Insert with Autocommit = 3043
        Select with Autocommit = 734
        Insert with transaction = 1484
        Select with transaction = 735
 
    Firebird (October)
        Insert with Autocommit = 4918 (162% of IC) better 6%
        Select with Autocommit = 1375 (187% of IC) worse 87%
        Insert with transaction = 1441 (97% of IC) worse 14%
        Select with transaction = 1168 (159% of IC) worse 64%
 
    After all those numbers, it seems that there is a performance problem in the Selects due to some code changed since June.
 
    I'll try to locate the problem, but I don't have too much idle time now, I hope others can help.
       
Regards
Blas Rodriguez Somoza
       
----- Original Message -----
Sent: Wednesday, October 16, 2002 10:38 AM
Subject: Re: [Firebird-Java] Driver Speed Tests

Hi,
the tests was done with Win2000 SP3 on Intel 600Mhz, Sun JDK1.3.1_05.
I can give you the exact times later.
 
gruß
Carsten 
----- Original Message -----
Sent: Wednesday, October 16, 2002 10:07 AM
Subject: Re: [Firebird-Java] Driver Speed Tests

Hello
 
    Thanks for the code, I'll try to investigate that this week.
 
    There are some information that can help me when evaluating the problem.
 
    1.- Which JDK are you using?. Because our driver is pure Java, with newer JDK's the performance enhancement is greater than in Interclient.
 
    2.- Can you give me the test time for Interclient and Jaybird?.
 
Regards   
Blas Rodriguez Somoza
----- Original Message -----
Sent: Monday, October 14, 2002 4:58 PM
Subject: [Firebird-Java] Driver Speed Tests

Hi
I've written a little test class to test the perfomance difference between Jaybird and Interclient with varchars with character set WIN1252.
 
This is my db structure:
CREATE DATABASE 'd:\DBTEST.GDB'
USER 'sysdba' PASSWORD 'masterkey'
PAGE_SIZE 4096;
CREATE TABLE T_TEST1 (
    F_TEST1          VARCHAR(250) CHARACTER SET WIN1252,
    F_TEST2          VARCHAR(250) CHARACTER SET WIN1252,
    F_TEST3          VARCHAR(250) CHARACTER SET WIN1252,
    F_TEST4          VARCHAR(250) CHARACTER SET WIN1252,
    F_TEST5          VARCHAR(250) CHARACTER SET WIN1252,
    F_TEST6          VARCHAR(250) CHARACTER SET WIN1252,
    F_TEST7          VARCHAR(250) CHARACTER SET WIN1252,
    F_TEST8          VARCHAR(250) CHARACTER SET WIN1252,
    F_TEST9          VARCHAR(250) CHARACTER SET WIN1252,
    F_TEST10          VARCHAR(250) CHARACTER SET WIN1252
);
 
And this is my test class:
 
import interbase.interclient.CharacterEncodings;
 
import java.sql.*;
import java.util.Properties;
 
public class DBTEST {
    public static final void dbtest(boolean interclient) {
        try {
            Properties dbProperties = new Properties();
            dbProperties.put("user","sysdba" );
            dbProperties.put("password", "masterkey");
            dbProperties.put("charSet",CharacterEncodings.Cp1252);
            dbProperties.put("lc_ctype", "WIN1252");
            Connection con = null;
            if (interclient) {
                Class.forName("interbase.interclient.Driver");
                con = DriverManager.getConnection("jdbc:interbase://localhost/d:/dbtest.gdb", dbProperties);
            } else {
                Class.forName("org.firebirdsql.jdbc.FBDriver");
                con = DriverManager.getConnection("jdbc:firebirdsql://localhost/d:/dbtest.gdb", dbProperties);
            }
            long start = System.currentTimeMillis();
            PreparedStatement insert = con.prepareStatement("insert into t_test1 values (?,?,?,?,?,?,?,?,?,?)");
            for (int i= 1;i<=20000;i++) {
                insert.setString(1, "ölkkkdsklsdaöd0590945650096945ß945ß46ßsakldsalk9034298428 45290904528420z5928dfldsf"+i);
                insert.setString(2, "iowerw8290nvdshdskljkfduiuj-xycäöüäpf3490384jhkffkhdfjgfdhfhdhfh92ß" + i);
                insert.setString(3, "dsahjdsalkasä3qpüp3410p4319p341jjludiouaidjilja" + i);
                insert.setString(4, "hkdsuuzhdsauidsazhs" + i);
                insert.setString(5, "cx,mkjaä3420990320984390843kjghjghjf´ß" + i);
                insert.setString(6, "sdkhkjdsau98712098912pk<ä#dsaüaasdääöasööüööösa" + i);
                insert.setString(7, "sajkhjasiu7wq987e902ß0pjxycj9fgklgjkgfjkjkdkjldfjkgfjkfdkjfdkjkjgfkjldkjdfkljqei8üoeöälp" + i);
                insert.setString(8, "cx.,cxcöö0pßq0ßßq0ßew90idlkdslökiaoödiaopdoixüycü+yxcxpü+y" + i);
                insert.setString(9, "12309912031ß13820818308108091238901ß830183208129ß028128hcxkh" + i);
                insert.setString(10, "cxklkxyhjsclisauuduiouduosuisauasiu128790132909871239891213209189" + i);
                insert.executeUpdate();
            }
            insert.close();
            long ende = System.currentTimeMillis();
            System.out.println("Time for insert into test1:"+((ende-start)) );
            Statement select = con.createStatement();
            ResultSet rs = select.executeQuery("select * from t_test1");
            while (rs.next()) {
                String s1 = rs.getString("f_test1");
                String s2 = rs.getString("f_test2");
                String s3 = rs.getString("f_test3");
                String s4 = rs.getString("f_test4");
                String s5 = rs.getString("f_test5");
                String s6 = rs.getString("f_test6");
                String s7 = rs.getString("f_test7");
                String s8 = rs.getString("f_test8");
                String s9 = rs.getString("f_test9");
                String s10 = rs.getString("f_test10");
            }
            rs.close();
            select.close();
            long ende2 = System.currentTimeMillis();
            System.out.println("Time for select from test1:" + ((ende2 - ende)));
            con.close();
        } catch (ClassNotFoundException e) {
            System.err.println("Error:" + e.getMessage());
            e.printStackTrace();
        }  catch (SQLException e2) {
            System.err.println("Error:"+e2.getMessage());
            e2.printStackTrace();
        }
    }
 

    public static void main(String[] args) {
        DBTEST.dbtest(false);
 
    }
}
 
With inserts Jaybird is about 30-35% slower than Interclient and with Selects it's 70-75%.
Any comments ?
 
gruß
carsten Schäfer
 
 


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.


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.


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.


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.


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.