Table of Contents for

Using SQLite

Most mobile devices have limited memory resources. Applications must be conscious of their memory usage, and often need to limit the resources that may be consumed. In most cases, the majority of SQLite memory usage comes from the page cache. By picking a sensible page size and cache size, the majority of memory use can be controlled. Remember that each open or attached database normally has its own, independent cache. The page size can be adjusted at database creation with the PRAGMA page_size command, while the cache size can be adjusted at any time with PRAGMA cache_size. See page_size and cache_size in Appendix F for more details.

Be aware that larger cache sizes are not always significantly better. Because some types of flash storage systems have no effective seek time, it is sometimes possible to utilize a relatively small page cache while still maintaining acceptable performance. The faster response time of the storage system reduces the cost of pulling pages into memory, lessening the impact of the cache. Just how fast the storage system can respond has a great deal to do with types of flash chips and how they are configured in the device, but depending on your system, you may find it acceptable to use a relatively small cache. This should help keep your memory footprint under control.

If you’re working in an extremely constrained environment, you can preallocate buffers and make them available to SQLite through the sqlite3_config() interface. Different buffers can be assigned for the SQLite heap, scratch buffers, and page cache. If buffers are configured before the SQLite library is initialized, all memory allocations will come from these buffers. This allows a host application precise control over the memory resources SQLite may use.

Most other issues are fairly self-evident. For example, the use of in-memory databases is generally discouraged on memory-bound devices, unless the database is very small and the performance gains are significant. Similarly, be aware of queries that can generate large intermediate result sets, such as ORDER BY clauses. In some cases it may make more sense to pull some of the processing or business logic out of the database and into your application, where you have better control over resource utilization.

Nearly all mobile devices use some type of solid-state storage media. The storage may be on-board, or it may be an expansion card, such as an SD (Secure Digital) card, or even an external thumb drive. Although these storage systems provide the same basic functionality as their “real computer” counterparts, these storage devices often have noticeably different operating characteristics from traditional mass-store devices.

If possible, try to match the SQLite page size to the native block size of the storage system. Matching the block sizes will allow the system to write database pages more quickly and more efficiently. You want the database page size to be the same size as one or more whole filesystem blocks. Pages that use partial blocks will be much slower. You don’t want to make the page too large, however, or you’ll be limiting your cache performance. Finding the right balance can take some experimentation.

Normally, SQLite depends heavily on filesystem locking to provide proper concurrency support. Unfortunately, this functionality can be limited on mobile and embedded platforms. To avoid problems, it is best to forego multiple database connections to the same database file, even from the same application. If multiple connections are required, make sure the operating system is providing proper locking, or use an alternate locking system. Also consider configuring database connections to acquire and hold any locks (use the PRAGMA locking_mode command; see locking_mode in Appendix F). While this makes access exclusive to one connection, it increases performance while still providing protection.

It may be tempting to turn off SQLite’s synchronization and journaling mechanism, but you should consider any possible consequences of disabling these procedures. While there are often significant performance gains to be found in disabling synchronizations and journal files, there is also the significant danger of unrecoverable data corruption.

For starters, mobile devices run off batteries. As we all know, batteries have a tendency to go dead at very annoying times. Even if the operating system provides low-power warnings and automatic sleep modes, on many models it is all too easy to instantly dislodge the battery if the device is dropped or mishandled. Additionally, many devices utilize removable storage, which has a tendency to be removed and disappear at inconvenient times. In all cases, the main defense against a corrupt database is the storage synchronization and journaling procedure.

Storage failures and database corruption can be particularly devastating in a mobile or embedded environment. Because mobile platforms tend to be more closed to the user, it is often difficult for the end-user to back up and recover data from individual applications, even if they are disciplined enough to regularly back up their data. Finally, data entry is often slow and cumbersome on mobile devices, making the prospect of manual recovery a long and undesirable prospect. Mobile applications should be as forgiving and error tolerant as possible. In many cases, losing a customer’s data will result in losing a customer.

Beyond special memory handlers and storage considerations, most other concerns boil down to being aware of the limitations of your platform and keeping resource use under control. If you’re preparing a custom SQLite build for your application, you should take some time to run through all the available compiler directives and see if there are any defaults you want to alter or any features you might want to disable (see Appendix A). Disabling unused features can reduce the code and memory footprints even further. Disabling some features also provides minor performance increases.

It is also a good idea to read through the available PRAGMA statements and see if there are any further configuration options to customize SQLite behavior for your specific environment. PRAGMA commands can also be used to dynamically adjust resource use. For example, it might be possible to temporarily boost the cache size for an I/O intensive operation if it is done at a time when you know more memory is available. The cache size could then be reduced, allowing the memory to be used elsewhere in the application.

When the iPhone and iPod touch were first released, Apple heavily advocated the use of SQLite. The SQLite library was provided as a system framework and was well documented, complete with code examples, in the SDK.

With the release of version 3.0, Apple has made their Core Data system available on the iPhone OS. Core Data has been available on the Macintosh platform for a number of years, and provides a high-level data abstraction framework that offers integrated design tools and runtime support to address complex data management needs. Unlike SQLite, the Core Data model is not strictly relational in nature.

Now that the higher level library is available on their mobile platform, Apple is encouraging people to migrate to Core Data. Most of the SQLite documentation and code examples have been removed from the SDK, and the system-level framework is no longer available. However, because Core Data utilizes SQLite in its storage layer, there is still a standard SQLite system library available for use. It is also relatively easy to compile application-specific versions of the SQLite library. This is required if you want to take advantage of some of the more recent features, as the system version of SQLite is often several versions behind.

Core Data has some significant advantages. Apple provides development tools that allow a developer to quickly lay out their data requirements and relationships. This can reduce development time and save on code. The Core Data package is also well integrated into current Mac OS X systems, allowing data to move back and forth between the platforms quite easily.

For all the advantages that Core Data provides, there are still situations where it makes sense to use SQLite directly. The most obvious consideration is if your development needs extend beyond Apple platforms. Unlike Core Data, the SQLite library is available on nearly any platform, allowing data files to be moved and accessed almost anywhere on any platform. Core Data also uses a different storage and retrieval model than SQLite. If your application is particularly well suited to the Relational Model, there may be advantages to having direct SQL query access to the data storage layer. Using the SQLite library directly also eliminates a number of abstraction layers from the application design. While this may lead to more detailed code, it is also likely to result in better performance, especially with larger datasets.

Like many engineering choices, there are benefits and concerns with both approaches. Assuming the platform limitations aren’t a concern, Core Data can provide a very rapid solution for moderately simple systems. On the other hand, SQLite allows better cross-platform compatibility (in both code and data), and allows direct manipulation of complex data models. If you’re not dependent on the latest version of SQLite, you may even be able to reduce the size of your application by using the existing SQLite system library. Which set of factors has higher value to you is likely to be dependent on your platform requirements, and the complexity of your data model.

A number of smartphone environments require application development to be done in Java or some similar language. These systems often provide no C compilers and limit the ability to deploy anything but byte-code. While most of these platforms provide custom wrappers to system SQLite libraries, these wrappers are often somewhat limited. Typically, the system libraries are several versions behind, and the Java wrappers are often limited to the essential core function calls.

While this may limit the ability to customize the SQLite build and use advanced features of the SQLite product, these libraries still provide full access to the SQL layer and all the functionality that comes with it, including constraints, triggers, and transactions. To get around some limitations (like the lack of user-defined functions), it may sometimes be necessary to pull some of the business logic up into the application. This is best done by designing an access layer into the application that centralizes all of the database functions. Centralizing allows the application code to consistently enforce any database design constraints, even when the database is unable to fully do so. It is also a good idea to include some type of verification function that can scan a database, identifying (and hopefully correcting) any problems.