Re: TypeInfoCache - Mailing list pgsql-jdbc

Hello, dear JDBC mailing list members,

At first I hope you all had a merry christmas. Regarding the patch I
sent, another important argument is this one: I createt a benchmark for
getCharacterStream vs. getStream running on a table with a varchar(50)
and a text column, each containing 50 chars of data.  These are the
benchmarks for the differences of calling 1000 times the given function
on a Core2Duo 2Ghz and JDK1.5:

    getString on text_col took 8.27384ms
    getCharacterStream on text_col took 14.26489ms
    Factor: 1.724095462324628
    getString on varchar_col took 9.57153ms
    getCharacterStream on varchar_col took 12.76733ms
    Factor: 1.3338860140437319

Please note only the getString() vs.
getCharacterStream()+creatingAString are benchmarked, not the DB
connection itself (which is the real bottleneck). And please note only
those applications that really use getCharacterStream are a bit slower,
but as I explained in my previous mails, they do it for a good reason.

Conclusion: There is no real performance loss even in the worst case
(which should never occur anyway), so please accept my patch. I wish you
all a happy new year!

With best regards,
Daniel Migowski

package de.ikoffice.jdbc;

import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class StringVsStreamTest {

    public static void main(String args[]) throws Exception {
        long nanos; int count;
        char[] buffer = new char[1000];
        long tempNanos1;
        long tempNanos2;

        Class.forName("org.postgresql.Driver");

        Connection c = DriverManager.getConnection("jdbc:postgresql://192.168.1.3:5432/Test","ik","ik0000");
        Statement s = c.createStatement();
        ResultSet r = s.executeQuery("SELECT * FROM tbl_test");
        r.next();

        for( int j = 0; j < 10; j++ ) {
            // Ignore the times for the first run to be sure no JIT compilation slows us down.
            for( int i=0; i<1000; i++ ) {
                String string = r.getString("varchar_col");
            }
            for( int i=0; i<1000; i++ ) {
                Reader reader = r.getCharacterStream("varchar_col");
                count = reader.read(buffer, 0, 1000);
                String string = new String(buffer,0,count);
            }
            for( int i=0; i<1000; i++ ) {
                String string = r.getString("text_col");
            }
            for( int i=0; i<1000; i++ ) {
                Reader reader = r.getCharacterStream("text_col");
                count = reader.read(buffer, 0, 1000);
                String string = new String(buffer,0,count);
            }
        }

        // Now do the real measurement.
        nanos = System.nanoTime();
        for( int i=0; i<1000; i++ ) {
            String string = r.getString("text_col");
        }
        tempNanos1 = System.nanoTime()-nanos;
        System.out.println("getString on text_col took "+((tempNanos1)/100000d)+"ms");

        nanos = System.nanoTime();
        for( int i=0; i<1000; i++ ) {
            Reader reader = r.getCharacterStream("text_col");
            count = reader.read(buffer, 0, 1000);
            String string = new String(buffer,0,count);
        }
        tempNanos2 = System.nanoTime()-nanos;
        System.out.println("getCharacterStream on text_col took "+((tempNanos2)/100000d)+"ms");

        System.out.println("Factor: " + (tempNanos2*1d/tempNanos1));

        nanos = System.nanoTime();
        for( int i=0; i<1000; i++ ) {
            String string = r.getString("varchar_col");
        }
        tempNanos1 = System.nanoTime()-nanos;
        System.out.println("getString on varchar_col took "+((tempNanos1)/100000d)+"ms");

        nanos = System.nanoTime();
        for( int i=0; i<1000; i++ ) {
            Reader reader = r.getCharacterStream("varchar_col");
            count = reader.read(buffer, 0, 1000);
            String string = new String(buffer,0,count);
        }
        tempNanos2 = System.nanoTime()-nanos;
        System.out.println("getCharacterStream on varchar_col took "+((tempNanos2)/100000d)+"ms");

        System.out.println("Factor: " + (tempNanos2*1d/tempNanos1));

    }

}