Sure. Hopefully within a couple of weeks 3.0 becomes Release Candidate
and final after that, so you may give it another look.
For now I guess the only way is to write a custom query extending
SQLTemplate and overriding 'createSQLAction' to call 'setFetchSize' on
the JDBC statement. Implementing a custom SQLAction can be pretty
involved, however if you are doing a one-off thing, it is doable.
Also I would recommend to switch to Cayenne 3.0 at least temporarily
to confirm that the fix I described is indeed working for you. There
may be some other things at play here. Who knows.
Andrus
On Dec 24, 2009, at 10:48 AM, Emanuele Maiarelli wrote:
I'm trying to figureout a solution using 2.0.4.
I don't feel confortable in switching to 3.0 at this stage of the
project.
Thank you,
Emanuele
Andrus Adamchik ha scritto:
This is likely an issue with MySQL driver caching returned data.
The solution was implemented in Cayenne 3.0 (which is now in beta):
query.setStatementFetchSize(1000);
This propagates the fetch size setting to the JDBC statement,
reducing the driver memory footprint.
Andrus
On Dec 24, 2009, at 9:43 AM, Emanuele Maiarelli wrote:
im using cayenne 2.0.4, and i need to fetch an huge ammout of
data, i did that way:
public static StringBuffer traceProts;
static {
traceProts = new StringBuffer();
traceProts.append("SELECT ");
traceProts.append("PROTOCOLLI.* ");
traceProts.append("FROM PROTOCOLLI, ");
traceProts.append("( ");
traceProts.append(" SELECT ");
traceProts.append(" FORM_PROT_FK ");
traceProts.append(" FROM ");
traceProts.append(" ( ");
traceProts.append(" SELECT ");
traceProts.append(" FORM_PROT_FK,SUM(IF(ME_PK is not
null,1,0)) AS ESPORTAZIONI ");
traceProts.append(" FROM FORMULARI ");
traceProts.append(" LEFT JOIN MOV_EXPORTED ON
(FORM_PK=ME_FORMULARI_FK) ");
traceProts.append(" GROUP BY FORM_PROT_FK ");
traceProts.append(" ) ");
traceProts.append(" EXPS ");
traceProts.append(" WHERE ESPORTAZIONI=0 ");
traceProts.append(") ");
traceProts.append("UNEXP ");
traceProts.append("where PROT_PK=UNEXP.FORM_PROT_FK");
}
this query returns an huge ammont of row,
and im fetching row using IteratedQuery, that's the code im using
StringBuffer mq=new StringBuffer();
Vector<EXPProtocolli> toRet=new Vector<EXPProtocolli>();
mq.append(traceProts);
mq.append(" AND PROT_CHIUSO=true AND PROT_RIPARTITO=true");
SQLTemplate rawSelect = new SQLTemplate(Protocolli.class,
mq.toString());
ResultIterator v=ctx.performIteratedQuery(rawSelect);
while (v.hasNextRow())
{
EXPProtocolli prt=new EXPProtocolli();
DataRow dataRow = (DataRow) v.nextDataRow();
Protocolli p=(Protocolli)
ctx.objectFromDataRow(Protocolli.class, dataRow,false);
this.bindEXPProtMsg(p,prt);
prt.setStato(ProtStato.PROT_CARICO_ESPORTABILE);
toRet.add(prt);
}
return toRet;
when running this method i always get an heap space problem
java.lang.OutOfMemoryError: Java heap space
at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:1621)
at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1398)
at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2816)
at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:467)
at
com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:
2510)
at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1746)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2135)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:
2542)
at
com
.mysql
.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
at
com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:995)
at
org
.apache
.cayenne
.access.jdbc.SQLTemplateAction.execute(SQLTemplateAction.java:135)
at
org
.apache
.cayenne
.access
.jdbc.SQLTemplateAction.performAction(SQLTemplateAction.java:107)
at
org
.apache
.cayenne
.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:59)
at
org.apache.cayenne.access.DataNode.performQueries(DataNode.java:273)
at
org
.apache
.cayenne
.access
.DataDomainLegacyQueryAction
.execute(DataDomainLegacyQueryAction.java:81)
at org.apache.cayenne.access.DataDomain
$1.transform(DataDomain.java:725)
at
org
.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:
826)
at
org
.apache.cayenne.access.DataDomain.performQueries(DataDomain.java:
722)
at
org
.apache
.cayenne
.access.DataContext.internalPerformIteratedQuery(DataContext.java:
1329)
at
org
.apache
.cayenne.access.DataContext.performIteratedQuery(DataContext.java:
1295)
at
services.EXP.Factory.getProtocolliCaricoNonEsp(Factory.java:85)
at services.EXP.EXPServicePBE.getData(EXPServicePBE.java:54)
at services.EXPExporter.processRequest(EXPExporter.java:52)
....
the error is generated by: ResultIterator
v=ctx.performIteratedQuery(rawSelect);
any hints?
