Performance with SQLite in Adobe AIR

April 18th, 2008

I recently had the opportunity to develop a small tutorial application that ran queries on a good sized dataset (not huge - but 10,000 rows). I hadn’t yet had the opportunity to test AIR’s SQLite database functionality, and I must say that I was pleased. The application (which was a tutorial on strongly typed database results and parameterized queries for my AIR API series at InsideRIA.com) - just has a search field that enabled you to search through these 10,000 records to find a certain contact. It also auto-populates the datagrid as you type. So, if you typed my first name ‘David’, it would have search over 10,000 records five times.

When I search, the application’s processor use goes from 2% to 22%, but then it drops almost immediately back down. Also, there is no visible delay between the search and having the datagrid populated. Overall, this leads me to believe that an AIR application could adequately handle extremely large datasets without degrading the user experience or hogging a great deal of system resources.

If you want to play with the application, it is at the bottom of the following tutorial. Also, if you download the source files, I have included the database - which might be useful for just playing around with the SQLite functionality (it was data from FakeNameGenerator.com).

AIR API - Additional Query Techniques (InsideRIA.com)

6 comments on “Performance with SQLite in Adobe AIR”

01

[...] David Tucker - Web Development Goodness » Blog Archive » Performance with SQLite in Adobe AIR (tags: air SQLlite) [...]

17 Pens » links for 2008-04-18 at April 18th, 2008 around 11:50 am
02

The SQLite database is pretty damn fast. I was getting near instantaneous searching with databases that were ~100,000 records. Somewhere around 800,000 - 1,000,000 records you start losing performance, waiting a few seconds for a search. Still, not bad eh?

Daniel Wabyick at May 8th, 2008 around 9:45 am
03

Not bad at all. It’s cool to see it perform that well on data sets that large! It was a great move for Adobe to include an existing powerful solution like SQLite for AIR.

David Tucker at May 8th, 2008 around 11:28 am
04

Hey there, I have some performance problems in using INSERT statements within a flex air application I developed recently. This app reads a csv file (txt file with comma separated data) and puts the data into a db. Since SQLite does not support multiple row inserting within one statement, I use an recursive method to send INSERT statements for each row to the connection object. The result handler of the statement, which handles the SQLEvent.RESULT event, is called very fast (less than a second for about 7000 statements), but the handler dealing with the SQLUpdateEvent.INSERT event of the connection is fired only about 10 times a second, so it takes my app more than 10 minutes to write all my data to the db. This is inacceptable. Of course I’m using async mode. Maybe you can help?! Thanks in advance. Best regards, Henry.

Henry at July 13th, 2008 around 3:41 am
05

@Henry - I have heard of this problem before (I believe that it is worst on Mac’s). I did some checking and SQLite seems to perform batch inserts when these inserts are performed within a transaction. I assume this should translate properly to AIR’s implementation of SQLite. I haven’t tried this personally - but some posts suggest that this speeds up the process quite a bit.

David Tucker at July 14th, 2008 around 7:37 am
06

Hey David, thanks for your answer! I experimented a bit on transactions but wasn’t able to make them work on an asynchronous connection, despite I cannot find anything about a restriction on using the begin/commit functions only on synchronous connections. Calling the commit function always caused the following error: ‘Error #3105: Operation is only allowed if a connection has an open transaction’, while calling the begin function didn’t cause any trouble at all. Later I changed my connection mode to synchronous and executed all (>7000 records) INSERT statements within one! transaction and it took around 3 to 4 seconds to populate the table with the data. Although I’m happy to have found a solution for my performance problem (thanks a lot for the hint trying transactions!!!), I’m a bit frustrated, cause now I have to change a lot of code. In asynchronous mode I didn’t need to bother about executing statements parallel (and I made use of this quiet extensively), but now I even have to close the asynchronous connection to establish the synchronous one. Further more I have to disable all buttons, etc. to advert user interaction during import process. Best wishes! Henry

Henry at July 14th, 2008 around 1:17 pm