Performance of setTimestamp and getTimestamp - Mailing list pgsql-jdbc

Hi all,

Not sure if this has been mentioned before but I have found that in my
development an extraordinary amount of time is being spent in these
two methods, just to give you an idea in two cases recently during
bulk operations I have found that 21% and 72% of the time to do the
bulk loads/bulk processing has been spent in setTimestamp and
getTimestamp respectively.

I first found that setTimestamp was going to be a performance issue
for me so I decided to see if I could find a faster way of doing it,
from the test code below I found that I had a 3 times improvement in
just using SimpleDateFormat to format the timestamp (the setTimestamp
method was copied from the jdbc drivers with a couple of small
changes).

I was just wondering firstly if this has been noticed/talked about
previously, secondly whether anything is being done about this (or if
it is even an issue for others), and thirdly whether you would like a
patch if I made changes myself.

Note that at present I am using 7.4 and so I am not so sure how this
fits in with the push for 8.

Cheers
Bryce

import java.sql.Timestamp;
import java.util.Date;
import java.text.SimpleDateFormat;
import java.text.FieldPosition;

public class Test {

    private static StringBuffer sbuf = new StringBuffer(32);

    private static final int COUNT = 100000;

    public static void main(String[] args) {
        Timestamp timestamp = new Timestamp((new Date()).getTime());

        long startTime = System.currentTimeMillis();
        for (int i = 0; i < COUNT; i++) {
            setTimestamp(0, timestamp);
        }
        long endTime = System.currentTimeMillis();

        String string1 = sbuf.toString();

        System.err.println("setTimestamp took: " + (endTime - startTime));

        //'2002-01-01 23:59:59.123456-0130'
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd
HH:mm:ss.SSS000Z");
        FieldPosition fieldPos = new FieldPosition(0);
        startTime = System.currentTimeMillis();
        for (int i = 0; i < COUNT; i++) {
            sbuf.setLength(0);
            sbuf.ensureCapacity(32);
            format.format(timestamp, sbuf, fieldPos);
        }
        endTime = System.currentTimeMillis();

        String string2 = sbuf.toString();

        System.err.println("format took: " + (endTime - startTime));

        System.err.println("string1: " + string1);
        System.err.println("string2: " + string2);
    }

    public static void setTimestamp(int parameterIndex, Timestamp x) {
        // Use the shared StringBuffer
        synchronized (sbuf)
        {
            sbuf.setLength(0);
            sbuf.ensureCapacity(32);
            //format the timestamp
            //we do our own formating so that we can get a format
            //that works with both timestamp with time zone and
            //timestamp without time zone datatypes.
            //The format is '2002-01-01 23:59:59.123456-0130'
            //we need to include the local time and timezone offset
            //so that timestamp without time zone works correctly
            int l_year = x.getYear() + 1900;

            // always use four digits for the year so very
            // early years, like 2, don't get misinterpreted
            int l_yearlen = String.valueOf(l_year).length();
            for (int i = 4; i > l_yearlen; i--)
            {
                sbuf.append("0");
            }

            sbuf.append(l_year);
            sbuf.append('-');
            int l_month = x.getMonth() + 1;
            if (l_month < 10)
                sbuf.append('0');
            sbuf.append(l_month);
            sbuf.append('-');
            int l_day = x.getDate();
            if (l_day < 10)
                sbuf.append('0');
            sbuf.append(l_day);
            sbuf.append(' ');
            int l_hours = x.getHours();
            if (l_hours < 10)
                sbuf.append('0');
            sbuf.append(l_hours);
            sbuf.append(':');
            int l_minutes = x.getMinutes();
            if (l_minutes < 10)
                sbuf.append('0');
            sbuf.append(l_minutes);
            sbuf.append(':');
            int l_seconds = x.getSeconds();
            if (l_seconds < 10)
                sbuf.append('0');
            sbuf.append(l_seconds);
            // Make decimal from nanos.
            char[] l_decimal = {'0', '0', '0', '0', '0', '0', '0', '0', '0'};
            char[] l_nanos = Integer.toString(x.getNanos()).toCharArray();
            System.arraycopy(l_nanos, 0, l_decimal, l_decimal.length -
l_nanos.length, l_nanos.length);
            sbuf.append('.');
            sbuf.append(l_decimal, 0, 6);
            //add timezone offset
            int l_offset = -(x.getTimezoneOffset());
            int l_houros = l_offset / 60;
            if (l_houros >= 0)
            {
                sbuf.append('+');
            }
            else
            {
                sbuf.append('-');
            }
            if (l_houros > -10 && l_houros < 10)
                sbuf.append('0');
            if (l_houros >= 0)
            {
                sbuf.append(l_houros);
            }
            else
            {
                sbuf.append( -l_houros);
            }
            int l_minos = l_offset - (l_houros * 60);
            if (l_minos != 0)
            {
                if (l_minos > -10 && l_minos < 10)
                    sbuf.append('0');
                if (l_minos >= 0)
                {
                    sbuf.append(l_minos);
                }
                else
                {
                    sbuf.append( -l_minos);
                }
            }
        }

    }
}