Spring Batch JdbcBatchItemWriter

Question

I'm using a chunk step with a reader and writer. The reader is using a JdbcPagingItemReader with a high page size of 5000. The writer is using JdbcBatchItemWriter with a commit-interval of 5000 on the chunk step.

    <batch:step id="createRecords">
                    <tasklet  allow-start-if-complete="true" >
                        <chunk reader="readTable"  writer="createNewRecords" 
                               commit-interval="5000" skip-limit="100" >
                       <skippable-exception-classes>
                       <batch:include class="java.lang.Exception"/>
                       </skippable-exception-classes>
                        </chunk>
                    </tasklet>
</batch:step>

When I'm using this chunk step to load records everything works as I would expect. It inserts records 5000 at a time (when ther'es no errors) and the performance is as expected. 10000 records are processed in under a minute etc.

However when I use the same chunk step (exact same reader) and change the SQL used by the writer to do an UPDATE statement ( as in a basic SQL update as oppose to an INSERT) the application takes up to 30+ minutes to do an update for 50K records which is poor. A single update statement in SQL (which is almost identical) for the whole table runs in under seconds. I'd like to take advantage of the batch processing if this is possible.

     <bean id="createNewRecords" class="org.springframework.batch.item.database.JdbcBatchItemWriter">
    <property name="dataSource" ref="dataSource" />   
    <property name="sql">
        <value>
            <![CDATA[        
            UPDATE  TABLE SET TABLE_COLUM = :test
            ]]>
        </value>
    </property>
    <property name="itemSqlParameterSourceProvider">
        <bean class="org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider" />
    </property>        
</bean>

Wondering does this sound like a config problem - or are update statements treated differently by spring batchs JdbcBatchItemWriter??

Answer 1

Your update query is updating EVERY single row in the table on every invocation. It needs a "where" clause, ideally based on a unique or primary key index.