Re: Big selects on PostGres : Configuring Statement.setFetchSize() of a selectquery

From: Andrey Razumovsky (razumovsky.andre..mail.com)
Date: Mon May 25 2009 - 06:18:21 EDT

  • Next message: Andrus Adamchik: "Re: Big selects on PostGres : Configuring Statement.setFetchSize() of a selectquery"

    Well, I tested only with pure JDBC, maybe Cayenne sets some enviroment
    correctly. It'd be great if you tested committed code. You can get compiled
    binaries from Hudson:
    http://hudson.zones.apache.org/hudson/job/Cayenne-trunk/403/

    2009/5/25 stefcl <stefatwor..mail.com>

    >
    > Here's the code I'm currently using, it's based on the v3 with generics
    > version I got from the repo a few days earlier :
    >
    > DataContext context = DataContext.createDataContext();
    >
    > SelectQuery query = new SelectQuery(ItemDetail.class);
    >
    > //query.andQualifier( ExpressionFactory.matchDbExp(
    > ItemDetail.PROFILEID_PK_COLUMN, 10 ) );
    > //query.andQualifier( ExpressionFactory.likeExp(
    > ItemDetail.NAME_PROPERTY, "G%")) ;
    > query.addOrdering(ItemDetail.NAME_PROPERTY, Ordering.ASC );
    >
    > query.setCursorFetchSize( 1000 );
    >
    > ResultIterator iterator = context.performIteratedQuery(query);
    >
    > while( iterator.hasNextRow())
    > {
    > ItemDetail detail = (ItemDetail)context.objectFromDataRow(
    > ItemDetail.class, (DataRow)iterator.nextRow(), false);
    > System.out.println( detail.getName() + "\t\t\t\t" +
    > detail.getKeywords() );
    > }
    >
    > It works...
    > If I remove the fetchSize parameter, it fails with following stackTrace :
    >
    > Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
    > at java.lang.Class.getDeclaredFields0(Native Method)
    > at java.lang.Class.privateGetDeclaredFields(Class.java:2291)
    > at java.lang.Class.getDeclaredField(Class.java:1880)
    > at
    >
    > java.util.concurrent.atomic.AtomicReferenceFieldUpdater$AtomicReferenceFieldUpdaterImpl.<init>(AtomicReferenceFieldUpdater.java:181)
    > at
    >
    > java.util.concurrent.atomic.AtomicReferenceFieldUpdater.newUpdater(AtomicReferenceFieldUpdater.java:65)
    > at java.sql.SQLException.<clinit>(SQLException.java:353)
    > at
    >
    > org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1325)
    > at
    >
    > org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:194)
    > at
    >
    > org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
    > at
    >
    > org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
    > at
    >
    > org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
    > at
    >
    > org.apache.cayenne.access.jdbc.SelectAction.performAction(SelectAction.java:78)
    > at
    >
    > org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:87)
    > at
    > org.apache.cayenne.access.DataNode.performQueries(DataNode.java:274)
    > at
    >
    > org.apache.cayenne.access.DataDomainLegacyQueryAction.execute(DataDomainLegacyQueryAction.java:79)
    > at
    > org.apache.cayenne.access.DataDomain$1.transform(DataDomain.java:713)
    > at
    > org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:833)
    > at
    > org.apache.cayenne.access.DataDomain.performQueries(DataDomain.java:710)
    > at
    >
    > org.apache.cayenne.access.DataContext.internalPerformIteratedQuery(DataContext.java:1213)
    > at
    >
    > org.apache.cayenne.access.DataContext.performIteratedQuery(DataContext.java:1179)
    > at cayenne.CayenneMain.main(CayenneMain.java:43)
    >
    >
    > I'm surprised that you get different results... I use the lastest 8.3.604
    > driver from jdbc.
    > Otherwise, looking at cayenne logs it seems that a transaction is being
    > started :
    >
    > INFO: --- will run 1 query.
    > 25 mai 2009 11:58:35 org.apache.cayenne.access.QueryLogger logConnect
    > INFO: Opening connection: jdbc:postgresql://localhost/prediggo_db
    > Login: postgres
    > Password: *******
    > 25 mai 2009 11:58:35 org.apache.cayenne.access.QueryLogger
    > logConnectSuccess
    > INFO: +++ Connecting: SUCCESS.
    > 25 mai 2009 11:58:35 org.apache.cayenne.access.QueryLogger
    > logBeginTransaction
    > INFO: --- transaction started.
    > 25 mai 2009 11:58:36 org.apache.cayenne.access.QueryLogger log
    > INFO: Detected and installed adapter:
    > org.apache.cayenne.dba.postgres.PostgresAdapter
    > 25 mai 2009 11:58:36 org.apache.cayenne.access.QueryLogger logQuery
    > INFO: SELECT t0.timest, t0.keywords,
    >
    >
    > If you want, I can test your implementation with my test case but I can't
    > afford to spend much time setting up the build environment (jdk 1.5 + maven
    > stuff)... If I could get compiled binaries somewhere...
    >
    >
    >
    > Andrey Razumovsky wrote:
    > >
    > > I'm using latest postgres (or mysql) driver. To make a test, I put 1M
    > > records in db table, then do "select * from that_table". No matter which
    > > fetch size I tried to set, at the moment of PreparedStastement.execute()
    > > something about 70-100Mb are allocated (I don't actually remember the
    > > quantity right now). So, if heap size is small, I get OutOfMemory.
    > > Otherwise, it's all ok and rows can be quickly iterated. Looking at code
    > > on
    > > postgres site, the only difference is that I didn't bother with
    > autocommit
    > > mode. I'm not sure Cayenne does, too... Maybe this is the reason why I
    > > didn't get positive results.
    > >
    > > Andrey
    > >
    >
    > --
    > View this message in context:
    > http://www.nabble.com/Fwd%3A-Big-selects-on-PostGres-%3A-Configuring-Statement.setFetchSize%28%29-of--a-selectquery-tp23703746p23704583.html
    > Sent from the Cayenne - Dev mailing list archive at Nabble.com.
    >
    >



    This archive was generated by hypermail 2.0.0 : Mon May 25 2009 - 06:19:05 EDT