Re: bytea, jdbc, i/o ...

Kenneth,

The representation of the data is always converted to a string format in
both the local and remote cases.  But the string representation for
bytea data can result in upto four times expansion (i.e. a 1K bytea
value can take up to 4K in it's string form).  Thus using alot of bytea
data can result in significantly more network traffic for the same
amount of data.  I don't know if this explains your findings or not.

thanks,
--Barry

Kenneth Been wrote:

> I am seeing some strange behavior, and I have a guess for what is
> causing it.  Maybe someone will know for sure.
>
> Here is the situation.  Database A contains mostly data of type
> integer[], text, and boolean[].  Database B contains mostly data of
> type bytea.
>
> I wrote a program to send a bunch of select queries and time the
> results.  This test program is in Java, and connects using JDBC.  The
> data types in A are retrieved with the ResultSet.getString() method.
> That includes the arrays, which I then parse myself.  The bytea fields
> in B are retrieved with the ResultSet.getBytes() method, which returns
> byte[].
>
> I ran this test program on the same machine that has the database
> engine, and on a different machine on the local network.
>
> Here is the strange behavior:
>
> For database A, the difference in performance between testing locally
> and testing over the network is negligible; about 5% slower over the
> network.  About what I would expect.  But for database B, the
> difference is huge; about 35 times slower over the network.
>
> Here is my guess for what is going on:
>
> Maybe for a local connection the db engine delivers the bytea as is,
> whereas for a remote connection it converts the bytea to a string
> representation, and then Java would have to convert it back for the
> getBytes() method.  If the queries are usually answered from the
> cache, then I would think converting to string and back could take 35
> times longer than just pulling the data out of the cache.
>
> For the other data types, on the other hand, maybe the engine always
> converts them to string, whether it is local or not.  Or anyway, even
> if it doesn't, Java would convert to string for the getString() method.
>
> Does that sound right?  If not, any ideas on what is going on?
>
> Thanks.
>
> Ken
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>