User experience considerations with SQLite operations

Allurent Desktop Connection's database pool

The Allurent Desktop Connection bundled a large database with the application to support client-side keyword and color searching. Additionally this database included detailed information on all products, including ones not shown in the primary navigation. A key aspect of the application was to provide as rich and engaging a user experience as possible while providing access to all of the information in the bundled database.

The use of panning and zooming as the primary navigation mechanism meant that using a synchronous connection would have impacted the user experience too much. When a user selects a product on the main strip, the application zooms in on the product before rendering detailed information about it. In the background while the zoom effect is playing, the application issues a database operation to load the full product information from the database. The effect that accomplishes the zooming was tuned to take slightly longer than it took to load the data from the database, so the user is visually occupied while the data is loading.

To minimize the burden of working with the asynchronous APIs we developed two utility classes. The first is the ConnectionPool class. This class handles opening one or more connections to the same database and executing database operations whenever a connection is available. In our application we found that we frequently had a long-running database operation executing at the same time a short operation needed to execute. Instead of queueing all database operations on the same connection, by using multiple connections we enabled the system threads to execute both queries at the same time. The ConnectionPool class handles the bookkeeping of which connections are in use and which are free, and assigns pending database operations to the next available connection.

On top of the ConnectionPool we wanted to leverage the use of prepared statements for the performance gains over needing to parse the SQL query each time. Since a SQLStatement is bound to a particular connection, managing which SQLStatement to use based on the connection returned from the ConnectionPool was cumbersome. The StatementPool was developed to encapsulate the logic of caching and reusing a SQLStatement based on the connection it was associated with.

These two utility classes provided a simplified view to the rest of the application. A typical data load operation was reduced to specifying the query to run, the parameters for the query, and the function the SQLResult should be passed to. This technique is used by the PoolExample class in the sample application. All the performance benefits of multiple connections and cached prepared SQLStatements were hidden from the rest of the application.

Where to go from here

This article discussed the two execution modes (synchronous and asynchronous), the programming model used for each (method calls versus handler functions), the primary factors that drive what execution mode should be used (time to complete database operations and user interface responsiveness when executing them), and some tips about other areas to consider (post processing delays, multiple connections, and database operation queuing). With this knowledge you can now better understand what type of execution mode will be best to use for your application.

Page 8 of 8

Previous Page