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(at)postgresql(dot)org
>