Recently, I am trying to build an interactive reporting tool that needs to deal with lots of data. The data is not dynamic because it is basically data from historical performance log files. However, the volume of the data is large (over few millions of rows) and I still want my clients to interact with large amount of data in ease. With this, I am looking into Adobe AIR as I heard that it comes with in-memory database “SQLite“. I believe it should have better performance than web-based application because data is local and SQLite is lightweight and fast. Apart from that, SQLite supports parameterized query, strongly-typed result, asynchronous/ synchonous processing, indexing, view, trigger, transaction and most of SQL92. On top of that, it is small footprint, cross-platform and open source. The tradeoff for SQLite is its weak support in concurrency because it is using table exclusive lock. However, it is totally fine for desktop application because it normally only serves one user. For more info of SQLite, check out my notes below.
Update
SQLite 3 is released that addressed some of its issues in version 2.
- BLOB support
- Fulltext searching
- Connection shared between threads
- Improve concurrency
However, it still doesn’t support writeable view, nested transaction and foreign key.
Presentation
Here is a nice presentation from Paul Roberson, look at it first.
Note from the video:
- Warm up with general SQL Tips (Join favors subquery, Avoid IN, Avoid LIKE, Specify columns name in select and insert, Avoid unnecessary join)
- AIR SQL connection can connect up to 10 databases at a time, you can use qualifier for your tables.
- Don’t reuse the same SQL Statement for different prepared statements.
- Use transaction to do batch insert/ update/ delete operations (48x faster!)
- Index columns in WHERE clause, use together index together
- Create table structure before you add data because internally SQLite…?
- Handle large resultset in parts for perceived performance gain (detailed)
There are several things I want to find out:
- Can SQLite handles large dataset?
- Yes. According to spec, it can handle terabyte of data.
- Does SQLite support pagination?
- Yes. Look at SQL Statement object.
- How SQLite synchronize with the updated data from the remote database?
- Strategies: overwrite vs delta (timestamp, field by field comparison, dirty flag)
- Live cycle data service has built-in SQLite synchronization support including offline caching and conflict management.
- http://coenraets.org/blog/2008/05/insync-automatic-offline-data-synchronization-in-air-using-lcds-26/
Some notes about SQLite
Below are some SQLite tips and practices I obtained from different sources:
- A big advantage of sqlite above a flat file is the possibility to index your data.
- Using parameterize query protects against sql injection, and makes the ‘ problems go away. It is also much faster because sqlite can reuse the execution plan of statements when you use parameters.
- Make sure to import the records in a transaction so that it doesn’t spend a lot of time creating indexes until everything is imported.
- The SQLite documentation states that SQLite databases can be terabytes in size, and that the primary limitation of SQLite is concurrency (many users at the same time).
- “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” – by Daniel
- Each database is contained within a single file.
Reference
Below are some good links I have found:
- Develop air application with large dataset
- SQLite at the Lake – Peter Elst (06/26/2009)
- SQLite admin tool by David Deraedt – not open source
- Another SQLite admin tool by Christophe Coenraets (10/2007) – open source
- Firefox SQLite Manager plugin – support csv import. However, the project is no longer active.
- Adobe Air and SQLite video session (4/18/2008) – it contains 3 interesting examples.
- Get started with adobe air and sqlite (1/28/2009)
- Develop the connection and statement pool
0 Comments.