Performance with SQLite in Adobe AIR
Posted by David Tucker in AIRApr 18th, 2008 | 13 responsesI 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)
13 Responses to “Performance with SQLite in Adobe AIR”
About Me
David Tucker is a Rich Internet Application developer, author, and trainer. David currently serves as a Software Engineer at Universal Mind. In addition, David also blogs regularly at InsideRIA where he produces the Weekly RIA RoundUp podcast. David also recently completed the AIR 1.5 Cookbook (with a great group of co-authors) and the Lynda.com series, AIR for Flash Developers.
![](\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\"/images/twitter.gif\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\")
[...] David Tucker – Web Development Goodness » Blog Archive » Performance with SQLite in Adobe AIR (tags: air SQLlite) [...]
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?
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.
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 – 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.
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
Bangor car dealers and realtors: It’ s a race to see who can rip you off the most.
Hi, Henry,
Did you find a solution for Error 3105 (besides the synchronous solution)?
http://books.google.com/books?id=QoxUx8GOjKMC&pg=PT39&lpg=PT39&dq=transaction+asychronous+sqlite&source=bl&ots=EtnpX22RvN&sig=VVqz6SVFy-eWhbywTHKM-h4T27U&hl=en&ei=4PKySYOyOoqhtwfOl_TEBw&sa=X&oi=book_result&resnum=6&ct=result
Take a gander at that ugly URL. Appears asynch transactions are supported via a PRAGMA tweak, but AIR does not support PRAGMA tweaks. Off to the wish list….
DK
@DK – Thanks for the research on this. I am sure you are already bringing this up to the AIR team – and I will do the same. I feel that this is a pretty high priority item in overall grand scheme of things. We’ll see what gets implemented for the next version of AIR.
@David – I am developing a desktop application using Adobe Air and Flex 3. On the backend I have a SQL database that’s populated by a DTS package. Currently I am using .Net to create a Web Service and pull that into Flex via WSDL. Sometimes I need to pull datasets that are 500-600k records and I see performance degradation…taking as long as 30 minutes to complete. I’ve heard of SQLLite being populated from other dataset creating persistent data and increased performance. Do you know how this is done? I am open to other ideas as well to increase performance.
Hi David,
am using a synchronous sqlite database connection in adobe air,i have two databases sample1.sqlite and sample2.sqlite both are encrypted by same key, i am able to openboth of the encrypted datases using “open” command in adobe air, my code for your reference:
dbConnection1.open(db1, SQLMode.CREATE, false, 1024, myencrptedKey);
dbConnection2.open(db2, SQLMode.CREATE, false, 1024, myencrptedKey);
am successfully able to open the database in two different sql connection string but the thing is i am not able to attach the encrypted database, since i need to have both the databases binded in a single connection string am trying to attach but an error thrown-error #3125: unable to open the database, take a look of my code for attach a database file:
db1 = File.applicationStorageDirectory.resolvePath(“Sample1.sqlite”);
db2 = File.desktopDirectory.resolvePath(“Sample2.sqlite”);
dbConnection.open(db1, SQLMode.CREATE, false, 1024, encryptionKey);
dbConnection.attach(“db2″,db2,null,encryptionKey);
—-
my sample2.sqlite is in desktop and its free from read only even am not able to attach the database, will be happy on your solution
Solely believed I would visit to advise you learn how all of us appreciated your function significantly. All of your job are all worth though because it was entirely superb. Preserve up the wonderful do the job!