cayenne-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Valery Barysok (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (CAY-2014) Ensure SelectQuery.setFetchSize() actually takes affect on MySQL
Date Mon, 15 Jun 2015 13:25:01 GMT

    [ https://issues.apache.org/jira/browse/CAY-2014?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14585914#comment-14585914
] 

Valery Barysok commented on CAY-2014:
-------------------------------------

SelectQuery contains setStatementFetchSize method instead of setFetchSize.

Required common jdbc conditions:
1) The Connection must not be in autocommit mode (Cursor will be closed on the end of transaction)
2) A ResultSet object must be not updatable and has a cursor that moves forward only ([it
is default by specification|http://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html])
3) Provide fetch size through Statement.setFetchSize or ResultSet.setFetchSize

Database specific conditions:
   MySQL:
       1) useCursorFetch must be true ([Client and server cursors in MySQL|http://wiki.genexus.com/commwiki/servlet/hwiki?Client+and+server+cursors+-+using+MySQL])
           through jdbc url:
{code:java}
"jdbc:mysql://localhost:3306/test?useCursorFetch=true";
{code}

           through properties:
{code:java}
Properties info = new Properties();
info.setProperty("useCursorFetch", "true");
// ...
Connection conn = DriverManager.getConnection(getJdbcUrl(), info);
{code}
   PostgreSQL:
       1) see [Getting results based on a cursor|https://jdbc.postgresql.org/documentation/head/query.html]

Next code was used to validate correct work of fetch size in the Cayenne ORM for MySQL and
PostgreSQL:

{code:java}
private static void testFetchSize(ObjectContext context) {
    SelectQuery<ManyRowsEntity> select = SelectQuery.query(ManyRowsEntity.class);
    select.setStatementFetchSize(2);
    ResultIterator<ManyRowsEntity> it = context.iterator(select);
    while (it.hasNextRow()) {
        // do some with
        System.out.println(it.nextRow());
    }
}
{code}

For MySQL you need to debug {code:java}com.mysql.jdbc.RowDataCursor.fetchMoreRows{code} method.
For PostgreSQL you need to debug {code:java}org.postgresql.core.v3.QueryExecutorImpl.fetch{code}
method.


> Ensure SelectQuery.setFetchSize() actually takes affect on MySQL
> ----------------------------------------------------------------
>
>                 Key: CAY-2014
>                 URL: https://issues.apache.org/jira/browse/CAY-2014
>             Project: Cayenne
>          Issue Type: Improvement
>            Reporter: Andrus Adamchik
>            Assignee: Andrus Adamchik
>
> We supported SelectQuery.setFetchSize() since times immemorial, however it doesn't do
much on MySQL. According to [1] MySQL still reads the entire ResultSet in memory. So whenever
a query has fetch size set, we need to ensure that MySQL adapter creates PreparedStatement
using the following parameters (also from [1]) :
> {noformat}stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
>               java.sql.ResultSet.CONCUR_READ_ONLY);
> stmt.setFetchSize(Integer.MIN_VALUE);
> {noformat}
> For a bonus point, we'll need to research whether other DBs (PostgreSQL, Oracle, SQLServer)
actually support fetch size or whether they require special handling like MySQL.
> [1] http://dev.mysql.com/doc/connector-j/en/connector-j-reference-implementation-notes.html



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message