AIR SQLite Optimization tricks

I have been playing with AIR lately and I have mostly been optimizing the interactions between the application and its SQLite database because that’s where I noticed the biggest speed impediment. Having a PHP/MySQL background, some of these tricks were not so obvious to me. Most of what I will list here come from this help page from Adobe, but I thought I would give more example since some weren’t so clear to me at first.

Specify column names in a SELECT or INSERT statement.

Well this is not specific to AIR, but it is still really easy to implement so I thought it was worth mentioning. So in a SELECT Statement list the column name you want instead of using the *. Even if you need all the columns, list them all, it’s going to execute faster.

var selectStmt:SQLStatement = new SQLStatement();
 
//so instead of doing this:
selectStmt.text =  "SELECT * FROM myTable";
 
//do this:
selectStmt.text =  "SELECT columnName1, columnName2 FROM myTable";

Always write the name of the database when you write the name of a table

I don’t know just how much speed gain this is suppoed to give but it is pretty easy to do so I suggest doing this all the time. Now, at first I was confused by  how I could find the name of the database I wanted to do a query on. It happens that most of the time, the name of your database is just going to be “main”. This can change when you use the attach method of a SQLConnection to add multiple database to a connection to do a query on tables from different database. When you attach a new database you will have to provide a name (you can choose what you want) and the first database will still be called “main”

So from my previous example, if we follow this advise, it will look like this:

var selectStmt:SQLStatement = new SQLStatement();
selectStmt.text =  "SELECT columnName1, columnName2 FROM main.myTable";
//main.myTable instead of just myTable

Parametrize your statements

This is something I was completely new to. From my PHP/MySQL experience here is what I would have written for an UPDATE statement with a WHERE clause:

_updateStmt = new SQLStatement();
_updateStmt.sqlConnection = _conn;
_updateStmt.text = "UPDATE main.myTable SET statusF=" + currentStatus + "  WHERE keyId=" + currentId;
_updateStmt.execute();

This is ok if you will use that statement only once in your application, but if you are going to use it multiple times like in a for loop, you’d better use a parametrized statement. It seems like a costly operation is preparing the SQLStatement and a statement is prepared everytime you change its text property. Now here is how you would parametrize the previous example:

_updateStmt = new SQLStatement();
_updateStmt.sqlConnection = _conn;
_updateStmt.text = "UPDATE main.myTable SET statusF=@STATUS  WHERE keyId=@ID";
_updateStmt.parameters["@STATUS"] = currentStatus;
_updateStmt.parameters["@ID"] = currentId;
_updateStmt.execute();

Again, just like this it doesn’t save much, it starts to make sense in a for loop_updateStmt = new SQLStatement();

_updateStmt.sqlConnection = _conn;
_updateStmt.text = "UPDATE main.myTable SET statusF=@STATUS  WHERE keyId=@ID";
 
for (var i:uint = 0; i < currentArray.length; i++){
  _updateStmt.parameters["@STATUS"] = currentArray[i].status;
  _updateStmt.parameters["@ID"] = currentArray[i].id;
  _updateStmt.execute();
}

Use Transactions

This is probably the biggest optimisation you can do. The principle of a transaction is that instead of executing all your statement separately, it will execute them all at the same time. Refer to the previously linked help file from Adobe for more information on this. Transactions are best suited for query that don’t need results like INSERT and UPDATE statements. Transactions are easy to implement:_updateStmt = new SQLStatement();

_updateStmt.sqlConnection = _conn;
_updateStmt.text = "UPDATE main.myTable SET statusF=@STATUS  WHERE keyId=@ID";
 
_conn.begin();//_conn is a SQLConnection, I didn't take the time to write the code for it, but this is where the magic happens
 
for (var i:uint = 0; i < currentArray.length; i++){
  _updateStmt.parameters["@STATUS"] = currentArray[i].status;
  _updateStmt.parameters["@ID"] = currentArray[i].id;
  _updateStmt.execute();
}
 
_conn.commit();

Only when the application will run the commit method will all the SQLStatement will be executed.

This is all the SQLite optimization tricks I have for now, but I still have to investigate indexes which might also help speed up queries.

, , , ,

  1. #1 by RR007 - February 19th, 2009 at 23:49

    This is GREAT. Every little bit helps.

  2. #2 by David Havrda - August 8th, 2009 at 19:47

    I am trying to use the transaction example. But I keep getting an error that says
    Error #3110: Operation cannot be performed while SQLStatement.executing is true.

    Any ideas?

  3. #3 by David Havrda - August 11th, 2009 at 17:44

    Also I would like to know how you opened the database. Did you use open or openAsync? I have found that the transaction stuff will not work when you use openAsync. Everytime a statement.execute is performed it actually does run and does not wait until the connections commit is executed. Do you have any ideas about this?

  4. #4 by ostrov - December 2nd, 2009 at 11:19

    Thank you,
    very interesting article

  5. #5 by Jason - March 23rd, 2010 at 05:25

    Superb, got 600 inserts down from 1min 45 secs to < 5 secs. I know this seems slow (even individually), but it's on Win 7 x64, seemed to work faster on my asus eeepc?!? all good now though.

  6. #6 by radikalFish - April 9th, 2010 at 11:30

    thx for the _conn.begin / _conn.commit hint
    that really speeds up the big bunches of inserts and updates…

  7. #7 by karlThom - March 29th, 2011 at 15:37

    You rock!
    Defined Array of statements,( initTable – create table and 10 inserts ) then

    // statement and listeners, connection and handlers defined elsewhere
    sqlStat = new SQLStatement();
    sqlStat.sqlConnection = sqlConnection;
    sqlStat.text = “”;
    // listeners for results and errors
    sqlStat.addEventListener(SQLEvent.RESULT, statResult);
    sqlStat.addEventListener(SQLErrorEvent.ERROR, createError);
    // as you said, where the fun begins
    sqlConnection.begin();
    // loop through array
    for (var i:uint = 0; i < initTable.length; i++){
    // assign array element to statement
    sqlStat.text = initTable[i];
    //
    sqlStat.execute();
    }
    sqlConnection.commit();
    // almost faster than light…

    thanks a million

  8. #8 by Don Mitchinson - January 12th, 2012 at 19:06

    David Havrda : Also I would like to know how you opened the database. Did you use open or openAsync? I have found that the transaction stuff will not work when you use openAsync. Everytime a statement.execute is performed it actually does run and does not wait until the connections commit is executed. Do you have any ideas about this?

    From the docs .. it is possible.

    After calling the SQLConnection.begin() method to open the transaction, you need to wait for the SQLConnection instance to dispatch its begin event. Only then can you call the SQLStatement instance’s execute() method. In this example the simplest way to organize the application to ensure that the operations are executed properly is to create a method that’s registered as a listener for the begin event. The code to call the SQLStatement.execute() method is placed within that listener method.

  9. #9 by Andrea - March 13th, 2012 at 01:01

    Really useful, thanks for sharing!

  10. #10 by Nicolas - June 28th, 2012 at 19:29

    Thanks a lot.I knew all the tricks but transactions, but that was the last touch it needed from f** slow to bearable fast

(will not be published)
Subscribe to comments feed