Обсуждение: Binary protocol support for JDBC
Hello, I partially, and for "test", implemented retrieval data in binary mode (instead of text mode) for some Jdbc2 types, because I see great performance boost (25% - 50%) on implemented types I think about including this work to main JDBC branch. This should be done without problem, because I added binary parameter to Connection and Datasources, so user can decide to use binary mode retrieve or current text mode (default). Currently I implemented retrieve of short, int, long, date and BigDecimal. Other simple and basic types, used in typically application I will implement shortly. If you think that this could be interested patch, I will send it. Kind regards, Radosław Smogura
On Tue, 20 Jul 2010, Rados?aw Smogura wrote: > I partially, and for "test", implemented retrieval data in binary mode > (instead of text mode) for some Jdbc2 types, because I see great performance > boost (25% - 50%) on implemented types I think about including this work to > main JDBC branch. Are you aware of the existing work in this area? http://wiki.postgresql.org/wiki/JDBC-BinaryTransfer > This should be done without problem, because I added binary parameter to > Connection and Datasources, so user can decide to use binary mode > retrieve or current text mode (default). Currently I implemented > retrieve of short, int, long, date and BigDecimal. Other simple and > basic types, used in typically application I will implement shortly. > One of the difficulties in the existing patch is knowing when to request binary transfer and when to request text transfer because for the first execution the datatypes are not known. How have you addressed this problem? Kris Jurka
I searched something about this, but I culdn't find :)
It looks like this what I've done, so I will only send BigDeciaml read code.
Below :) some Sysout trashes left, but works, I've tested
public BigDecimal getBigDecimal(int columnIndex, int scale) throws
SQLException
{
checkResultSet(columnIndex);
if (wasNullFlag)
return null;
final int column = columnIndex - 1;
if (fields[column].getFormat() == Field.BINARY_FORMAT) {
//TODO Extract this do getBinaryBigDeciaml to support NaN
if (fields[column].getOID() != Oid.NUMERIC)
throw new PSQLException("Conversion in binary form not fully
implemented yet.", PSQLState.NOT_IMPLEMENTED);
byte[] number = this_row[column];
short ndigits = (short) (((number[0] & 0xff) << 8) | (number[1] &
0xff));
short weight = (short) (((number[2] & 0xff) << 8) | (number[3] &
0xff));
short sign = (short) (((number[4] & 0xff) << 8) | (number[5] &
0xff));
short dscale = (short) (((number[6] & 0xff) << 8) | (number[7] &
0xff));
if (sign == (short) 0xC000) {
//Numeric NaN - BigDecimal doesn't support this
throw new PSQLException("The numeric value is NaN - can't
convert to BigDecimal",
PSQLState.NUMERIC_VALUE_OUT_OF_RANGE);
}
final int bigDecimalSign = sign == 0x4000 ? -1 : 1;
// System.out.println("ndigits=" + ndigits
// +",\n wieght=" + weight
// +",\n sign=" + sign
// +",\n dscale=" + dscale);
//// for (int i=8; i < number.length; i++) {
// System.out.println("numer[i]=" + (int) (number[i] & 0xff));
// }
int tail = ndigits % 4;
int bytesToParse = (ndigits - tail) * 2 + 8;
// System.out.println("numberParseLength="+numberParseLength);
int i;
BigInteger unscaledValue = BigInteger.ZERO;
final BigInteger nbase = getNBase();
final BigInteger nbasePow2 = getNBasePow2();
final BigInteger nbasePow4 = getNBasePow4();
final long nbaseLong = AbstractJdbc2ResultSet.nbaseLong;
final long nbaseLongPow2 = AbstractJdbc2ResultSet.nbaseLongPow2;
final int nbaseInt = (int) AbstractJdbc2ResultSet.nbaseInt;
//final long nbasePow2Long = nbaseLong * nbaseLong;
byte[] buffer = new byte[8];
// System.out.println("tail = " + tail + " bytesToParse = " +
bytesToParse);
for (i=8; i < bytesToParse; i+=8) {
//This Hi and Lo aren't bytes Hi Li, but decimal Hi Lo!!! (Big
& Small)
long valHi = (((number[i] & 0xff) << 8) | (number[i+1] & 0xff))
* 10000
+ (((number[i+2] & 0xff) << 8) | (number[i+3] & 0xff));
long valLo = (((number[i+4] & 0xff) << 8) | (number[i+5] &
0xff)) * 10000
+ (((number[i+6] & 0xff) << 8) | (number[i+7] & 0xff));
long val = valHi * nbaseLongPow2 + valLo;
buffer[0] = (byte)(val >>> 56);
buffer[1] = (byte)(val >>> 48);
buffer[2] = (byte)(val >>> 40);
buffer[3] = (byte)(val >>> 32);
buffer[4] = (byte)(val >>> 24);
buffer[5] = (byte)(val >>> 16);
buffer[6] = (byte)(val >>> 8);
buffer[7] = (byte)(val >>> 0);
BigInteger valBigInteger = new BigInteger(bigDecimalSign,
buffer);
unscaledValue =
unscaledValue.multiply(nbasePow4).add(valBigInteger);
// System.out.println("Value (8) = " + val + ", unscaled =" +
unscaledValue
// +", valBI = "+ valBigInteger);
}
tail = tail % 2;
bytesToParse = (ndigits - tail) * 2 + 8;
//System.out.println("tail = " + tail + " bytesToParse = " +
bytesToParse);
buffer = new byte[4];
for (;i < bytesToParse; i+=4) {
int val = (((number[i] & 0xff) << 8) | (number[i+1] & 0xff)) *
nbaseInt
+ (((number[i+2] & 0xff) << 8) | (number[i+3] & 0xff));
buffer[0] = (byte)(val >>> 24);
buffer[1] = (byte)(val >>> 16);
buffer[2] = (byte)(val >>> 8);
buffer[3] = (byte)val;
BigInteger valBigInteger = new BigInteger(bigDecimalSign,
buffer);
unscaledValue =
unscaledValue.multiply(nbasePow2).add(valBigInteger);
// System.out.println("Value (4) = " + val + ", unscaled =" +
unscaledValue
// +", valBI = "+ valBigInteger);
}
//Add the rest of number
//System.out.println("tail = " + tail + " bytesToParse = " +
bytesToParse);
if (tail % 2 == 1){
buffer = new byte[2];
buffer[0] = number[number.length - 2];
buffer[1] = number[number.length - 1];
BigInteger valBigInteger = new BigInteger(buffer);
unscaledValue =
unscaledValue.multiply(nbase).add(valBigInteger);
// System.out.println("Value (2) unscaled =" + unscaledValue
// +", valBI = "+ valBigInteger);
}
//System.out.println("Final unscaled value " + unscaledValue);
//if (sign == 0x4000)
// unscaledValue = unscaledValue.negate();
//Calculate scale offset
final int databaseScale = (ndigits - weight - 1)*4; // Number of
digits in nabse
//TODO This number of digits should be calculeted depending on
nbase (getNbase());
BigDecimal result = new BigDecimal(unscaledValue, databaseScale);
//System.out.println("Final result " + result);
if (scale == -1)
return result;
else
return result.setScale(scale);
}else {
Encoding encoding = connection.getEncoding();
if (encoding.hasAsciiNumbers()) {
try {
return getFastBigDecimal(columnIndex);
} catch (NumberFormatException ex) {
}
}
return toBigDecimal( getFixedString(columnIndex), scale );
}
}
> On Tue, 20 Jul 2010, Rados?aw Smogura wrote:
> > I partially, and for "test", implemented retrieval data in binary mode
> > (instead of text mode) for some Jdbc2 types, because I see great
> > performance boost (25% - 50%) on implemented types I think about
> > including this work to main JDBC branch.
>
> Are you aware of the existing work in this area?
>
> http://wiki.postgresql.org/wiki/JDBC-BinaryTransfer
>
> > This should be done without problem, because I added binary parameter to
> > Connection and Datasources, so user can decide to use binary mode
> > retrieve or current text mode (default). Currently I implemented
> > retrieve of short, int, long, date and BigDecimal. Other simple and
> > basic types, used in typically application I will implement shortly.
>
> One of the difficulties in the existing patch is knowing when to request
> binary transfer and when to request text transfer because for the first
> execution the datatypes are not known. How have you addressed
> this problem?
>
> Kris Jurka
And I forgot about some AbstractJdbc2ResultSet constants used for cache
important values, I haven't checked this nbase, but it's looks like it's
constant in Postgres.
private static final BigInteger _nbase = new BigInteger("10000");
private static final BigInteger _nbasePow2 = _nbase.pow(2);
private static final BigInteger _nbasePow4 = _nbase.pow(4);
private static final long nbaseLong = _nbase.longValue();
private static final long nbaseLongPow2 = nbaseLong * nbaseLong;
private static final int nbaseInt = (int) nbaseLong;
protected BigInteger getNBase() {
return _nbase;
}
protected BigInteger getNBasePow2() {
return _nbasePow2;
}
protected BigInteger getNBasePow4() {
return _nbasePow4;
}
> > This should be done without problem, because I added binary parameter to
> > Connection and Datasources, so user can decide to use binary mode
> > retrieve or current text mode (default). Currently I implemented
> > retrieve of short, int, long, date and BigDecimal. Other simple and
> > basic types, used in typically application I will implement shortly.
>
> One of the difficulties in the existing patch is knowing when to request
> binary transfer and when to request text transfer because for the first
> execution the datatypes are not known. How have you addressed
> this problem?
Hm... I've done only binary retrieve. I can think wrong, but I probably read
and I didn't inspected this, but after you bind statement you can't change
mode (binary / transfer) of parameters, so _probably_ in prepared statement
will be inpossible to call setInt(1, val), and on 2nd call setString(1,
stringVal) - not sure.
About requesting binary transfer I assumed this
1) user sets binary for it's own responsibility (he must test application, and
there is nothing special in this, because PG supporrts too many and special
types, eg. inet address), so for example assuming that "scientific user" will
be able to use it's own types may be wrong.
2) i request all result parameters to be in binary form, i decided to write
parsers for all supported types :) with following code
if (isBinary()) {
pgStream.SendInteger2(1);
pgStream.SendInteger2(1);
}else{
pgStream.SendInteger2(0);
}
///Orignal code from QueryExecutorImpl.sendBind
pendingBindQueue.add(portal);
if (bindException != null)
{
throw bindException;
}
so yours way is better.
Regards,
Radek
Hello,
At the begining I would like to ask if those patches, from can be applied to
trunk sources from CVS, because I want to build complete version.
In the second part of message I wuould like to correct a little a method for
BigDecimal I posted before. There, I don't know way was a small mistake in
last "tail" if. In particullary sign number disappered should be
BigInteger(bigDecimalSign, buffer);, and complete version is.
if (tail % 2 == 1) {
buffer = new byte[2];
buffer[0] = number[number.length - 2];
buffer[1] = number[number.length - 1];
BigInteger valBigInteger = new BigInteger(bigDecimalSign, buffer);
unscaledValue = unscaledValue.multiply(nbase).add(valBigInteger);
// System.out.println("Value (2) unscaled =" + unscaledValue
// +", valBI = "+ valBigInteger);
}
I know that this version can work for version 8 of PostgreSQL, but
unfortunalty I don't have sources to check for 7.x versions. I don't know way
how to get valid nbase for given version, so I use nbase encoded in sources
(this nbase doesnt depend on compile parameters). Maybe it isn't problem as
version 9.0 is incoming.
Regards,
Radek