Re: JDBC slow performance on resultSet iteration? - Mailing list pgsql-jdbc

Kevin,

Ya, however I am still questioning the basic time measurement. 

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Tue, Nov 5, 2013 at 12:39 PM, Kevin Wooten <kdubb@me.com> wrote:

Dave,

That is not necessarily true. Most of the drivers data conversion is done in the getXXX calls of the ResultSet.  All of which would count as “penalty”.  I’m not saying it’s a large penalty, just that this is time that must be considered since pgAdmin is converting and displaying the data as well.

On Nov 5, 2013, at 10:34 AM, Dave Cramer <pg@fastcrypt.com> wrote:

Rui Leal,

You shouldn't see that much of a penatly, but I question how you are measuring it. Since you are timing the loop which is using cursors.

Just run a simple query and time it without iterating through the result set. That would be equivalent 

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Tue, Nov 5, 2013 at 12:16 PM, Rui Pedro Leal <rui.pedro.leal@gmail.com> wrote:

Hello Dave, thanks for the quick reply.

I'm just executing the same query on pgAdmin query interface. Just a way to measure performance of the query + getting results vs the JDBC way.

I'm using fetchsize in order to get some faster results when iterating the resultSet. I've tried not to use fetchSize/cursor but the performance is naturally slower (and worser than the simple pgAdmin query).

What i'm asking is .. is this performance penalty i'm getting using postgres JDBC normal and within what's expected? I'm i doing something wrong? Can i do it in any other way?

Regards, 

Rui Leal

[] Rui Pedro Leal

[] rui.pedro.leal@gmail.com

On 2013/11/05, at 16:43, Dave Cramer wrote:

How can you measure the same query in pgAdmin ? JDBC will use a cursor when you set the fetchsize.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Tue, Nov 5, 2013 at 11:12 AM, Rui Pedro Leal <rui.pedro.leal@gmail.com> wrote:

Just an quick info update: i'm using the postgresql-9.2-1003-jdbc4 driver

On 2013/11/05, at 16:09, Rui Pedro Leal wrote:

> Hello everyone,
>
> I'm having slow JDBC performance on iterating a resultSet obtained from a somewhat simple query.
>
> The query, although using PostGIS functions, is pretty straight forward:
>
> SELECT _id, _spatial_type, ST_AsBinary(_geometry), _attribute, _count, _references, countpersons, countfatals, countdrunks, density
> FROM accidents_5
> WHERE (_geometry && ST_MakeEnvelope(-126.60644531250001,26.43122806450644,-63.369140625,52.96187505907603, 4326) )
> ORDER BY _pk_id
>
> The results are the following:
> - Executing the query directly from pgAdmin: ~2807ms
> - Executing from JVM + JDBC: 4184ms
>
> The code i'm executing is pretty much standard:
>
> -------------------- code --------------------
>
>       public static void main(final String[] args) throws Exception {
>
>               String sql = "SELECT _id, _spatial_type, ST_AsBinary(_geometry), _attribute, _count, _references, countpersons, countfatals, countdrunks, density";
>               sql += " FROM accidents_5";
>               sql += " WHERE (_geometry && ST_MakeEnvelope(-126.60644531250001,26.43122806450644,-63.369140625,52.96187505907603, 4326) )";
>               sql += " ORDER BY _pk_id";
>
>               System.out.println(sql);
>
>               Long time = System.currentTimeMillis();
>               try {
>                       Connection connection = DataStoreInfo.getDataStores().get(0);
>                       connection.setAutoCommit(false);
>
>                       System.out.println("[QUERY " + Thread.currentThread().getId()
>                                       + "] - connection in "
>                                       + (System.currentTimeMillis() - time) + "ms.");
>
>                       Statement st = connection.createStatement(
>                                       ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
>                                       ResultSet.CLOSE_CURSORS_AT_COMMIT);
>                       st.setFetchSize(250);
>
>                       System.out.println("[QUERY " + Thread.currentThread().getId()
>                                       + "] - statement in " + (System.currentTimeMillis() - time)
>                                       + "ms.");
>
>                       ResultSet resultSet = st.executeQuery(sql);
>
>                       System.out.println("[QUERY " + Thread.currentThread().getId()
>                                       + "] - done in " + (System.currentTimeMillis() - time)
>                                       + "ms.");
>
>                       GeometryFactory geofact = new GeometryFactory(new PrecisionModel(),4326);
>                       WKBReader wkbReader = new WKBReader(geofact);
>
>                       String id;
>                       String spatialType;
>                       Geometry geometry;
>                       String attribute;
>                       Long count;
>                       String reference;
>                       Map<String, Double> properties;
>                       String granularSynthString = "GranularSynthesis";
>
>                       while (resultSet.next()) {
>                               id = resultSet.getString(1);
>                               spatialType = resultSet.getString(2);
>
>                               // geometry = wkbReader.read(resultSet.getBytes(3)); // ignored the WKBReader and the results are about the same.
>                               attribute = resultSet.getString(4);
>                               count = resultSet.getLong(5);
>                               reference = resultSet.getString(6);
>
>                               properties = new HashMap<String, Double>();
>                               Double aux = resultSet.getDouble(7);
>                               properties.put("countpersons", aux);
>                               aux = resultSet.getDouble(8);
>                               properties.put("countfatals", aux);
>                               aux = resultSet.getDouble(9);
>                               properties.put("countdrunks", aux);
>                               aux = resultSet.getDouble(10);
>                               properties.put("density", aux);
>                       }
>
>                       System.out.println("[QUERY " + Thread.currentThread().getId()
>                                       + "] - done & iterated in "
>                                       + (System.currentTimeMillis() - time) + "ms.");
>
>                       resultSet.close();
>                       st.close();
>                       connection.commit();
>                       connection.close();
>               } catch (SQLException exception) {
>                       exception.printStackTrace();
>               // } catch (ParseException exception) {  // ignored from WKBreader
>               // exception.printStackTrace();
>               }
>
>               System.out.println("[End " + Thread.currentThread().getId()
>                               + "] - done in " + (System.currentTimeMillis() - time) + "ms.");
>       }
>
> -------------------- end code --------------------
>
> Although i'm executing this on slow MacbookPro (2.6 core duo, 2Gbs RAM but SSD) and have a 9.1.2 postgres, i've also tested this on a recent retina MBP and the ratio between pgAdmin and JDBC execution is similar.
>
> Is this expected? Can someone point if i'm doing something terrible wrong?
>
> I'm not concerned about the query performance per-se (i know it CAN be optimized), but the differences just using JDBC and iterating the resultSet are really annoying.
>
> Thanks in advance for any help.
>
> Kind regards,
>
> Rui Leal
>
>



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc