Apache Camel SQL Batch insertion taking long time

Question

I am using Apache Camel SQL batch insertion process.

1. My application is reading tickets from the Active MQ which contains around 2000 tickets.

2. I have updated the batch as 100.

3. The query which i am firing is as follows:

   sql.subs.insertCdr= insert into subscription_logs(master_id,request_type,req_desc,msisdn,amount,status,resp_code,resp_desc,channel,transaction_id,se_mode,be_mode,sub_type,sub_timeleft,srv_name,srv_id,start_date,end_date,operator,circle,country,time_offset,retry_count,user_status,previous_state,se_reqrecvtime,se_respsenttime,be_reqsenttime,be_resprecvtime,cp_id,cp_name,sub_srvname,sub_srvid,msg_senderid,msg_text,call_back_url,call_back_resp,client_ip,se_sysIp,language,cp_callbackurlhittime,action,alert,notification_url,notification_resp) values(:#masterId, :#requestType,:#reqDesc,:#msisdnCdr,:#price,:#status,:#responseCode,:#reason,:#channel,:#transactionId,:#seMode,:#beMode,:#subType,:#subTimeLeft,:#serviceName,:#serviceId,:#subStartDate,:#cdrEndDate,:#operator,:#circle,:#country,:#timeOffset,:#retryCount,:#userStatus,:#previousState,:#seReqRecvTime,:#seRespSentTime,:#beReqSentTime,:#beRespRecvTime,:#cpId,:#cpName,:#subServiceName,:#subServiceId,:#shortCode,:#message,:#callBackUrl,:#callBackResp,:#clientIp,:#seSysIp,:#language,:#cpCallbackUrlHitTime,:#action,:#alert,:#notificationUrl,:#notificationResponse)

4. The SQL batch route is defined as follows:

   <pipeline>
      <log message="Going to insert in database"></log>
      <transform>
         <method ref="insertionBean" method="subsBatchInsertion"></method>
      </transform>
      <choice>
          <when>
              <simple>${in.header.subsCount} == ${properties:batch.size}</simple>
              <to uri="sql:{{sql.subs.insertCdr}}?batch=true"></to>
              <log message="Inserted rows ${body}"></log>
          </when>
      </choice>
   </pipeline>
   

5. Below is my java code:

   public List<Map<String, Object>> subsBatchInsertion(Exchange exchange) {
   if (subsBatchCounter > batchSize) {
       subsPayLoad.clear();
       subsBatchCounter = 1;
   }
   subsPayLoad.add(generateInsert(exchange.getIn().getBody(SubscriptionCdr.class)));
   exchange.getIn().setHeader("subsCount", subsBatchCounter);
   subsBatchCounter++;
   return subsPayLoad;
   }
   
   public Map<String, Object> generateInsert(Cdr cdr) {
   Map<String, Object> insert = new HashMap<String, Object>();
   try {
       insert = BeanUtils.describe(cdr);
   } catch (Exception e) {
       Logger.sysLog(LogValues.error, this.getClass().getName()+" | "+Thread.currentThread().getStackTrace()[1].getMethodName(), coreException.GetStack(e));
   } 
   for (String name : insert.keySet()) {
       Logger.sysLog(LogValues.APP_DEBUG, this.getClass().getName(), name + ":"+ insert.get(name) + "\t");
   }
   return insert;
   }
   

Now the problem is when there are around 120 ticket in ActiveMQ, SQL batch should have started to insert the values in to the database. But it is taking a lot more time. It starts insertion process when there are around 500 tickets in ActiveMQ. Can anyboody help in optimizing the insertion process? Or any other approach?

Answer 1

The problem was with ActiceMQ consumer numbers.

When i changed the consumer count back to 1, the batch getting updated on time.

Actually, when the consumer count was 10, the tickets were consumed in parallel. That means, for 100 tickets consumed from activemq with 10 consumers, there were approx 10 tickets with each consumer, thus adding more time. When any one of the consumer got 100 tickets, the batch was getting updated.

So changing the consumer count to 1 made all the tickets to be processed by single consumer, and thus performing the batch update fine.