title: Spring Batch: DAO call from processor slows down batch process -- can I reuse DB connection?
body: <p>When I add a DAO call in the MdwValidatingItemProcessor below, I get a serious hit in performance. Prior to adding the additional DAO call, 2500 records where being read/processed/written in 40ish seconds. When I add the one additional DAO call (lobPolicyMapper.getPolicyOrigin(item.getLob(),item.getRgn_cd())
  ), it becomes 120 seconds. And the lob_policy table only has one record in it. I plan to have many such DAO calls in my processor to do additional validation of the item. Can I reuse the DAO's connection so for each processor that is validating an item, the connection doesn't have to be opened and closed constantly? The AsyncItemProcessor delegates to the MdwValidatingItemProcessor:</p>
  
  <pre><code>public class MdwValidatingItemProcessor implements
      ItemProcessor&lt;MecMdw, MecMdw&gt; {
  
  @Autowired
  DataSourceTransactionManager txManager;
  
  @Autowired
  LobPolicyMapper lobPolicyMapper;
  
  @Autowired
  MecUtils mecUtils;
  
  @Autowired 
  LobShopMapper lobShopMapper;
  
  @Autowired
  KaiserIssuerMapper kaiserIssuerMapper;
  
  private Validator validator;
  
  public void setValidator(Validator validator) {
      this.validator = validator;
  }
  
  private List&lt;Map&gt; validationReasons;
  
  @BeforeStep
  public void beforeStep(StepExecution stepExecution) {
      validationReasons = (List&lt;Map&gt;) stepExecution.getJobExecution().getExecutionContext().get("validationReasons");
  }
  
  
  public MecMdw process(MecMdw item) throws Exception {
  
      DefaultTransactionDefinition def = new DefaultTransactionDefinition();
      def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
      TransactionStatus status = txManager.getTransaction(def);
  
      BindingResult results = BindAndValidate(item);
  
      try {
          if (results.hasErrors()) {
  
              buildValidationException(results, item);
  
              return setAsKickOut(item);
  
          }
  
          mecUtils.checkForPaddingAndValidateSSN(item);
  
          //if both SSN and DOB are valid, set Rep_DOB_or_SSN to be SSN           
          if (item.getInvalid_ssn().equals(MecConstants.Y_FLAG) &amp;&amp; mecUtils.isDOBValid(item)) {
              item.setRep_doborssn("SSN");
          }
          else if(mecUtils.isDOBValid(item)){
              item.setRep_doborssn("DOB");
          }
          else{
              List&lt;String&gt; listOfErrors = new ArrayList&lt;String&gt;();
  
              listOfErrors.add("BothSSNAndDOBInvalid");
  
              item.setValidationErrors(listOfErrors);
  
              return setAsKickOut(item);
          }                       
  
          // Policy Origin code not found in MEC database based on Region /
          // LOB.
          LobPolicy lobPolicy = lobPolicyMapper.getPolicyOrigin(
                  item.getLob(), item.getRgn_cd());
  
          if (lobPolicy == null || ("").equals(lobPolicy.getLob())
                  || lobPolicy.getLob() == null) {
              return setAsKickOut(item);
          }
  
          //set the Rep_PolicyOgnCd
          item.setRep_policy_ogn_cd(lobPolicy.getPolicyOrigin());
  
          //If origin of policy = SHOP, look for Shop Identifier.(mec_lob_shop table)
          if(("SHOP").equals(lobPolicy.getPolicyOrigin())){
              if(lobShopMapper.getValidationShopIdentifier(item) == null){
                  return setAsKickOut(item);
              }               
          }
  
          //Validation Shop Identifier not found in MEC database based on Region/LOB/PID
          if(lobShopMapper.getValidationShopIdentifier(item) == null){
              return setAsKickOut(item);
          }
  
          //Kaiser Issuer EIN not found in MEC database based on Region/LOB.
          Integer kaiserIssuer = kaiserIssuerMapper.getKaiserIssuerIdWithLobAndRegion(item);
          if(kaiserIssuer  == 0){
              return setAsKickOut(item);
          }
          else{
              item.setRep_kaiser_issuer_id(kaiserIssuer.toString() );
          }
  
      } catch (Exception ex) {
          ex.printStackTrace();
      } finally {
          txManager.commit(status);
      }
  
      return item;
  
  }
  
  private MecMdw setAsKickOut(MecMdw item) {
      item.setKick_out_fl('Y');           
  
      return item;
  }
  
  private BindingResult BindAndValidate(MecMdw item) {
  
      DataBinder binder = new DataBinder(item);
  
      binder.setValidator(validator);
  
      binder.validate();
  
      return binder.getBindingResult();
  
  }
  
  private void buildValidationException(BindingResult results, MecMdw item) {
  
      List&lt;String&gt; listOfErrors = new ArrayList&lt;String&gt;();
  
      for (ObjectError error : results.getAllErrors()) {
          String[] codes = error.getCodes();
          listOfErrors.add(codes[1]);
      }
  
      item.setValidationErrors(listOfErrors);
      // throw new ValidationException(msg.toString());
  }
  </code></pre>
  
  <p>I have a batch job using AsyncItemProcessor and AsyncItemWriter as follows:</p>
  
  <pre><code>&lt;job id="mecmdwvalidatorJob" xmlns="http://www.springframework.org/schema/batch"&gt;
      &lt;step id="mdwvalidatorStep1"&gt;
          &lt;tasklet&gt;
              &lt;chunk reader="pageItemReader" processor="asyncItemProcessor"
                  writer="asynchItemWriter" commit-interval="1000" skip-limit="2147483647"&gt;
                  &lt;skippable-exception-classes&gt; &lt;!-- TODO --&gt;
                      &lt;include class="java.lang.Exception" /&gt;
                  &lt;/skippable-exception-classes&gt;
              &lt;/chunk&gt;
          &lt;/tasklet&gt;
      &lt;/step&gt;
  &lt;/job&gt;
  
  &lt;bean id="pageItemReader"
      class="org.springframework.batch.item.database.JdbcPagingItemReader"&gt;
      &lt;property name="dataSource" ref="dataSource" /&gt;
      &lt;property name="queryProvider"&gt;
          &lt;bean
              class="org.springframework.batch.item.database.support.SqlPagingQueryProviderFactoryBean"&gt;
              &lt;property name="dataSource" ref="dataSource" /&gt;
              &lt;property name="selectClause"
                  value="select MDW_ID,FK_LOG_FILE_ID,TAX_YEAR,SUBS_TYPE_CD,SUB_FIRST_NM,SUB_MIDDLE_NM,SUB_LAST_NM,SUB_SUFFIX,SUB_DOB,SUB_ADDR1,SUB_ADDR2,SUB_CITY,SUB_STATE,SUB_PROVINCE,SUB_ZIP,SUB_ZIP4,SUB_COUNTRY_CD,SUB_COUNTRY,SUB_F_POSTAL_CD,LOB,SUB_SSN,GRP_EMP_NAME1,GRP_EMP_NAME2,GRP_EIN,GRP_ADDR1,GRP_ADDR2,GRP_CITY,GRP_STATE,GRP_PROVINCE,GRP_ZIP,GRP_ZIP4,GRP_COUNTRY_CD,GRP_COUNTRY,GRP_F_POSTAL_CD,ISSUER_NAME1,ISSUER_NAME2,ISSUER_PHONE,ISSUER_ADDR1,ISSUER_ADDR2,ISSUER_CITY,ISSUER_PROVINCE,ISSUER_ZIP,ISSUER_ZIP4,ISSUER_COUNTRY_CD,ISSUER_COUNTRY,ISSUER_F_POSTAL_CD,MEM_FIRST_NM,MEM_MIDDLE_NM,MEM_LAST_NM,MEM_SUFFIX,MEM_SSN,MEM_DOB,MEM_START_DATE,MEM_END_DATE,REGION_CD,SUB_MRN,SUB_MRN_PREFIX,MEM_MRN,MRN_PREFIX,PID,SUB_GRP_ID,SUB_GRP_NAME,INVALID_ADDR_FL" /&gt;
              &lt;property name="fromClause"
                  value="from MEC_MDW JOIN MEC_FILE_LOG on MEC_FILE_LOG.LOG_FILE_ID=MEC_MDW.FK_LOG_FILE_ID  " /&gt;
              &lt;property name="whereClause" value="where MEC_FILE_LOG.STATUS=:status" /&gt;
              &lt;property name="sortKey" value="MDW_ID" /&gt;
          &lt;/bean&gt;
      &lt;/property&gt;
      &lt;property name="parameterValues"&gt;
          &lt;map&gt;
              &lt;entry key="status" value="READY TO VALIDATE" /&gt;
          &lt;/map&gt;
      &lt;/property&gt;
      &lt;property name="pageSize" value="1000" /&gt;
      &lt;property name="rowMapper" ref="mdwRowMapper" /&gt;
  &lt;/bean&gt;
  &lt;bean id="mdwRowMapper" class="org.my.rowmapper.MdwRowMapper" /&gt;
  
  &lt;bean id="asyncItemProcessor"
      class="org.springframework.batch.integration.async.AsyncItemProcessor"&gt;
      &lt;property name="delegate"&gt;
          &lt;bean
              class="org.my.itemprocessor.MdwValidatingItemProcessor"&gt;
              &lt;property name="validator"&gt;
                  &lt;bean
                      class="org.springframework.validation.beanvalidation.LocalValidatorFactoryBean" /&gt;
              &lt;/property&gt;
          &lt;/bean&gt;
      &lt;/property&gt;
      &lt;property name="taskExecutor" ref="taskExecutor" /&gt;
  &lt;/bean&gt;
  
  &lt;task:executor id="taskExecutor" pool-size="10" /&gt;
  
  &lt;bean id="asynchItemWriter"
      class="org.springframework.batch.integration.async.AsyncItemWriter"&gt;
      &lt;property name="delegate" ref="customerCompositeWriter"&gt;
      &lt;/property&gt;
  &lt;/bean&gt;
  
  &lt;bean id="customerCompositeWriter"
      class="org.springframework.batch.item.support.CompositeItemWriter"&gt;
      &lt;property name="delegates"&gt;
          &lt;list&gt;
              &lt;ref bean="itemWriter1" /&gt;
              &lt;ref bean="itemWriter2" /&gt;
          &lt;/list&gt;
      &lt;/property&gt;
  &lt;/bean&gt;
  
  &lt;bean id="itemWriter1" class="org.my.writer.MdwWriter" /&gt;
  &lt;bean id="itemWriter2" class="org.my.writer.KickoutWriter" /&gt;
  </code></pre>
  
  <p>The transaction manager and MyBatix SqlSessionTemplate:</p>
  
  <pre><code>&lt;!-- transaction manager, use JtaTransactionManager for global tx --&gt;
  &lt;bean id="transactionManager"
      class="org.springframework.jdbc.datasource.DataSourceTransactionManager"&gt;
      &lt;property name="dataSource" ref="dataSource" /&gt;
  &lt;/bean&gt;
  
  &lt;!--  define SqlSessionFactory as BATCH execution --&gt;
  &lt;bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate"&gt;
      &lt;constructor-arg index="0" ref="sqlSessionFactory" /&gt;
      &lt;constructor-arg index="1" value="BATCH" /&gt;
  &lt;/bean&gt;
  
  &lt;!-- stored job-meta in database --&gt;
  &lt;bean id="jobRepository"
      class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean"&gt;
      &lt;property name="dataSource" ref="dataSource" /&gt;
      &lt;property name="transactionManager" ref="batchTransactionManager" /&gt;
      &lt;property name="databaseType" value="sqlserver" /&gt;
  &lt;/bean&gt;
  
  &lt;bean id="jobLauncher"
      class="org.springframework.batch.core.launch.support.SimpleJobLauncher"&gt;
      &lt;property name="jobRepository" ref="jobRepository" /&gt;
  &lt;/bean&gt;
  </code></pre>
  
  <p>The DAO making the call:</p>
  
  <pre><code>public interface LobPolicyMapper {
  
  public abstract LobPolicy getPolicyOrigin(@Param("lob") String lob, @Param("regionCd") String regionCd);
  </code></pre>
  
  <p><strong><em>UPDATE</em></strong> I added ehcache to the the MyBatis XML. This will help with the repetitive queries. But again, I am looking for a way to share this same DAO's connection across the AsyncItemProcessors:</p>
  
  <pre><code>&lt;mapper namespace="org.mybatis.LobPolicyMapper"&gt;
  
  &lt;cache type="org.mybatis.caches.ehcache.EhcacheCache"/&gt;
  
  &lt;select id="getPolicyOrigin" parameterType="hashmap" resultType='org.my.domain.LobPolicy'&gt;
      SELECT 
      l.lob_id as lobId,
      l.lob as lob,
      l.policy_origin as policyOrigin,
      l.region_cd as regionCd
      from mec_lob_policy l
      where lob= #{lob} and region_cd=#{regionCd}
  &lt;/select&gt;
  </code></pre>
  
  <p></p>
  
  <p>I added ehcache to the project</p>
  
  <pre><code>    &lt;dependency&gt;
          &lt;groupId&gt;org.mybatis&lt;/groupId&gt;
          &lt;artifactId&gt;mybatis-ehcache&lt;/artifactId&gt;
          &lt;version&gt;1.0.0&lt;/version&gt;
      &lt;/dependency&gt;
  </code></pre>
  
  <p>I think I need a database pool. Here is what I am seeing in my logs on exiting the processor:</p>
  
  <pre><code> 2015-08-27 17:09:54,194 [taskExecutor-3] DEBUG org.mybatis.spring.SqlSessionUtils - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@77b027f]
   2015-08-27 17:10:06,674 [taskExecutor-3] DEBUG org.mybatis.spring.SqlSessionUtils - Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@77b027f]
  2015-08-27 17:10:06,675 [taskExecutor-3] DEBUG org.mybatis.spring.SqlSessionUtils - Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@77b027f]
  2015-08-27 17:10:06,675 [taskExecutor-3] DEBUG org.mybatis.spring.SqlSessionUtils - Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@77b027f]
  2015-08-27 17:10:06,675 [taskExecutor-3] DEBUG org.springframework.jdbc.datasource.DataSourceTransactionManager - Initiating transaction commit
  2015-08-27 17:10:06,675 [taskExecutor-3] DEBUG org.springframework.jdbc.datasource.DataSourceTransactionManager - Committing JDBC transaction on Connection [ConnectionID:14 ClientConnectionId: 1b95dc0e-83c0-487b-af59-f5be52931818]
  2015-08-27 17:10:06,805 [taskExecutor-3] DEBUG org.springframework.jdbc.datasource.DataSourceTransactionManager - Releasing JDBC Connection [ConnectionID:14 ClientConnectionId: 1b95dc0e-83c0-487b-af59-f5be52931818] after transaction
  2015-08-27 17:10:06,805 [taskExecutor-3] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Returning JDBC Connection to DataSource
  </code></pre>
  
  <p>Here is what I am seeing in my logs on entering the processor. New transaction is created and a new database connection is fetched:</p>
  
  <pre><code>2015-08-27 17:10:06,805 [taskExecutor-3] DEBUG org.springframework.jdbc.datasource.DataSourceTransactionManager - Creating new transaction with name [null]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
  2015-08-27 17:10:06,805 [taskExecutor-3] DEBUG org.springframework.jdbc.datasource.DriverManagerDataSource - Creating new JDBC DriverManager Connection to [jdbc:sqlserver://blah]
  2015-08-27 17:10:07,115 [taskExecutor-3] DEBUG org.springframework.jdbc.datasource.DataSourceTransactionManager - Acquired Connection [ConnectionID:23 ClientConnectionId: d1f016e6-3e9d-4b0e-a34d-14298c292a65] for JDBC transaction
  2015-08-27 17:10:07,115 [taskExecutor-3] DEBUG org.springframework.jdbc.datasource.DataSourceTransactionManager - Switching JDBC Connection [ConnectionID:23 ClientConnectionId: d1f016e6-3e9d-4b0e-a34d-14298c292a65] to manual commit
  </code></pre>