Sqlite | Titanium Development

All posts in Sqlite

10x Faster INSERTs in SQLite using BEGIN / COMMIT in Appcelerator Titanium Mobile

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
January 26, 2012 10:21 pm via Twitterrific for MacReplyRetweetFavorite
@tobiwankenobi
tobiwankenobi

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.
January 27, 2012 5:38 pm via Twitterrific for MacReplyRetweetFavorite
@tobiwankenobi
tobiwankenobi

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!

Date posted: January 27, 2012

6 comments - Latest by:
  • jeff Hi Stephen. See https://gist.github.com/1210394 for the tracer.
  • Stefan Hi Jeff, Looks like something I'll try What type is tracer variable, can't find anything that matches the createTracer() method in the ...
Top

App.Properties vs SQLite in Appcelerator Titanium Mobile: Learning to love your inner JSON

When I started working with Appcelerator Titanium, one of the big advantages was the built in SQLite database. This was a must in my first app – an RSS reader. I was working with thousands of articles in that needed to be queried and sorted in different ways. However, now that I am working in smaller apps, SQLite may not be the right solution.

In our current app, I am querying a web service and storing the data locally for off-line use. The local storage is minimal – only about 100 of the most recent records. Each record has about 8 fields. Initially, I setup database tables that mirrored the server. The onload method of httpClient request inserted the rows received from the server. The UI wasn’t as responsive as I liked while the database was being updated so I looked at some different options.

The amount of data I needed to store was small – only about 70k stringified JSON. Could I just use App.Properties to save it?

I ran some tests to compare the performance of save and load using App.Properties vs SQLite. Here are the results on different platforms:

App.Properties vs SQLite Save

App.Properties vs SQLite Load

The big difference is the in save – saving one stringified JSON object with App.Properties was 80x faster than inserting the 105 rows! The load was also much faster – 3x faster. This is probably due to the overhead creating tableViewRows when loading the table data.

Update Jan 27 2010: I have since found out that using BEGIN / COMMIT statements, I could have made my INSERT / Save SQLite code much quicker. See 10x Faster INSERTs in SQLite using BEGIN / COMMIT in Appcelerator Titanium Mobile.

Besides the speed benefit, the code is much simpiler when using App.Properties for both the save and the load.

Of course, your results may very depending on the amount of data (and if you need to sort or query), but App.Properties could save you (and your users’ iPhone) a ton of time.

Date posted: October 30, 2011

2 comments - Latest by:
  • jeff I think the App.Properties is better suited for smaller pieces of information. I suspect that parsing anything very big will ...
  • Ben Hi there, thanks for the tips, but do you know what's the storage limitation for properties? 10M? Thanks.
Top
Page 1 of 1