Re: how to monitor the amount of bytes fetched in a executeQuery()

Hi all,

I have a system in which large (13Mb) images are stored in the database as
compressed bytea column entries. When fetching from the local computer it's
fast enough the lag is not noticeable. When fetching remotely at 1Mb LAN
speed, about 15 seconds elapse.

After timing the executeQuery() and the getBinaryStream(), the first takes
about 15 seconds and the second about 3. So it looks like the executeQuery()
is actually downloading the image, and the getBinaryStream is merely copying
it from a local resource. Is that right?

Is there any way in which the number of bytes fetched in a query or for a
particular column can be monitored, so I can display a more accurate and
elaborated waiting dialog in my application?

Thanks for any hints.

Albert

--
Albert Cardona
Molecular Cell Developmental Biology
University of California Los Angeles
Tel +1 310 2067376
Programming: http://www.ini.unizch.ch/~acardona/trakem2.html
Research: http://www.mcdb.ucla.edu/Research/Hartenstein/
Web design: http://www.pixelets.com

You could possibly modify the driver to start a thread in the background
to monitor the progress - hack. I just did some very similar to monitor
the amount of memory being used by a result set as it was being
generated.

-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Oliver Jowett
Sent: Tuesday, July 11, 2006 10:11 PM
To: Albert Cardona
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] how to monitor the amount of bytes fetched in a
executeQuery()

Albert Cardona wrote:

> I have a system in which large (13Mb) images are stored in the
database as
> compressed bytea column entries. When fetching from the local computer
it's
> fast enough the lag is not noticeable. When fetching remotely at 1Mb
LAN
> speed, about 15 seconds elapse.
>
> After timing the executeQuery() and the getBinaryStream(), the first
takes
> about 15 seconds and the second about 3. So it looks like the
executeQuery()
> is actually downloading the image, and the getBinaryStream is merely
copying
> it from a local resource. Is that right?

Yes.

> Is there any way in which the number of bytes fetched in a query or
for a
> particular column can be monitored, so I can display a more accurate
and
> elaborated waiting dialog in my application?

I can't see any way to do this, unfortunately.

-O

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Albert Cardona wrote:

> I have a system in which large (13Mb) images are stored in the database as
> compressed bytea column entries. When fetching from the local computer it's
> fast enough the lag is not noticeable. When fetching remotely at 1Mb LAN
> speed, about 15 seconds elapse.
>
> After timing the executeQuery() and the getBinaryStream(), the first takes
> about 15 seconds and the second about 3. So it looks like the executeQuery()
> is actually downloading the image, and the getBinaryStream is merely copying
> it from a local resource. Is that right?

Yes.

> Is there any way in which the number of bytes fetched in a query or for a
> particular column can be monitored, so I can display a more accurate and
> elaborated waiting dialog in my application?

I can't see any way to do this, unfortunately.

-O

Hum, it would be infinitely more convenient, for deployment purposes, not to
touch the driver at all. What I can do maybe is to hack the driver with
reflection, since private fields can be accessed as well (except in applets).

Since you've done it before, would you mind sharing the caveats you went
through, general warnings and also the particular class where the receiving
InputStream for the ResultSet is? That would save a lot of time!

Also, the ability to monitor the amount of bytes loaded would be an nice
addition to the driver itself. Perhaps a small Monitor class that extends
Thread, which monitors the receiving InputStream and provides a single
getLoadedBytes() method?

Thanks for your insight Nicholas.

Albert

Albert Cardona wrote:

> Also, the ability to monitor the amount of bytes loaded would be an nice
> addition to the driver itself. Perhaps a small Monitor class that extends
> Thread, which monitors the receiving InputStream and provides a single
> getLoadedBytes() method?

Well, there's only one InputStream per connection really.. If a
bytecount from there is useful it wouldn't be too hard to expose
(perhaps on PGconnection), but it'd only be approximate because that one
stream is used for all activity on the connection including other
columns, protocol overhead, etc.

-O

> Well, there's only one InputStream per connection really.. If a
> bytecount from there is useful it wouldn't be too hard to expose
> (perhaps on PGconnection), but it'd only be approximate because that one
> stream is used for all activity on the connection including other
> columns, protocol overhead, etc.

That would definitely work for me, since data is orders of magnitude larger
than any overheads. In any case it would serve the purpose of showing that
the connection is not stuck, that bytes are flowing in even if -on occasions-
slowly. One could even use it to calculate connection/download/upload speed
and thus provide very nice information to the impatient user.

I will have a look at the code myself but I'm not familiar with it -you say
it's at PGconnection class?

Albert

Albert Cardona wrote:

> I will have a look at the code myself but I'm not familiar with it -you say
> it's at PGconnection class?

org.postgresql.PGConnection is our extension interface that applications
use to get access to driver-specific features. The actual implementation
would go in AbstractJdbc2Connection which would delegate to the
protocol-level code in org/postgresql/core/* to get stats from the
underlying connection. Looks like you may need to play with PGStream so
it actually tracks the byte count, unless one of the wrapping streams
already does that.

app code would look something like this:

Connection c = /* get connection */;
if (c instanceof PGConnection) {
PGConnection pgc = (PGConnection)c;
long byteCount = pgc.getInputByteCount(); // or whatever
}

-O