Extract and Insert concurrently into database using JdbcTemplate and BlockingQueue

Question

I am extracting thousands of rows from one database table and inserting into another database table. I don't want to load all the records into memory and then insert into the other database.

Because of that, I am trying to use a BlockingQueue to load a the extractor results using one thread and insert into another database concurrently using another thread. I am using Spring JdbcTemplate to access my database.

Here is my plan

    public void performExtractionInsertion(JdbcTemplate inboundJdbcTemplate, JdbcTemplate outboundJdbcTemplate){

        final BlockingQueue queue = new LinkedBlockingQueue<Transaction>(50);
        ExecutorService executor = Executors.newFixedThreadPool(2);
        final String SELECT_QUERY = "SELECT acc_number, date, type  FROM transactions";
        final String INSERT_QUERY = "INSERT INTO analysis(col1, col2, col3) VALUES(?,?,?)";

        executor.execute(new Runnable() {
            @Override
            public void run() {
                    queue.put(/*IMPLEMENTATION OF EXTRACTOR USING inboundJdbcTemplate*/);
            }
        });
        executor.execute(new Runnable() {
            @Override
            public void run() {
                queue.take(/*IMPLEMENTATION OF INSERTER USING outboundJdbcTemplate*/)
            }
        });
}

Could someone give me an idea of how to implement the EXTRACTOR and INSERTER so that they use the same BlockingQueue to limit the number of rows in memory?

Is this the right approach? Can I still use jdbcTemplate? What is the most intelligent and convenient way to do this?

Thanks guys

BTW, Transaction is the class of the object that is going to hold the extracted elements to be inserted.

Jonathan · Answer 1 · 2013-05-21T08:01:26.103

I've had the very same scenario (in a slightly different setting). I was not going for JdbcTemplate but for MappingSQLQuery. I think, at least for the query it better suited my needs. Given you would be willing to change, the code could look like this

MappingSQLQuery selector = ...;       
executor.execute(new Runnable() {
  public void run() {
    List<WrapObject> list = selector.execute();
    for (WrapObject object : list) {
      while (!queue.offer(object)) {
        Thread.sleep(100);
      }
    }
    while (!queue.offer(WrapObject.NULL_OBJECT)) {
      Thread.sleep(100);
    }
  }
});
executor.execute(new Runnable() {
  public void run() {
    WrapObject object;
    while ((object = queue.take) != WrapObject.NULL_OBJECT) {
      outboundJdbcTemplate.update(INSERT_QUERY, object.getParam1(),...)
    }
  }
});

Given a suitable definition of WrapObject, this should do the trick.

Working a lot with performance-critical DB-Systems I found the following two things.

Often, reimplementing the Spring mappers gives you a better control over the things happening to the database (especially batch-updates, compile-time of PreparedStatements, setting batch-sizes)
If you take a peek at the code, you will learn, that the part of the over-generic Spring-JDBC classes relevant to you is mostly some 10-20 lines and easily reimplemented for your sepcific case while speeding up your application significantly
Depending on your database you might wish to use more than one reader/writer. I have worked with Oracle-Clusters where eight parallel read-processes don't even start to put a significant load on the hardware

Wouldn't the selector add all the rows into memory at the moment you execute it? — locorecto, May 09 '13 at 19:46
No, the selector onl;y fetches `fetchSize` (defaultvalue Oracle:10, MySQL:1) tuples of the ResultSet into memory. Fetch is automatically extended, once you reach the end of the prefetched list. — Jonathan, May 10 '13 at 05:54
So you are saying that the list of WrapObject is full of fetchSize rather than WrapObjects? I thought the list was full of all the objects returned from the query. And then you put each of those object from the list into the queue. IF THAT IS NOT THE CASE, could you please clarify? — locorecto, May 10 '13 at 12:10
Another point, the BlockingQueue doesn't allow to insert null objects. It looks that you are trying to prevent the queue from underflow. However, the implementation of BlockingQueue maintains itself preventing over and underflow. If this is not the case, I would love to be clarified. THANKS AGAIN. — locorecto, May 10 '13 at 12:14
I've modified the code, such that it can deal with BlockingQueues. Now, your memory-consumption is as follows: You have at maximum `fetchSize` tuples in your `ResultSet` and some Elements in transit of the `outboundJdbcTemplate`. — Jonathan, May 21 '13 at 08:04

Extract and Insert concurrently into database using JdbcTemplate and BlockingQueue

1 Answers1