title: Parallel processing large SQL table with Camel
body: <p>I am trying to process about 7 million rows daily from an Informix table with Apache Camel but I can't figure out how it can be accomplished.</p>
  
  <p>My first attempt that was working with a very low set of data (about 50k rows) was using <code>.split(body()).parallelProcessing()</code> like so:</p>
  
  <pre><code>from("quartz2://transaccionesGroup/myTimerTransaction?cron=0+1+0+*+*+?")
  .bean(QueryTable.class, "queryData").split(body()).parallelProcessing() // Essentially executes a query on my table and returns a list of MyTable.class
  .bean(ProcessTable.class, "processData") // Converts each MyTable object into another type of object (NewData.class) for later processing, storing in them in a synchronized list
  .end().to("direct:transform-data");
  
  from("direct:transform-data")
  .bean(ProcessNewData.class, "processNewData").split(body()).parallelProcessing() // Obtains list
  .bean(AnalyzeData.class, "analyze") // Analyzes the data
  .bean(PersistData.class, "persist") // Persists the new data on other tables
  .end();
  </code></pre>
  
  <p>This of course resulted in an "OutOfMemory" error when I tried it with 500k rows on <code>.bean(QueryTable.class, "queryData").split(body()).parallelProcessing()</code> because it first tried caching all of the data from the query before parsing it. I tried setting <code>fetchSize</code> to something like 100 but I got the same error, and using <code>maxRows</code> would only get me the amount of rows I specified and ignore the rest.</p>
  
  <p>My next attempt was using one of Camel's components like <a href="http://camel.apache.org/sql-component.html" rel="nofollow noreferrer">sql-component</a> and <a href="http://camel.apache.org/jdbc.html" rel="nofollow noreferrer">jdbc</a> and trying to use a Splitter to process each row in separate threads but I got the same exact problem.</p>
  
  <p>sql:</p>
  
  <pre><code>from("quartz2://transaccionesGroup/myTimerTransaction?cron=0+1+0+*+*+?")
  .bean(QueryTable.class, "queryDataParams") // Gets the params for my query
  .to("sql:SELECT * FROM my_table WHERE date_received BETWEEN :#startDate AND :#endDate?dataSource=dataSourceInformix").split(body()).parallelProcessing()
  // The rest would be essentially the same
  </code></pre>
  
  <p>jdbc:</p>
  
  <pre><code>from("quartz2://transaccionesGroup/myTimerTransaction?cron=0+1+0+*+*+?")
  .bean(QueryTable.class, "queryString") // Gets the query to execute
  .to("jdbc:dataSourceInformix").split(body()).parallelProcessing()
  </code></pre>
  
  <p>My last attempt was to use <code>maxMessagesPerPoll</code> for sql and <code>outputType=StreamList</code> for jdbc components but unfortunatelly the former only processes one row at a time (as well as it has to be a consumer to be used as such) and the latter gives me an <code>java.sql.SQLException: Cursor not open</code> exception.</p>
  
  <p>sql:</p>
  
  <pre><code>from("sql:" + query +"?dataSource=dataSourceInformix&amp;maxMessagesPerPoll=100") // I need to be able to use the quartz2 component
  </code></pre>
  
  <p>jdbc:</p>
  
  <pre><code>.to("jdbc:dataSourceInformix?outputType=StreamList").split(body()).streaming() // Throws exception
  </code></pre>
  
  <p>The end goal is to be able to process millions of rows without consuming so much memory so as to prevent the "OutOfMemory" error. My idea, if possible is to do the following:</p>
  
  <ol>
  <li>Create my query on quartz cron-trigger</li>
  <li>Obtain and group N amount of results</li>
  <li>Send a group of results to be process (in another thread) whilst another group is being obtained</li>
  <li>Repeat untill all data has been processed</li>
  </ol>
  
  <p>I know this question is similar to <a href="https://stackoverflow.com/questions/38764814/camel-sql-consumer-performance-for-large-datasets">this one</a> but the answer doesn't really help my situation. I also noticed that in the documentation for the sql component it has an <code>outputType=StreamList</code> option for the producer but it is implemented on version 2.18 and higher while I have version 2.14.1.</p>
  
  <p>Any help and tips would be extremely helpful!</p>
  
  <p>Thanks.</p>
  
  <p>Some other info:
  Apache Camel Version: 2.14.1
  Database: Informix</p>