Adding a bulk insert to a DAO

[Nick Campion]

I've created a DAO by following your example in the documentation.

I have ErrorMailDao the interface:

public interface ErrorMailDao extends Dao<ErrorMail, String> {}

And ErrorMailDaoImpl:

public class ErrorMailDaoImpl extends BaseDaoImpl<ErrorMail, String>
implements ErrorMailDao {

ConnectionSource cs = null;

public ErrorMailDaoImpl(ConnectionSource connectionSource) throws
SQLException {
super(connectionSource, ErrorMail.class);
cs = connectionSource;

}
}

I wanted to bulk load about 32,000 entries using ormlite and found
that I was only able to do 5-10 per second with sqlite through jdbc.
This seemed to be because of the transaction code. I created a small
method that doesn't use ORMLite like so:

PreparedStatement prep = conn.prepareStatement(
"INSERT INTO `reports`
(`id` ,`linkId` ,`date` ,`appVersion` ,`model` ,`kernel` ,`phoneId` ,`stackTrace` )
VALUES (?,?,?,?,?,?,?,?);");

System.out.println("Preparing db commit...");
int i = 0;
for(ErrorMail mail : errorMails){
prep.setString(1, Integer.toString(i++));
//load up all my params
prep.addBatch();
}

conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);

And when I used this I was able to do about 15,000 entries per
second. What I'd like to do is understand how to implement this
inside the DAO based on the ORMLite infrastructure. I looked some and
it seemed that creating a batchTask to do the inserts should work, but
I must have been doing it wrong, because when I went through my
inserts it seemed like it didn't disable the single insert
transaction:

mailDao.callBatchTasks(new Callable<String>() {

public String call() throws Exception {
for(ErrorMail mail : errorMails){
mailDao.create(mail);
}
return "none";
}

});

Could anyone explain to me the best method of doing bulk imports like
this? Should I just do it outside of ormlite? Or is there a way to
do it inside the DAO framework?

[Gray]

On Jan 30, 12:01 am, Nick Campion <camp...@gmail.com> wrote:
>
> I wanted to bulk load about 32,000 entries using ormlite and found
> that I was only able to do 5-10 per second with sqlite through jdbc.

Yes. This is because of the autocommit.

>             conn.setAutoCommit(false);
>             prep.executeBatch();
>             conn.setAutoCommit(true);

Right.

> I must have been doing it wrong, because when I went through my
> inserts it seemed like it didn't disable the single insert transaction:

Huh. I would have expected it to work. The batch transaction code
basically disables the autoCommit calls the Callable and then resets
the auto-commit. It should have done just what your code was doing.

public <CT> CT callBatchTasks(DatabaseConnection connection,
Callable<CT> callable) throws Exception {
boolean autoCommitAtStart = false;
try {
if (connection.isAutoCommitSupported()) {
autoCommitAtStart = connection.getAutoCommit();
if (autoCommitAtStart) {
// disable auto-commit mode if supported and enabled at start
connection.setAutoCommit(false);
logger.debug("disabled auto-commit on table {} before batch
tasks", tableInfo.getTableName());
}
}
return callable.call();
} finally {
if (autoCommitAtStart) {
// try to restore if we are in auto-commit mode
connection.setAutoCommit(true);
logger.debug("re-enabled auto-commit on table {} after batch
tasks", tableInfo.getTableName());
}
}
}

Are you seeing those logger.debug messages?

disabled auto-commit on table errormail before batch tasks
re-enabled auto-commit on table errormail after batch tasks

> Could anyone explain to me the best method of doing bulk imports like
> this?  Should I just do it outside of ormlite?  Or is there a way to
> do it inside the DAO framework?

The batch stuff is the right way to do it. Another thing to try is to
use the TransactionManager and try to do the inserts in a single
transaction. This also turns out autocommit but also enables a
savepoint. But the autocommit code really should do it.

gray

[Gray Watson]

On Feb 3, 2011, at 11:05 AM, Gray wrote:

> if (connection.isAutoCommitSupported()) {

I wonder if SQLite JDBC is improperly saying that auto-commit is not supported. Can you debug down to there to see if it works Nick.

gray

[Nicholas Campion]

Most certainly. I have it in my todo queue, I'll let you know as soon
as I'm able to get it debugged. Hopefully in the next 4-6 hours.

>
> gray
>

[Nicholas Campion]

Sorry it took me a bit to get back to this.

I debugged down the stack and I figured out the issue. I stepped
through the code and could see that connection.isAutoCommitSupported()
was returning true and that setAutoCommit was being called with 'false'.
Still the commits were happening on every insert.

The real problem seems to stem from the fact that I was using
JdbcPooledConnectionSource. I have to admit my lack of expertise on the
matter, but I assumed pooling the jdbc connections for 35k inserts would
be good. I really probably shouldn't have started there but, early
optimization is one of my short comings :)

Anyway, it seems that callBatchTasks only effects one connection. When
I got into the actual commit logic, I could see that I was getting a
different connection source for the creates then i got when i called
callBatchTasks. It seems as though there is some state information
missing from BaseDaoImpl when callBatchTasks is called to ensure that
all connection sources that are created also have auto-commit turned
off. I'm not sure of the best way to implement this or if this is a
true issue.

Thanks,
Nick

On Thu, 2011-02-03 at 11:08 -0500, Gray Watson wrote:

[Gray Watson]

On Feb 11, 2011, at 2:04 PM, Nicholas Campion wrote:

> Anyway, it seems that callBatchTasks only effects one connection. When
> I got into the actual commit logic, I could see that I was getting a
> different connection source for the creates then i got when i called
> callBatchTasks.

Oh boy. That as a bug dude. Just fixed this in the main line and it will be in 4.11. A real miss for sure. I assed a saveSpecialConnection() call to the callBatchTasks() which will mean that every time you get a connection inside of the callable, it will be the same connection that has been auto-committed.

Here's the new copy of the BaseDaoImpl.callBatchTasks():

public <CT> CT callBatchTasks(Callable<CT> callable) throws Exception {
checkForInitialized();
DatabaseConnection connection = connectionSource.getReadWriteConnection();
try {
// new line
connectionSource.saveSpecialConnection(connection);
return statementExecutor.callBatchTasks(connection, callable);
} finally {
// new line
connectionSource.clearSpecialConnection(connection);
connectionSource.releaseConnection(connection);
}
}

Sorry about that.
gray

[Gray Watson]

Created bug:

https://sourceforge.net/tracker/?func=detail&aid=3178539&group_id=297653&atid=1255989

gray