To detect that the problem comes from TYPE_SCROLL... choice at create statement time, I have created a batch having the following job.xml:
<bean id="step1" parent="simpleStep">
<property name="commitInterval" value="20000" />
<property name="saveRestartData" value="false" />
<property name="allowStartIfComplete" value="true"/>
<property name="tasklet">
<bean class="org.springframework.batch.execution.tasklet.Restar tableItemProviderTasklet">
<property name="itemProvider" ref="DBItemProvider" />
<property name="itemProcessor" ref="compositeItemProcessor"/>
</bean>
DBItemProvider:
<bean id="DBItemProvider" class="mypackage.VersementItemProvider">
<property name="dataSource" ref="dataSource" />
<property name="mapper">
<bean class="mypackage.VersementRowMapper" />
</property>
</bean>
The provider don't realy use the mapper in my test then I won't give you mapper's code.
Processor:
<bean id="compositeItemProcessor" class="org.springframework.batch.item.processor.Composite ItemProcessor">
<property name="itemProcessors">
<list>
<!--<bean id="fileItemProcessor" class="org.springframework.batch.item.processor.ItemWrite rItemProcessor">
<property name="itemWriter" ref="fileVersementItemWriter"/>
</bean>-->
<bean id="mockProcessor" class="com.natixis.sphinx.batch.modele.processor.MockProc essor"/>
<!--<bean id="DBItemProcessor" class="org.springframework.batch.item.processor.ItemWrite rItemProcessor">
<property name="itemWriter" ref="sqlSupportDao"/>
</bean>-->
</list>
</property>
</bean>
Only the MockProcessor is used in my test and it does nothing.
its code:
public class MockProcessor implements ItemProcessor {
public void process(Object data) throws Exception {
// TODO Raccord de méthode auto-généré
}
}
My VersementItemProvider is:
public class VersementItemProvider implements ItemProvider {
private Log log=LogFactory.getLog(VersementItemProvider.class) ;
private static final String SELECT_SUPPORT_RECORD = "SELECT c.ceseqo \"contrat\", m.ceseqo, s.ceseqo \"support\", m.amount \"amount\",s.cotaux \"taux\",t.valcot \"cote\",s.nbpart \"nbpart\" "+
" FROM sphmcontrat c, sphmmvt m, sphmsupport s,sphmtypesupport t " +
" WHERE m.dateff<= '01/02/2008' and m.coperi=1 and m.cocont=c.ceseqo and c.ceseqo=s.cocont and t.ceseqo=s.cosupp and (c.coetco=1 or c.coetco=2) order by c.ceseqo, m.ceseqo, s.ceseqo";
private static final String SELECT_CLIENT_RECORD = "select p.ceseqo, p.cocivi,p.nompat,p.nommar,p.prenom,p.preno2,p.dah eme,p.cosecu,p.cositf,a.librue,a.copost,a.libvil,a .copays,a.coposn,a.copayn,a.libvin,c.cochar,c.dahe m1,c.numrib from sphmclient c, sphmadresse a, sphmpersonne p where c.cochar=2 and c.copers=p.ceseqo and p.ceseqo=a.copers order by p.ceseqo";
private Connection con;
private Statement stmt;
protected ResultSet rs;
private DataSource dataSource;
private RowMapper mapper;
private boolean initialized = false;
public Object next() {
if (!initialized){
try {
con = dataSource.getConnection();
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSIT IVE,ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
rs=stmt.executeQuery(SELECT_CLIENT_RECORD);
initialized=true;
} catch (SQLException e) {
log.error(e);
throw new RuntimeException(e);
}
}
Object result = null;
try {
if (rs.next()){
result = new Object();//mapper.mapRow(rs, rs.getRow());
System.out.println(rs.getRow());
} else{
con.commit();
rs.close();
stmt.close();
con.close();
}
} catch (SQLException e) {
log.error(e);
throw new RuntimeException(e);
}
return result;
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
public void setMapper(RowMapper mapper) {
this.mapper = mapper;
}
public Object getKey(Object item) {
return item;
}
public boolean recover(Object data, Throwable cause) {
return false;
}
}
With this code, my batch returns 199913 lines, consums 550 Mo of memory, the processor time is about 14 s and the duration is about 1minute 20 s.
When I change create statement like this in my provider:
stmt = con.createStatement();
my batch returns always 199913 lines but consums 55 Mo of memory; processor time and duration are similar than previously.
I have done some tests using setMaxRows(): the less number of rows is read, the less memory is consumed and the less is the duration but I don't have all the results.
If I close the ResultSet each 10000 records, 85 Mo of memory is consumed with the same time processor as previously but the duration is 5 minutes.
When I increase fetch size, the performance are better (30 s instead of 1minute 20s for fetch size=10000) and 550 Mo of memory are always consumed.
If you want some precisions about a test or if you want the code of the batch which uses JdbcCursorInputSource, say to me.