A while back, I wrote about how multiple INSERT statements in SQLite can be pretty slow. Today, I saw the following tweet from Tobias Reike:
cool,just decreased the time to save data to db in my android app from about 1.5min down to 10sec by using only one sqlite transaction
After contacting Tobias, he pointed me to the BEGIN / COMMIT documentation in SQLite:
@ look here: http://t.co/4XW2DRwn I’ve just done a db.execSQL(“BEGIN”) before and db.execSQL(“COMMIT”) after my inserts.
Sure enough, I modified my code to use BEGIN / COMMIT and the results were pretty awesome. Inserting 105 records went from 585ms to 44ms – more than 10x faster!
I’m sure this falls into the category of something I should have known, but it was news to me that SQLite had BEGIN / COMMENT. Here is my code of before and after:
// BEFORE
var dbLog = tracer.createTracer('db');
dbLog.info('opening database');
var db = database.open();
dbLog.info('deleting old items');
db.execute('DELETE FROM ITEM');
dbLog.info(db.rowsAffected + ' rows deleted');
dbLog.info('starting insert');
for( var i = 0; i < data.length; i++ ){
var item = data[i];
db.execute('INSERT INTO ITEM (SERVERID, TITLE, TYPE, Q1, Q2, MAINPHOTOURL, ALBUMPHOTOURL, THUMBPHOTOURL, AUTHOR, AUTHORID, AUTHORPHOTO, COMMENTCOUNT) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)', item.id, item.title, item.type, item.q1, item.q2, item.mainPhotoURL, item.albumPhotoURL, item.thumbPhotoURL, item.authorUsername, item.authorId, item.authorUsername, item.commentCount);
}
dbLog.info( 'finished inserts' );
dbLog.info( 'closing database' );
db.close();
dbLog.info('database save complete');
// AFTER
var dbLog2 = tracer.createTracer('db2');
dbLog2.info('opening database');
var db2 = database.open();
dbLog2.info('deleting old items');
db2.execute('DELETE FROM ITEM');
dbLog2.info(db2.rowsAffected + ' rows deleted');
dbLog2.info('starting BEGIN');
db2.execute('BEGIN');
dbLog2.info('starting insert');
for( var i = 0; i < data.length; i++ ){
var item = data[i];
db2.execute('INSERT INTO ITEM (SERVERID, TITLE, TYPE, Q1, Q2, MAINPHOTOURL, ALBUMPHOTOURL, THUMBPHOTOURL, AUTHOR, AUTHORID, AUTHORPHOTO, COMMENTCOUNT) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)', item.id, item.title, item.type, item.q1, item.q2, item.mainPhotoURL, item.albumPhotoURL, item.thumbPhotoURL, item.authorUsername, item.authorId, item.authorUsername, item.commentCount);
}
dbLog2.info( 'finished inserts' );
dbLog2.info( 'starting commit' );
db2.execute('COMMIT');
dbLog2.info( 'finished commit' );
dbLog2.info( 'closing database' );
db2.close();
dbLog2.info('database save complete');
And the results:
[INFO] db 0ms opening database
[INFO] db 0ms deleting old items
[INFO] db 4ms 105 rows deleted
[INFO] db 4ms starting insert
[INFO] db 589ms finished inserts
[INFO] db 589ms closing database
[INFO] db 589ms database save complete
[INFO] db2 0ms opening database
[INFO] db2 0ms deleting old items
[INFO] db2 10ms 105 rows deleted
[INFO] db2 10ms starting BEGIN
[INFO] db2 10ms starting insert
[INFO] db2 25ms finished inserts
[INFO] db2 25ms starting commit
[INFO] db2 53ms finished commit
[INFO] db2 54ms closing database
[INFO] db2 54ms database save complete
I hope this helps!