Skip site navigation (1) Skip section navigation (2)

Re: SOLVED ... Re: Getting rid of a cursor from JDBC .... Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set

From: Kris Jurka <books(at)ejurka(dot)com>
To: Dave Crooke <dcrooke(at)gmail(dot)com>
Cc: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, pgsql-jdbc(at)postgresql(dot)org, pgsql-performance <pgsql-performance(at)postgresql(dot)org>, Scott Carey <scott(at)richrelevance(dot)com>
Subject: Re: SOLVED ... Re: Getting rid of a cursor from JDBC .... Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set
Date: 2010-04-20 21:05:54
Message-ID: alpine.BSO.2.00.1004201644280.6736@leary.csoft.net (view raw or flat)
Thread:
Lists: pgsql-jdbcpgsql-performance

On Tue, 20 Apr 2010, Dave Crooke wrote:

> a. Make setFetchSize(10000) the default

The reason this is not done is that the mechanism used for fetching a 
piece of the results at a time can change the query plan used if using a 
PreparedStatement.  There are three ways to plan a PreparedStatement:

a) Using the exact parameter values by substituting them directly into the 
query.  This isn't really "planned" as you can't re-use it at all.  This 
is only available using the V2 protocol.

b) Using the parameter values for statistics, but not making any stronger
guarantees about them.  So the parameters will be used for evaluating the 
selectivity, but not to perform other optimizations like 
contraint_exclusion or transforming a LIKE operation to a range query. 
This is the default plan type the JDBC driver uses.

c) Planning the query with no regard for the parameters passed to it. 
This is the plan type the JDBC driver uses when it sees the same 
PreparedStatement being re-used multiple times or when it is respecting 
setFetchSize and allowing for partial results.

We must use (c) for partial results instead of (b) because of some 
limitations of the server.  Currently you cannot have two statements of 
type (b) open on the same connection.  So since the driver can't know if 
the user will issue another query before fetching the remainder of the 
first query's results, it must setup the first query to be of type (c) so 
that multiple statements can exist simultaneously.

Switching the default plan type to (c) will cause a significant number of 
complaints as performance on some queries will go into the tank.  Perhaps 
we could have a default fetchSize for plain Statements as it won't affect 
the plan.  I could also see making this a URL parameter though so it could 
be set as the default with only a configuration, not a code change.

> b. If someone does call rs.close() before the end of the ResultSet, and has
> not created an explicit cursor at the JDBC level, flag the query / lock /
> virtual transaction in some way in the JDBC driver that tells it that it can
> just dump the cursor on a subsequent stmt.close(), conn.commit() or
> conn.close() call without sucking down the rest of the data.

This is already true.  The JDBC driver only asks the server for more of 
the ResultSet when a next() call requires it.  So the server isn't 
constantly spewing out rows that the driver must deal with, the driver 
only gets the rows it asks for.  Once the ResultSet is closed, it won't 
ask for any more.

Kris Jurka


In response to

Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2010-04-20 21:57:11
Subject: Re: significant slow down with various LIMIT
Previous:From: Dave CrookeDate: 2010-04-20 20:40:14
Subject: Re: SOLVED ... Re: Getting rid of a cursor from JDBC .... Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set

pgsql-jdbc by date

Next:From: Shivender DevarakondaDate: 2010-04-21 00:11:50
Subject: Authentication failed when Password contains Japaneese Charecters
Previous:From: Dave CrookeDate: 2010-04-20 20:40:14
Subject: Re: SOLVED ... Re: Getting rid of a cursor from JDBC .... Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group