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

Mon, 25 May 2009 03:28:59 -0700

I should mention that Cayenne always sets autocommit to false. Not sure if that matters. 

Andrus


On May 25, 2009, at 1:18 PM, Andrey Razumovsky wrote:


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 <[email protected]>
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.

Reply via email to