Performance gotcha in AIR's local database

Submitted by Falken on

Today I was writing what looked like fairly simple code to persist ~60 items in a local database using AIR, and then load them back up at application start-up.

It all worked more-or-less first time, as advertised but would take several seconds to do all 60, during which time the application would appear to be locked up.
Neither disk or CPU usage would be max'ed out during this time either, which makes it double odd.
My code looked like

for ( var i:int=0;i<rpt.length;i++ ){
var day:DayNumber=rpt[i];
reportsDataStmt.parameters[":reports_id"]=id;
reportsDataStmt.parameters[":dayNumber"]=day.number;
reportsDataStmt.parameters[":workingDay"]=day.workingDay;
reportsDataStmt.parameters[":dayDate"]=day.date;
reportsDataStmt.parameters[":dayOrder"]=i;
reportsDataStmt.execute();
}

In the end, it was a fairly simple fix, and obvious in hind sight.
The docs say "by default each SQL statement is executed within its own transaction, and the transaction ends when the statement's execution succeeds". It turns out that this is what the problem was, and simply starting and ending my own transaction fixed things, as "from that point on, all SQL statements executed against the SQLConnection instance take place within the transaction".
This means adding just two extra lines to start and end the transaction:

sqlConnection.begin();

for ( var i:int=0;i<rpt.length;i++ ){
    var day:DayNumber=rpt[i];
    reportsDataStmt.parameters[":reports_id"]=id;
    reportsDataStmt.parameters[":dayNumber"]=day.number;
    reportsDataStmt.parameters[":workingDay"]=day.workingDay;
    reportsDataStmt.parameters[":dayDate"]=day.date;
    reportsDataStmt.parameters[":dayOrder"]=i;
    reportsDataStmt.execute();
}

sqlConnection.commit();
So, note to self: Transactions are for performance, not just for safety.
Sections

hye...i'm interested in you article.but u didn't mention on how an application can save data in the database.for example,when user click the 'save' button,all the data in that application can be saved into the database.

Well, the only bit I left out was

var file:File = File.userDirectory.resolvePath("contact.db");
var isNewDB:Boolean = !file.exists;
sqlConnection = new SQLConnection();
sqlConnection.open(file);
if (isNewDB) runCreateTableStatements();
var stmt:SQLStatement = new SQLStatement();
stmt.sqlConnection = sqlConnection;
stmt.text = "INSERT into foo (bar,baz) values (:bar,:baz)";

Have you checked out the DevNet article on this area ?

Tom

(Disclaimer: a bit of self promotion here)

The idea of wrapping batch operations in a transaction for performance is also covered in the documentation (along with other performance tips):

http://help.adobe.com/en_US/AIR/1.5/devappsflex/WS5b3ccc516d4fbf351e63e3d118666ade46-7d47.html

Some additional techniques are described in this presentation (slides and video):

http://probertson.com/articles/2008/10/30/video-and-other-updates-from-360flex-air-sql-optimization/

Submitted by garindeathray (not verified) on Thu, 11/12/2009 - 11:28

Permalink

just tried this on a project I am working on that parses 140k+ records from a csv file into a sqlite db. Previously it was taking 8+ minutes to complete, adding the begin and complete reduced this to just 40 seconds! I cannot thank you enough.