Обсуждение: bytea memory improvement

   The current postgresql driver has some memory issues when reading
bytea fields from the backend.

   The problem is in the class org.postgresql.util.PGbytea, in method
public static byte[] toBytes(byte[] s).
   This method (as I understood it) translates from the wire protocol to
the java byte[] for the user. The current implementation uses 2 buffers
(the receiving buffer and one temp) with the wire size plus one smaller
buffer (the final translated buffer).
   For big files (bytea fields can be as big as 2GB) this is too
expensive in RAM (to download a field of 100MB I need at least 300MB
free in the client).
     One workaround could be to translate inplace on the receive buffer
(I think it is not used for anything else), and then copy to the right
size final buffer, but this would imply to alter the the receiving
buffer, not very elegant.

   My solution is to have a threshold (I think 1MB is a balanced value)
and below that execute as always, 3 buffers. Above, do an extra cycle
through the incoming buffer, compute the final buffer size, and then
behave as befoe (but skip the last part - we already have the right size
of the buffer, so we don't need to do the last copy).

   I've implemented the code (it's very simple), and tested it
(comparing the old function and the new) and it look ok.

   The overhead for passing one more time in the initial buffer is about
30ms for each 5MB in a Celeron M 1.6GHz.

     I hope this code (or some improved version of it) could make it's
way into the driver, I need to work with lots of big bytea fields and
the memory constraints are very hard to meet.

    Thanks for the nice work,
--

Luis Flores

Analista de Sistemas

*Evolute* - Consultoria Informática

<http://www.evolute.pt> Email: lflores@evolute.pt
<mailto:lflores@evolute.pt>

Tel: (+351) 212949689


AVISO DE CONFIDENCIALIDADE
Esta mensagem de correio electrónico e eventuais ficheiros anexos são
confidenciais e destinados apenas à(s) pessoa(s) ou entidade(s) acima
referida(s), podendo conter informação privilegiada e confidencial, a
qual não poderá ser divulgada, copiada, gravada ou distribuída nos
termos da lei vigente. Caso não seja o destinatário da mensagem, ou se
ela lhe foi enviada por engano, agradecemos que não faça uso ou
divulgação da mesma. A distribuição ou utilização da informação nela
contida é interdita. Se recebeu esta mensagem por engano, por favor
notifique o remetente e apague este e-mail do seu sistema. Obrigado.

CONFIDENTIALITY NOTICE
This e-mail transmission and eventual attached files are intended only
for the use of the individual(s) or entity(ies) named above and may
contain information that is both privileged and confidential and is
exempt from disclosure under applicable law. If you are not the intended
recipient, you are hereby notified that any disclosure, copying,
distribution or use of any of the information contained in this
transmission is strictly restricted. If by any means you have received
this transmission in error, please immediately notify the sender and
delete this e-mail from your system. Thank you.
/*-------------------------------------------------------------------------
*
* Copyright (c) 2003-2005, PostgreSQL Global Development Group
*
* IDENTIFICATION
*   $PostgreSQL: pgjdbc/org/postgresql/util/PGbytea.java,v 1.12 2005/01/11 08:25:49 jurka Exp $
*
*-------------------------------------------------------------------------
*/
package org.postgresql.util;

import java.sql.*;

/**
 * Converts to and from the postgresql bytea datatype used by the backend.
 */
public class PGbytea
{
    private static final int MAX_3_BUFF_SIZE = 1024*1024;

    /*
     * Converts a PG bytea raw value (i.e. the raw binary representation
     * of the bytea data type) into a java byte[]
     */
    public static byte[] toBytes(byte[] s) throws SQLException
    {
        if (s == null)
            return null;
        final int slength = s.length;
        byte[] buf = null;
        int correctSize = slength;
        if (slength > MAX_3_BUFF_SIZE)
        {
//            long l = System.currentTimeMillis();
            // count / * //
            for (int i = 0; i < slength; ++i)
            {
                byte current = s[i];
                if (current == '\\')
                {
                    byte next = s[ ++i ];
                    if (next == '\\')
                    {
                        --correctSize;
                    }
                    else
                    {
                        correctSize -= 3;
                    }
                }
            }
//System.out.println( "TOOK: " + ( System.currentTimeMillis() - l ) + "ms - SAVED " + ( slength - correctSize ) + " ON
SECONDBUFFER" ); 
            buf = new byte[correctSize];
        }
        else
        {
            buf = new byte[slength];
        }
        int bufpos = 0;
        int thebyte;
        byte nextbyte;
        byte secondbyte;
        for (int i = 0; i < slength; i++)
        {
            nextbyte = s[i];
            if (nextbyte == (byte)'\\')
            {
                secondbyte = s[++i];
                if (secondbyte == (byte)'\\')
                {
                    //escaped \
                    buf[bufpos++] = (byte)'\\';
                }
                else
                {
                    thebyte = (secondbyte - 48) * 64 + (s[++i] - 48) * 8 + (s[++i] - 48);
                    if (thebyte > 127)
                        thebyte -= 256;
                    buf[bufpos++] = (byte)thebyte;
                }
            }
            else
            {
                buf[bufpos++] = nextbyte;
            }
        }
        if (bufpos == correctSize)
        {
//System.out.println( "SKIPPED LAST BUFFER" );
            return buf;
        }
        byte[] l_return = new byte[bufpos];
        System.arraycopy(buf, 0, l_return, 0, bufpos);
        return l_return;
    }

    /*
     * Converts a java byte[] into a PG bytea string (i.e. the text
     * representation of the bytea data type)
     */
    public static String toPGString(byte[] p_buf) throws SQLException
    {
        if (p_buf == null)
            return null;
        StringBuffer l_strbuf = new StringBuffer(2 * p_buf.length);
        for (int i = 0; i < p_buf.length; i++)
        {
            int l_int = (int)p_buf[i];
            if (l_int < 0)
            {
                l_int = 256 + l_int;
            }
            //we escape the same non-printable characters as the backend
            //we must escape all 8bit characters otherwise when convering
            //from java unicode to the db character set we may end up with
            //question marks if the character set is SQL_ASCII
            if (l_int < 040 || l_int > 0176)
            {
                //escape charcter with the form \000, but need two \\ because of
                //the parser
                l_strbuf.append("\\");
                l_strbuf.append((char)(((l_int >> 6) & 0x3) + 48));
                l_strbuf.append((char)(((l_int >> 3) & 0x7) + 48));
                l_strbuf.append((char)((l_int & 0x07) + 48));
            }
            else if (p_buf[i] == (byte)'\\')
            {
                //escape the backslash character as \\, but need four \\\\ because
                //of the parser
                l_strbuf.append("\\\\");
            }
            else
            {
                //other characters are left alone
                l_strbuf.append((char)p_buf[i]);
            }
        }
        return l_strbuf.toString();
    }
}

On Wed, 28 Jun 2006, Luis Vilar Flores wrote:

>     Can any JDBC developer give some feedback about this patch ? Memory
> usage is a real issue for a few applications ...
>

It looks like a reasonable thing to do, but could you give us some more
details on the cost/benefits?  Your original email said it cost an extra
30ms for 5MB of data on your machine.  What's percentage of the original
cost is this?  Also you could be more clear on what percentage of memory
this saves.  For the worst case scenario your going to get four bytes of
escaped data for every real byte so the total size of the original method
would be 4 + 4 + 1 and for the new method 4 + 1, so a savings of 44%?  Is
that what you've calculated?

Finally you haven't actually submitted a patch, you've just sent a
modified copy of a whole file.  Since it's a small file that changes
infrequently it's not a big deal, but we prefer context diffs if
you can.

Kris Jurka

Kris Jurka wrote:
>
>
> On Wed, 28 Jun 2006, Luis Vilar Flores wrote:
>
>>     Can any JDBC developer give some feedback about this patch ?
>> Memory usage is a real issue for a few applications ...
>>
>
> It looks like a reasonable thing to do, but could you give us some
> more details on the cost/benefits?
My sugestion is to trade some CPU time for memory in large files.
The benefits are: memory saving (one byte[] of final size at least),
less garbage collecting, these buffers are always created on a bytea read,
we also save the CPU time of one System.arraycopy, but this is a native
method and should be very fast, so it's not a big deal.
The only cost is one more pass through  the original buffer, this pass
is just to count the final array size.

In simple terms, the original method did:
incoming buffer size N
allocate temp buffer size N
for through incoming buffer, translate data from incoming to temp
allocate final buffer, size M (M is between N and N/4)
copy temp buffer to final buffer
return final buffer

The new method does:
incoming buffer size N
for through incoming buffer, calculate final buffer size
allocate final buffer, size M (M is between N and N/4)
for through incoming buffer, translate data from incoming to final
return final buffer

>   Your original email said it cost an extra 30ms for 5MB of data on
> your machine.  What's percentage
> of the original cost is this?
This cost is to read and count the escaped bytes of a 5MB byte array  in
a for cycle on a CeleronM 1.6, but keep in mind that we save the last
System.arraycopy (last if in the method).
Originally this extra passage didn't existed.
I will make more detailed timing in the full method body (old and new),
and send results tomorrow.
> Also you could be more clear on what percentage of memory this saves.
> For the worst case scenario your going to get four bytes of escaped
> data for every real byte so the total size of the original method
> would be 4 + 4 + 1 and for the new method 4 + 1, so a savings of 44%?
> Is that what you've calculated?
Yeap, that's for files larger than 1MB on the incoming buffer (the
threshold could be defined by some property) - with the extra pass on
the incoming array I calculate the final size, so I can skip the temp
buffer, so we save the 44% (on worst case), the minimum savings is 16.7%
for no escaped data.
>
> Finally you haven't actually submitted a patch, you've just sent a
> modified copy of a whole file.  Since it's a small file that changes
> infrequently it's not a big deal, but we prefer context diffs if you can.
>
> Kris Jurka
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
I will send the diff too.

    Thanks for the comments ...

--

Luis Flores

Analista de Sistemas

*Evolute* - Consultoria Informática

<http://www.evolute.pt> Email: lflores@evolute.pt
<mailto:lflores@evolute.pt>

Tel: (+351) 212949689


AVISO DE CONFIDENCIALIDADE
Esta mensagem de correio electrónico e eventuais ficheiros anexos são
confidenciais e destinados apenas à(s) pessoa(s) ou entidade(s) acima
referida(s), podendo conter informação privilegiada e confidencial, a
qual não poderá ser divulgada, copiada, gravada ou distribuída nos
termos da lei vigente. Caso não seja o destinatário da mensagem, ou se
ela lhe foi enviada por engano, agradecemos que não faça uso ou
divulgação da mesma. A distribuição ou utilização da informação nela
contida é interdita. Se recebeu esta mensagem por engano, por favor
notifique o remetente e apague este e-mail do seu sistema. Obrigado.

CONFIDENTIALITY NOTICE
This e-mail transmission and eventual attached files are intended only
for the use of the individual(s) or entity(ies) named above and may
contain information that is both privileged and confidential and is
exempt from disclosure under applicable law. If you are not the intended
recipient, you are hereby notified that any disclosure, copying,
distribution or use of any of the information contained in this
transmission is strictly restricted. If by any means you have received
this transmission in error, please immediately notify the sender and
delete this e-mail from your system. Thank you.

    Hello,

    This time I believe to have all tests and source needed to have the
patch accepted.
    To all that already forgot the first emails, I  developed  an
modified version of the  method toBytes from the
org.postgresql.util.PGbytea class.
    The old method uses 3 buffers to translate the data from the nework
to the client, this uses too much memory.
    My method only uses 2 buffers, but does one more pass through the
original buffer (to calculate it's final size).

    Bellow is a table with times and memory usage of the 2 methods,
using the supplied ByteaTest class:
OLD method:
size: 0.5MB execute+next: 49ms getBytes: 18ms used mem: 74505KB
size: 1.5MB execute+next: 94ms getBytes: 53ms used mem: 48004KB
size: 2.5MB execute+next: 147ms getBytes: 110ms used mem: 23537KB
size: 3.5MB execute+next: 244ms getBytes: 190ms used mem: 24504KB
size: 4.5MB execute+next: 306ms getBytes: 224ms used mem: 31448KB
size: 5.5MB execute+next: 364ms getBytes: 267ms used mem: 38392KB
size: 6.5MB execute+next: 413ms getBytes: 308ms used mem: 45336KB
size: 7.5MB execute+next: 464ms getBytes: 306ms used mem: 52281KB
size: 8.5MB execute+next: 511ms getBytes: 349ms used mem: 59225KB
size: 9.5MB execute+next: 804ms getBytes: 377ms used mem: 66169KB
size: 10.5MB execute+next: 634ms getBytes: 546ms used mem: 73112KB
size: 11.5MB execute+next: 689ms getBytes: 450ms used mem: 80057KB
size: 12.5MB execute+next: 748ms getBytes: 482ms used mem: 87001KB
size: 13.5MB execute+next: 820ms getBytes: 514ms used mem: 93945KB
size: 14.5MB execute+next: 865ms getBytes: 734ms used mem: 100888KB
size: 15.5MB execute+next: 921ms getBytes: 586ms used mem: 107833KB
size: 16.5MB execute+next: 1003ms getBytes: 619ms used mem: 114777KB
size: 17.5MB execute+next: 1030ms getBytes: 652ms used mem: 121721KB
size: 18.5MB execute+next: 1102ms getBytes: 927ms used mem: 128664KB
size: 19.5MB execute+next: 1166ms getBytes: 723ms used mem: 135609KB
size: 20.5MB execute+next: 1217ms getBytes: 735ms used mem: 142583KB
size: 21.5MB execute+next: 1284ms getBytes: 766ms used mem: 149527KB
size: 22.5MB execute+next: 1437ms getBytes: 801ms used mem: 156471KB
size: 23.5MB execute+next: 1425ms getBytes: 833ms used mem: 163415KB
size: 24.5MB execute+next: 1453ms getBytes: 866ms used mem: 170359KB
size: 25.5MB execute+next: 1766ms getBytes: 902ms used mem: 177303KB
size: 26.5MB execute+next: 2004ms getBytes: 939ms used mem: 184247KB
size: 27.5MB execute+next: 1650ms getBytes: 968ms used mem: 191191KB
size: 28.5MB execute+next: 1757ms getBytes: 796ms used mem: 198105KB
size: 29.5MB execute+next: 1770ms getBytes: 1040ms used mem: 205086KB
size: 30.5MB execute+next: 1820ms getBytes: 1074ms used mem: 212030KB
size: 31.5MB execute+next: 1869ms getBytes: 1109ms used mem: 218974KB
size: 32.5MB execute+next: 1930ms getBytes: 1146ms used mem: 225918KB
size: 33.5MB execute+next: 2183ms getBytes: 1177ms used mem: 232862KB
size: 34.5MB execute+next: 2241ms getBytes: 1221ms used mem: 239806KB

NEW method:
size: 0.5MB execute+next: 50ms getBytes: 19ms used mem: 73137KB
size: 1.5MB execute+next: 90ms getBytes: 50ms used mem: 43760KB
size: 2.5MB execute+next: 149ms getBytes: 97ms used mem: 16136KB
size: 3.5MB execute+next: 237ms getBytes: 113ms used mem: 14170KB
size: 4.5MB execute+next: 302ms getBytes: 174ms used mem: 18127KB
size: 5.5MB execute+next: 357ms getBytes: 234ms used mem: 22110KB
size: 6.5MB execute+next: 602ms getBytes: 232ms used mem: 26095KB
size: 7.5MB execute+next: 477ms getBytes: 265ms used mem: 30079KB
size: 8.5MB execute+next: 532ms getBytes: 296ms used mem: 34063KB
size: 9.5MB execute+next: 590ms getBytes: 385ms used mem: 38046KB
size: 10.5MB execute+next: 648ms getBytes: 357ms used mem: 42031KB
size: 11.5MB execute+next: 695ms getBytes: 391ms used mem: 46015KB
size: 12.5MB execute+next: 765ms getBytes: 423ms used mem: 49999KB
size: 13.5MB execute+next: 825ms getBytes: 542ms used mem: 53982KB
size: 14.5MB execute+next: 874ms getBytes: 491ms used mem: 57967KB
size: 15.5MB execute+next: 931ms getBytes: 521ms used mem: 61951KB
size: 16.5MB execute+next: 992ms getBytes: 551ms used mem: 65935KB
size: 17.5MB execute+next: 1063ms getBytes: 694ms used mem: 69918KB
size: 18.5MB execute+next: 1111ms getBytes: 618ms used mem: 73903KB
size: 19.5MB execute+next: 1168ms getBytes: 649ms used mem: 77887KB
size: 20.5MB execute+next: 1230ms getBytes: 654ms used mem: 81903KB
size: 21.5MB execute+next: 1289ms getBytes: 687ms used mem: 85890KB
size: 22.5MB execute+next: 1345ms getBytes: 737ms used mem: 89875KB
size: 23.5MB execute+next: 1415ms getBytes: 751ms used mem: 93861KB
size: 24.5MB execute+next: 1461ms getBytes: 782ms used mem: 97846KB
size: 25.5MB execute+next: 1521ms getBytes: 817ms used mem: 101833KB
size: 26.5MB execute+next: 1587ms getBytes: 848ms used mem: 105817KB
size: 27.5MB execute+next: 1634ms getBytes: 877ms used mem: 109804KB
size: 28.5MB execute+next: 1692ms getBytes: 931ms used mem: 113789KB
size: 29.5MB execute+next: 1748ms getBytes: 944ms used mem: 117775KB
size: 30.5MB execute+next: 1820ms getBytes: 972ms used mem: 121760KB
size: 31.5MB execute+next: 1869ms getBytes: 1005ms used mem: 125747KB
size: 32.5MB execute+next: 1915ms getBytes: 1038ms used mem: 129731KB
size: 33.5MB execute+next: 1983ms getBytes: 1088ms used mem: 133718KB
size: 34.5MB execute+next: 2055ms getBytes: 1103ms used mem: 137703KB

    As you can see the execution time remained almost the same (small
gain on the new version), but memory usage is drastically improved.

    These times were obtained in a Celeron M 1.6GHz laptop with 1GB RAM,
running Fedora Core 5, Java 1.5.0_08 and Postgresql 8.1.4.

    In attach I supply the modified PGbytea.java, the patch versus
8.1-407 source (the 8.2dev-503 is the same), and the test program
ByteaTest.java.

    The test program also validates the correctness of the result
through CRC32.

    Hope to hear some feedback soon, hope I didn't forget anything ...

       Luis Flores

/*
 * ByteaTest.java
 *
 * Created on August 22, 2006, 8:09 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package com.evolute.postgresql.test;

import java.sql.*;
import java.util.zip.*;

/**
 *
 * @author lvflores
 */
public class ByteaTest
{
    private static String url = "jdbc:postgresql://localhost/test_bytea";
    private static String user = "postgres";
    private static String password = "";

    private static final int RUNS = 35;

    private final CRC32 crcCalc = new CRC32();

    private Connection connection = null;

    private long crc32[] = new long[ RUNS ];

    /** Creates a new instance of ByteaTest */
    public ByteaTest( String pgUrl, String pgUser, String pgPass )
    throws Exception
    {
    Class.forName( "org.postgresql.Driver" );
    connection = DriverManager.getConnection( pgUrl, pgUser, pgPass );
    createTable();
    test();
    deleteTable();
    connection.close();
    }


    private void createTable()
    throws Exception
    {
    Statement stm = connection.createStatement();
    try
    {
        stm.executeUpdate( "DROP TABLE test_bytea" );
    }
    catch( Exception ex )
    {
    }
    stm.executeUpdate( "CREATE TABLE test_bytea ("
        + "id INT NOT NULL, "
        + "data BYTEA )" );
    stm.close();
    }

    private void test()
    throws Exception
    {
    // insert
    for( int i = 0; i < RUNS; ++i )
    {
        PreparedStatement pstm = connection.prepareStatement( "INSERT INTO test_bytea ( id, data ) VALUES ( ?, ? )" );
        pstm.setInt( 1, i );
        byte data[] = generateBytes( i );
//        long l = System.currentTimeMillis();
        pstm.setBytes( 2, data );
//        long l1 = System.currentTimeMillis();
        pstm.execute();
//        long l2 = System.currentTimeMillis();
        long usedMem = Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory();
//        System.out.println( "size: " + i + ".5MB setBytes: " + ( l1 -l )
//            + "ms execute: " + ( l2 -l1 ) + "ms used mem: " + ( usedMem / 1024 ) + "KB" );
        pstm.close();
        System.gc();
    }
    // select and check
    for( int i = 0; i < RUNS; ++i )
    {
        PreparedStatement pstm = connection.prepareStatement( "SELECT data FROM test_bytea WHERE ID = ?" );
        pstm.setInt( 1, i );
        long l = System.currentTimeMillis();
        ResultSet rs = pstm.executeQuery();
        rs.next();
        long l1 = System.currentTimeMillis();
        byte data[] = rs.getBytes( 1 );
        long l2 = System.currentTimeMillis();
        crcCalc.reset();
        crcCalc.update( data );
        long crc = crcCalc.getValue();
        if( crc != crc32[ i ] )
        {
        System.out.println( "WRONG DATA on idx " + i );
        }
        long usedMem = Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory();
        System.out.println( "size: " + i + ".5MB execute+next: " + ( l1 -l )
            + "ms getBytes: " + ( l2 -l1 ) + "ms used mem: " + ( usedMem / 1024 ) + "KB" );
        pstm.close();
        System.gc();
    }
    }

    private byte[] generateBytes( int idx )
    {

    byte data[] = new byte[ 1024 * 1024 * idx + 512 * 1024 ];

    for( int i = 0; i < data.length; ++i )
    {
        data[ i ] = ( byte )i;
    }

    crcCalc.reset();
    crcCalc.update( data );
    crc32[ idx ] = crcCalc.getValue();
    return data;
    }

    private void deleteTable()
    throws Exception
    {
    Statement stm = connection.createStatement();
    stm.executeUpdate( "DROP TABLE test_bytea" );
    stm.close();
    }

    public static void main( String arg[] )
    throws Exception
    {
    if( arg.length != 0 && arg.length != 3 )
    {
        System.err.println( "Usage: java com.evolute.postgresql.test.ByteaTest <URL> <USER> <PASSWORD>" );
    }
    else if( arg.length == 3 )
    {
        url = arg[ 0 ];
        user = arg[ 1 ];
        password = arg[ 2 ];
    }
    new ByteaTest( url, user, password );
    }
}
/*-------------------------------------------------------------------------
*
* Copyright (c) 2003-2005, PostgreSQL Global Development Group
*
* IDENTIFICATION
*   $PostgreSQL: pgjdbc/org/postgresql/util/PGbytea.java,v 1.12 2005/01/11 08:25:49 jurka Exp $
*
*-------------------------------------------------------------------------
*/
package org.postgresql.util;

import java.sql.*;

/**
 * Converts to and from the postgresql bytea datatype used by the backend.
 */
public class PGbytea
{
    private static final int MAX_3_BUFF_SIZE = 0;

    /*
     * Converts a PG bytea raw value (i.e. the raw binary representation
     * of the bytea data type) into a java byte[]
     */
    public static byte[] toBytes(byte[] s) throws SQLException
    {
        if (s == null)
            return null;
        final int slength = s.length;
        byte[] buf = null;
        int correctSize = slength;
        if (slength > MAX_3_BUFF_SIZE)
        {
//            long l = System.currentTimeMillis();
            // count / * //
            for (int i = 0; i < slength; ++i)
            {
                byte current = s[i];
                if (current == '\\')
                {
                    byte next = s[ ++i ];
                    if (next == '\\')
                    {
                        --correctSize;
                    }
                    else
                    {
                        correctSize -= 3;
                    }
                }
            }
//System.out.println( "TOOK: " + ( System.currentTimeMillis() - l ) + "ms - SAVED " + ( slength - correctSize ) + " ON
SECONDBUFFER" ); 
            buf = new byte[correctSize];
        }
        else
        {
            buf = new byte[slength];
        }
        int bufpos = 0;
        int thebyte;
        byte nextbyte;
        byte secondbyte;
        for (int i = 0; i < slength; i++)
        {
            nextbyte = s[i];
            if (nextbyte == (byte)'\\')
            {
                secondbyte = s[++i];
                if (secondbyte == (byte)'\\')
                {
                    //escaped \
                    buf[bufpos++] = (byte)'\\';
                }
                else
                {
                    thebyte = (secondbyte - 48) * 64 + (s[++i] - 48) * 8 + (s[++i] - 48);
                    if (thebyte > 127)
                        thebyte -= 256;
                    buf[bufpos++] = (byte)thebyte;
                }
            }
            else
            {
                buf[bufpos++] = nextbyte;
            }
        }
        if (bufpos == correctSize)
        {
//System.out.println( "SKIPPED LAST BUFFER" );
            return buf;
        }
        byte[] l_return = new byte[bufpos];
        System.arraycopy(buf, 0, l_return, 0, bufpos);
        return l_return;
    }

    /*
     * Converts a java byte[] into a PG bytea string (i.e. the text
     * representation of the bytea data type)
     */
    public static String toPGString(byte[] p_buf) throws SQLException
    {
        if (p_buf == null)
            return null;
        StringBuffer l_strbuf = new StringBuffer(2 * p_buf.length);
        for (int i = 0; i < p_buf.length; i++)
        {
            int l_int = (int)p_buf[i];
            if (l_int < 0)
            {
                l_int = 256 + l_int;
            }
            //we escape the same non-printable characters as the backend
            //we must escape all 8bit characters otherwise when convering
            //from java unicode to the db character set we may end up with
            //question marks if the character set is SQL_ASCII
            if (l_int < 040 || l_int > 0176)
            {
                //escape charcter with the form \000, but need two \\ because of
                //the parser
                l_strbuf.append("\\");
                l_strbuf.append((char)(((l_int >> 6) & 0x3) + 48));
                l_strbuf.append((char)(((l_int >> 3) & 0x7) + 48));
                l_strbuf.append((char)((l_int & 0x07) + 48));
            }
            else if (p_buf[i] == (byte)'\\')
            {
                //escape the backslash character as \\, but need four \\\\ because
                //of the parser
                l_strbuf.append("\\\\");
            }
            else
            {
                //other characters are left alone
                l_strbuf.append((char)p_buf[i]);
            }
        }
        return l_strbuf.toString();
    }
}
--- /home/lvflores/Desktop/postgresql-jdbc-8.1-407.src/org/postgresql/util/PGbytea.java    2005-01-11
08:25:49.000000000+0000 
+++ PGbytea.java    2006-08-22 23:37:53.000000000 +0100
@@ -16,8 +16,9 @@
  */
 public class PGbytea
 {
-
-    /*
+    private static final int MAX_3_BUFF_SIZE = 0;
+
+    /*
      * Converts a PG bytea raw value (i.e. the raw binary representation
      * of the bytea data type) into a java byte[]
      */
@@ -25,8 +26,36 @@
     {
         if (s == null)
             return null;
-        int slength = s.length;
-        byte[] buf = new byte[slength];
+        final int slength = s.length;
+        byte[] buf = null;
+        int correctSize = slength;
+        if (slength > MAX_3_BUFF_SIZE)
+        {
+//            long l = System.currentTimeMillis();
+            // count / * //
+            for (int i = 0; i < slength; ++i)
+            {
+                byte current = s[i];
+                if (current == '\\')
+                {
+                    byte next = s[ ++i ];
+                    if (next == '\\')
+                    {
+                        --correctSize;
+                    }
+                    else
+                    {
+                        correctSize -= 3;
+                    }
+                }
+            }
+//System.out.println( "TOOK: " + ( System.currentTimeMillis() - l ) + "ms - SAVED " + ( slength - correctSize ) + " ON
SECONDBUFFER" ); 
+            buf = new byte[correctSize];
+        }
+        else
+        {
+            buf = new byte[slength];
+        }
         int bufpos = 0;
         int thebyte;
         byte nextbyte;
@@ -55,6 +84,11 @@
                 buf[bufpos++] = nextbyte;
             }
         }
+        if (bufpos == correctSize)
+        {
+//System.out.println( "SKIPPED LAST BUFFER" );
+            return buf;
+        }
         byte[] l_return = new byte[bufpos];
         System.arraycopy(buf, 0, l_return, 0, bufpos);
         return l_return;
@@ -103,6 +137,4 @@
         }
         return l_strbuf.toString();
     }
-
-
 }

Luis Vilar Flores wrote:
>     Hope to hear some feedback soon, hope I didn't forget anything ...

I have an idea for a minor improvement. The MAX_3_BUFF_SIZE is set to 0.
Actually, you can immediately return an empty byte array if the size of
the incomming buffer is 0; that could be a static final byte[], because
nobody could do anything with it anyway. In all other cases, the 2
buffer method is simpler and faster, because it uses fewer buffers and
memory accesses, and is therefore the right solution.


Till

till toenges wrote: <blockquote cite="mid44EC66B0.2040108@kyon.de" type="cite"><pre wrap="">Luis Vilar Flores wrote:
</pre><blockquotetype="cite"><pre wrap="">    Hope to hear some feedback soon, hope I didn't forget anything ...
</pre></blockquote><prewrap="">
 
I have an idea for a minor improvement. The MAX_3_BUFF_SIZE is set to 0.
Actually, you can immediately return an empty byte array if the size of
the incomming buffer is 0; that could be a static final byte[], because
nobody could do anything with it anyway. In all other cases, the 2
buffer method is simpler and faster, because it uses fewer buffers and
memory accesses, and is therefore the right solution.


Till
 </pre></blockquote> The MAX_3_BUFF_SIZE can be deleted (and the test that use it too), it was these so that we can set
asize threshold to use 3 buffers (old algorithm), or only 2 (at the beginning it seemed that 2 buffers were slower).<br
/><br/> If the incoming size is 0 we can use the incoming array, I tried to only change the buffer algorithm, the null
casefor instance was already there.<br /><br /> Thanks for the comments,<br /><br /><div class="moz-signature">-- <br
/></div><p><fontcolor="#7da647"><font face="Verdana, sans-serif"><font size="2" style="font-size: 10pt;"> Luis Flores
</font></font></font><p><fontcolor="#7da647"><font face="Verdana, sans-serif"><font size="2" style="font-size: 8pt;">
Analistade Sistemas</font></font></font><p><a href="http://www.evolute.pt"><font face="Verdana, sans-serif"><font
size="2"style="font-size: 8pt;"><b>Evolute</b> - Consultoria Informática<br /><br /></font></font></a> <font
color="#7da647"><fontface="Verdana, sans-serif"><font size="2" style="font-size: 8pt;"> Email: </font></font></font> <a
href="mailto:lflores@evolute.pt"><fontface="Verdana, sans-serif"><font size="2" style="font-size:
8pt;">lflores@evolute.pt</font></font></a><p><font color="#7da647"><font face="Verdana, sans-serif"><font size="2"
style="font-size:8pt;"> Tel: (+351) 212949689</font></font></font><div style="text-align: justify;"><font
color="#7d7d7d"><fontface="Verdana, sans-serif"><font size="1" style="font-size: 7pt;"><br /> AVISO DE
CONFIDENCIALIDADE</font></font></font><br/><font color="#7d7d7d"><font face="Verdana, sans-serif"><font size="1"
style="font-size:7pt;"> Esta mensagem de correio electrónico e eventuais ficheiros anexos são confidenciais e
destinadosapenas à(s) pessoa(s) ou entidade(s) acima referida(s), podendo conter informação privilegiada e
confidencial,a qual não poderá ser divulgada, copiada, gravada ou distribuída nos termos da lei vigente. Caso não seja
odestinatário da mensagem, ou se ela lhe foi enviada por engano, agradecemos que não faça uso ou divulgação da mesma. A
distribuiçãoou utilização da informação nela contida é interdita. Se recebeu esta mensagem por engano, por favor
notifiqueo remetente e apague este e-mail do seu sistema. Obrigado. <br /></font></font></font><font
color="#7d7d7d"><fontface="Verdana, sans-serif"><font size="1" style="font-size: 7pt;"> </font></font></font><br
/><fontcolor="#7d7d7d"><font face="Verdana, sans-serif"><font size="1" style="font-size: 7pt;"> CONFIDENTIALITY
NOTICE</font></font></font><br/><font color="#7d7d7d"><font face="Verdana, sans-serif"><font size="1" style="font-size:
7pt;">This e-mail transmission and eventual attached files are intended only for the use of the individual(s) or
entity(ies)named above and may contain information that is both privileged and confidential and is exempt from
disclosureunder applicable law. If you are not the intended recipient, you are hereby notified that any disclosure,
copying,distribution or use of any of the information contained in this transmission is strictly restricted. If by any
meansyou have received this transmission in error, please immediately notify the sender and delete this e-mail from
yoursystem. Thank you. </font></font></font></div> 

On Wed, 23 Aug 2006, Luis Vilar Flores wrote:

>   To all that already forgot the first emails, I developed an modified
> version of the method toBytes from the org.postgresql.util.PGbytea
> class.  The old method uses 3 buffers to translate the data from the
> nework to the client, this uses too much memory.  My method only uses 2
> buffers, but does one more pass through the original buffer (to
> calculate it's final size).
>

I'm not super impressed with these timing results.  They are certainly
showing some effects due to GC, consider the rise in time here at 10.5MB.

> OLD method:
> size: 9.5MB execute+next: 804ms getBytes: 377ms used mem: 66169KB
> size: 10.5MB execute+next: 634ms getBytes: 546ms used mem: 73112KB
> size: 11.5MB execute+next: 689ms getBytes: 450ms used mem: 80057KB
> size: 12.5MB execute+next: 748ms getBytes: 482ms used mem: 87001KB

I came up with my own contrived benchmark (attached) that attempts to
focus solely on the getBytes() call and avoid the time of fetching
results, but it doesn't give really consistent results and I haven't been
able to come up with a case that actually shows the new method was faster
even with 30MB of data.  This is on Debian Linux / 2xOpteron 246 / jdk
1.5.0-05.

I've committed this to CVS HEAD with a rather arbitrarily set
MAX_3_BUFF_SIZE value of 2MB.  Note that this is also the escaped size, so
we may actually be dealing with output data a quarter of that size.  If
anyone could do some more testing of what a good crossover point would be
that would be a good thing.

Thanks for your patience with this item.

Kris Jurka

Kris Jurka wrote: <blockquote cite="midPine.BSO.4.63.0609260131040.29854@leary2.csoft.net" type="cite"><br /><br /> On
Wed,23 Aug 2006, Luis Vilar Flores wrote: <br /><br /><blockquote type="cite">  To all that already forgot the first
emails,I developed an modified version of the method toBytes from the org.postgresql.util.PGbytea class.  The old
methoduses 3 buffers to translate the data from the nework to the client, this uses too much memory.  My method only
uses2 buffers, but does one more pass through the original buffer (to calculate it's final size). <br /><br
/></blockquote><br/> I'm not super impressed with these timing results.  They are certainly showing some effects due to
GC,consider the rise in time here at 10.5MB. <br /></blockquote> Well, thanks a lot for the attention. My main purpose
wasto reduce the memory footprint. But, before I did the tests, I had the idea that the new method would be slower than
theolder ... So it would only be better on large files, i.e. where the reduced memory usage was more important than raw
speed.This was because of the extra cycle through the array.<br /><blockquote
cite="midPine.BSO.4.63.0609260131040.29854@leary2.csoft.net"type="cite"><br /><blockquote type="cite">OLD method: <br
/>size: 9.5MB execute+next: 804ms getBytes: 377ms used mem: 66169KB <br /> size: 10.5MB execute+next: 634ms getBytes:
546msused mem: 73112KB <br /> size: 11.5MB execute+next: 689ms getBytes: 450ms used mem: 80057KB <br /> size: 12.5MB
execute+next:748ms getBytes: 482ms used mem: 87001KB <br /></blockquote><br /> I came up with my own contrived
benchmark(attached) that attempts to focus solely on the getBytes() call and avoid the time of fetching results, but it
doesn'tgive really consistent results and I haven't been able to come up with a case that actually shows the new method
wasfaster even with 30MB of data.  This is on Debian Linux / 2xOpteron 246 / jdk 1.5.0-05. <br /></blockquote> The new
methodis very similar to the old, but it just computes the final size before the copy. The old method does less
instructionsto convert an array, the new method is only faster when the older is slowed down by garbage
collection/memoryallocation.<br /><blockquote cite="midPine.BSO.4.63.0609260131040.29854@leary2.csoft.net"
type="cite"><br/> I've committed this to CVS HEAD with a rather arbitrarily set MAX_3_BUFF_SIZE value of 2MB.  Note
thatthis is also the escaped size, so we may actually be dealing with output data a quarter of that size.  If anyone
coulddo some more testing of what a good crossover point would be that would be a good thing. <br /></blockquote> I
thinkthe old option should be there for a while, but I hope that the new method proves to be as fast as the old, so we
canjust discard the MAX_3_BUFF_SIZE and always compute the final size - as the method code would be clearer that
way.<br/><blockquote cite="midPine.BSO.4.63.0609260131040.29854@leary2.csoft.net" type="cite"><br /> Thanks for your
patiencewith this item. <br /></blockquote> It's me who thanks for such a great product ...<br /> I will check the new
benchmark,the see the memory usage, and garbage collection ...<br /><blockquote
cite="midPine.BSO.4.63.0609260131040.29854@leary2.csoft.net"type="cite"><br /> Kris Jurka<br /><pre wrap="">
 
<hr size="4" width="90%" />
import java.sql.*;

public class ByteaTest2 {
public static void main(String args[]) throws Exception {    Class.forName("org.postgresql.Driver");    Connection conn
=DriverManager.getConnection("jdbc:postgresql://localhost:5432/jurka","jurka","");
 
    for (int k=0; k<5; k++) {        long t1 = System.currentTimeMillis();        long total = 0;
        for (int j=0; j<10; j++) {            PreparedStatement pstmt = conn.prepareStatement("SELECT
varcharsend(repeat(?,?))");           pstmt.setString(1, "a\\001");            pstmt.setInt(2, 150000);
ResultSetrs = pstmt.executeQuery();            rs.next();            for (int i=0; i<100; i++) {                byte
b[]= rs.getBytes(1);                total += b.length;            }
 
            rs.close();            pstmt.close();        }        long t2 = System.currentTimeMillis();
System.out.println(t2-t1);   }}
 
} </pre></blockquote><br /><br /><div class="moz-signature">-- <br /></div><p><font color="#7da647"><font
face="Verdana,sans-serif"><font size="2" style="font-size: 10pt;"> Luis Flores </font></font></font><p><font
color="#7da647"><fontface="Verdana, sans-serif"><font size="2" style="font-size: 8pt;"> Analista de
Sistemas</font></font></font><p><ahref="http://www.evolute.pt"><font face="Verdana, sans-serif"><font size="2"
style="font-size:8pt;"><b>Evolute</b> - Consultoria Informática<br /><br /></font></font></a> <font
color="#7da647"><fontface="Verdana, sans-serif"><font size="2" style="font-size: 8pt;"> Email: </font></font></font> <a
href="mailto:lflores@evolute.pt"><fontface="Verdana, sans-serif"><font size="2" style="font-size:
8pt;">lflores@evolute.pt</font></font></a><p><font color="#7da647"><font face="Verdana, sans-serif"><font size="2"
style="font-size:8pt;"> Tel: (+351) 212949689</font></font></font><div style="text-align: justify;"><font
color="#7d7d7d"><fontface="Verdana, sans-serif"><font size="1" style="font-size: 7pt;"><br /> AVISO DE
CONFIDENCIALIDADE</font></font></font><br/><font color="#7d7d7d"><font face="Verdana, sans-serif"><font size="1"
style="font-size:7pt;"> Esta mensagem de correio electrónico e eventuais ficheiros anexos são confidenciais e
destinadosapenas à(s) pessoa(s) ou entidade(s) acima referida(s), podendo conter informação privilegiada e
confidencial,a qual não poderá ser divulgada, copiada, gravada ou distribuída nos termos da lei vigente. Caso não seja
odestinatário da mensagem, ou se ela lhe foi enviada por engano, agradecemos que não faça uso ou divulgação da mesma. A
distribuiçãoou utilização da informação nela contida é interdita. Se recebeu esta mensagem por engano, por favor
notifiqueo remetente e apague este e-mail do seu sistema. Obrigado. <br /></font></font></font><font
color="#7d7d7d"><fontface="Verdana, sans-serif"><font size="1" style="font-size: 7pt;"> </font></font></font><br
/><fontcolor="#7d7d7d"><font face="Verdana, sans-serif"><font size="1" style="font-size: 7pt;"> CONFIDENTIALITY
NOTICE</font></font></font><br/><font color="#7d7d7d"><font face="Verdana, sans-serif"><font size="1" style="font-size:
7pt;">This e-mail transmission and eventual attached files are intended only for the use of the individual(s) or
entity(ies)named above and may contain information that is both privileged and confidential and is exempt from
disclosureunder applicable law. If you are not the intended recipient, you are hereby notified that any disclosure,
copying,distribution or use of any of the information contained in this transmission is strictly restricted. If by any
meansyou have received this transmission in error, please immediately notify the sender and delete this e-mail from
yoursystem. Thank you. </font></font></font></div> 

Kris Jurka wrote:
> I'm not super impressed with these timing results.  They are certainly
> showing some effects due to GC, consider the rise in time here at 10.5MB.

The method isn't neccessarily much faster, especially when there are
only a few megabytes involved. This is very difficult to benchmark in
the presence of a garbage collector.

> I've committed this to CVS HEAD with a rather arbitrarily set
> MAX_3_BUFF_SIZE value of 2MB.  Note that this is also the escaped size, so
> we may actually be dealing with output data a quarter of that size.  If
> anyone could do some more testing of what a good crossover point would be
> that would be a good thing.

AFAIK the MAX_3_BUFF_SIZE entry was a debug artifact. Not needed any
more. The new method is always faster or at least as fast as the old
method, because it requires fewer memory accesses.

3 Buffers:

Buffer1 zeroing (vm intern)
Buffer1 filling

Buffer2 zeroing (vm intern)
Buffer1 reading
Buffer2 writing

Buffer3 zeroing (vm intern)
Buffer2 reading
Buffer3 writing

Total: 8 memory accesses.

Eventually Buffer3 reading, but that's not part of the driver.


2 Buffers:

Buffer1 zeroing (vm intern)
Buffer1 filling

Buffer1 reading (the new pass)

Buffer2 zeroing (vm intern)
Buffer1 reading
Buffer2 writing

Total: 6 memory accesses.


Conclusion: The new method uses less memory. It must be faster as well,
since everything else is fast in comparison to memory access.

Additionally, it requires only 2 allocations, and memory allocation have
some overhead as well, and mean more work for the garbage collector in
the end. Even if the VM can do some magic to avoid zeroing the buffers,
the newer method has one less memory access. It is always the winner.

Your explanation is very simple and correctly explains both methods.<br /><br /> Here are some more comments ...<br
/><br/> till toenges wrote: <blockquote cite="mid45192600.5050704@kyon.de" type="cite"><pre wrap="">Kris Jurka wrote:
</pre><blockquotetype="cite"><pre wrap="">I'm not super impressed with these timing results.  They are certainly 
 
showing some effects due to GC, consider the rise in time here at 10.5MB.   </pre></blockquote><pre wrap="">
The method isn't neccessarily much faster, especially when there are
only a few megabytes involved. This is very difficult to benchmark in
the presence of a garbage collector.
 </pre><blockquote type="cite"><pre wrap="">I've committed this to CVS HEAD with a rather arbitrarily set 
MAX_3_BUFF_SIZE value of 2MB.  Note that this is also the escaped size, so 
we may actually be dealing with output data a quarter of that size.  If 
anyone could do some more testing of what a good crossover point would be 
that would be a good thing.   </pre></blockquote><pre wrap="">
AFAIK the MAX_3_BUFF_SIZE entry was a debug artifact. Not needed any </pre></blockquote> It's almost correct, I would
likethe new code to be more tested before it fully replaces the old - in large arrays there's a big memory advantage,
soit makes sense to replace, in small array it's almost the same, so the old code can stay for a while ...<br
/><blockquotecite="mid45192600.5050704@kyon.de" type="cite"><pre wrap="">more. The new method is always faster or at
leastas fast as the old
 
method, because it requires fewer memory accesses.

3 Buffers:

Buffer1 zeroing (vm intern)
Buffer1 filling

Buffer2 zeroing (vm intern)
Buffer1 reading
Buffer2 writing

Buffer3 zeroing (vm intern)
Buffer2 reading
Buffer3 writing

Total: 8 memory accesses.

Eventually Buffer3 reading, but that's not part of the driver.


2 Buffers:

Buffer1 zeroing (vm intern)
Buffer1 filling

Buffer1 reading (the new pass)

Buffer2 zeroing (vm intern)
Buffer1 reading
Buffer2 writing

Total: 6 memory accesses.


Conclusion: The new method uses less memory. It must be faster as well,
since everything else is fast in comparison to memory access.

Additionally, it requires only 2 allocations, and memory allocation have
some overhead as well, and mean more work for the garbage collector in
the end. Even if the VM can do some magic to avoid zeroing the buffers,
the newer method has one less memory access. It is always the winner.
 </pre></blockquote> Not all memory accesses are created equal :-), the Buffer1 is the biggest buffer, and the new code
passone more time through it. The last copy from Buffer3 to Buffer2 in the old method is done through System.arraycopy,
whichI think is very, very fast (hardware based), so the methods are more balanced ...<br /> For large arrays the new
isALWAYS much faster off course - due to memory access.<br /><br /><blockquote cite="mid45192600.5050704@kyon.de"
type="cite"><prewrap="">---------------------------(end of broadcast)---------------------------
 
TIP 9: In versions below 8.0, the planner will ignore your desire to      choose an index scan if your joining column's
datatypesdo not      match
 
 </pre></blockquote><br /><br /><div class="moz-signature">-- <br /></div><p><font color="#7da647"><font face="Verdana,
sans-serif"><fontsize="2" style="font-size: 10pt;"> Luis Flores </font></font></font><p><font color="#7da647"><font
face="Verdana,sans-serif"><font size="2" style="font-size: 8pt;"> Analista de Sistemas</font></font></font><p><a
href="http://www.evolute.pt"><fontface="Verdana, sans-serif"><font size="2" style="font-size: 8pt;"><b>Evolute</b> -
ConsultoriaInformática<br /><br /></font></font></a> <font color="#7da647"><font face="Verdana, sans-serif"><font
size="2"style="font-size: 8pt;"> Email: </font></font></font> <a href="mailto:lflores@evolute.pt"><font face="Verdana,
sans-serif"><fontsize="2" style="font-size: 8pt;">lflores@evolute.pt </font></font></a><p><font color="#7da647"><font
face="Verdana,sans-serif"><font size="2" style="font-size: 8pt;"> Tel: (+351) 212949689</font></font></font><div
style="text-align:justify;"><font color="#7d7d7d"><font face="Verdana, sans-serif"><font size="1" style="font-size:
7pt;"><br/> AVISO DE CONFIDENCIALIDADE</font></font></font><br /><font color="#7d7d7d"><font face="Verdana,
sans-serif"><fontsize="1" style="font-size: 7pt;"> Esta mensagem de correio electrónico e eventuais ficheiros anexos
sãoconfidenciais e destinados apenas à(s) pessoa(s) ou entidade(s) acima referida(s), podendo conter informação
privilegiadae confidencial, a qual não poderá ser divulgada, copiada, gravada ou distribuída nos termos da lei vigente.
Casonão seja o destinatário da mensagem, ou se ela lhe foi enviada por engano, agradecemos que não faça uso ou
divulgaçãoda mesma. A distribuição ou utilização da informação nela contida é interdita. Se recebeu esta mensagem por
engano,por favor notifique o remetente e apague este e-mail do seu sistema. Obrigado. <br /></font></font></font><font
color="#7d7d7d"><fontface="Verdana, sans-serif"><font size="1" style="font-size: 7pt;"> </font></font></font><br
/><fontcolor="#7d7d7d"><font face="Verdana, sans-serif"><font size="1" style="font-size: 7pt;"> CONFIDENTIALITY
NOTICE</font></font></font><br/><font color="#7d7d7d"><font face="Verdana, sans-serif"><font size="1" style="font-size:
7pt;">This e-mail transmission and eventual attached files are intended only for the use of the individual(s) or
entity(ies)named above and may contain information that is both privileged and confidential and is exempt from
disclosureunder applicable law. If you are not the intended recipient, you are hereby notified that any disclosure,
copying,distribution or use of any of the information contained in this transmission is strictly restricted. If by any
meansyou have received this transmission in error, please immediately notify the sender and delete this e-mail from
yoursystem. Thank you. </font></font></font></div> 

Hello,

These are the results from the Kris Jurka's ByteaTest2.java.
If there are more questions or someone want something else about this
patch just ask it...

I look forward to see a new driver with this patch ...


CeleronM@1.6 1MB L2 cache
Fedora Core 5
jdk1.5.0_08
local db ver 8.1.4
jdbc driver ver 407

OLD
Time: 56261ms Mem: 2760776b
Time: 56077ms Mem: 2753088b
Time: 56181ms Mem: 2753088b
Time: 56184ms Mem: 2753088b
Time: 56259ms Mem: 2753088b

NEW
Time: 34603ms Mem: 1859656b
Time: 34438ms Mem: 1852776b
Time: 34409ms Mem: 1852776b
Time: 34610ms Mem: 1852776b
Time: 34496ms Mem: 1852776b


G5@1.8 512KB L2 cache
Mac OS X 10.4.8
jdk1.5.0_06
remote db ver 8.1.4(on LAN)
jdbc driver ver 407

OLD
Time: 130390ms Mem: 2812064b
Time: 131060ms Mem: 2811776b
Time: 131039ms Mem: 2811776b
Time: 131627ms Mem: 2811776b
Time: 131772ms Mem: 2811776b

NEW
Time: 83940ms Mem: 1911752b
Time: 83244ms Mem: 1911464b
Time: 83350ms Mem: 1911464b
Time: 83457ms Mem: 1911464b
Time: 83610ms Mem: 1911464b


import java.sql.*;

public class ByteaTest2 {

    public static void main(String args[]) throws Exception {
        Class.forName("org.postgresql.Driver");
        Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test_bytea","postgres","");

        for (int k=0; k<5; k++) {
            long t1 = System.currentTimeMillis();
            long total = 0;

            for (int j=0; j<10; j++) {
                PreparedStatement pstmt = conn.prepareStatement("SELECT varcharsend(repeat(?,?))");
                pstmt.setString(1, "a\\001");
                pstmt.setInt(2, 150000);
                ResultSet rs = pstmt.executeQuery();
                rs.next();
                for (int i=0; i<100; i++) {
                    byte b[] = rs.getBytes(1);
                    total += b.length;
                }

                rs.close();
                pstmt.close();
            }
            long t2 = System.currentTimeMillis();
            long usedMem = Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory();
            System.out.println("Time: " + ( t2-t1 ) + "ms Mem: " + usedMem + "b" );
        }
    }
}