Thread: 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
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
Re: how to monitor the amount of bytes fetched in a executeQuery()
From
"Nicholas E. Wakefield"
Date:
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@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Oliver Jowett Sent: Tuesday, July 11, 2006 10:11 PM To: Albert Cardona Cc: pgsql-jdbc@postgresql.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
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
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
> 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