Sunday, August 24, 2008

Google Gears Database versus TrimQuery

The on-browser data manipulation and analysis that I tried out earlier was not very scalable. One of the reasons was the TrimQuery API itself, as it needed javascript data structures to be created in memory for the whole database. There was also no scope of caching the data on the client side.

Google Gears lets web applications store data locally in a SQLite database. The query language is slightly more sophisticated than TrimQuery and most importantly it lets me cache data! So I modified the earlier sample to work with Google Gears Database. You can download the sources here. Extract into any local folder and start the HTML file on your browser. If you do not have Gears installed, it will prompt you to install. If you already have it, it may prompt you to create a local database.

I didn't have to make too many changes as you would see. I think it is much faster and much more scalable than TrimQuery. I could display two orders of magnitude more amount of data using gears with the same performance and responsiveness as that of TrimQuery.

One difficulty I faced and overcame was of insert performance. Insert performance in SQLite is extremely bad without transactions as the engine has to flush the database file to disk with every command. During a bulk insert, remember to wrap all your inserts with a "begin;" and "commit;".

Also check out Uriel Katz's blog where he talks about a Gears ORM library that lets you do ORM on top of Google Gears Database.