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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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


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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux