Test

If you plan to create a database with a large schema or insert large blobs, you should test the database on your target BlackBerry devices to make sure that the devices have adequate memory.

Store as little data as possible

Most of the processing time of SQLite databases is taken up by reading and writing to storage. Less data generally means fewer reads and writes. The SQLite database engine caches frequently accessed database pages. By storing less data, you can increase the probability that the SQLite database engine retrieves requested data more quickly from the cache instead of from the relatively slow storage access.

Use explicit transactions

If you do not use explicit transactions, a transaction begins before each statement is executed and ends after the statement is executed. This default behavior is inefficient. It requires the opening, reopening, writing to, and closing of the journal file for each statement. With explicit transactions, you can group statements.

Create efficient indexes

Indexes can greatly reduce the time required to scan a table. Consider the following guidelines:

• Indexes can improve performance for read-only queries such as SELECT, but they can reduce performance for queries that change rows. Consider only indexing tables that are infrequently updated.
• The order of columns in an index affects performance. Columns that are typically used in WHERE clauses should be placed first, followed by columns that are typically used in ORDER BY clauses.
• For columns containing data that is retrieved, create a covering index.
• Avoid duplicate indexes. The SQLite database engine automatically creates indexes for columns that have UNIQUE or PRIMARY KEY constraints.
• Columns declared as INTEGER PRIMARY KEY result in faster queries because they have direct access to table data.

Minimize the size of rows

If you have a very large row, consider putting it in a separate table.

Store BLOBs appropriately

If your data includes BLOBs, consider storing each BLOB in a separate table. If the BLOBs are very large, you can store them as files outside the database, and store the path to each file in the database. This practice does introduce overhead for filename lookups.

Consider using temporary tables

If you do not need the data to be available following a restart of a BlackBerry device, use the CREATE TEMP TABLE statement instead of CREATE TABLE.

Use SQL parameters

To execute a set of statements of the same format, first prepare a generic statement that uses SQL parameters. You can execute the statement by iterating through the variable values and binding the values to the named variables in each iteration.

Avoid subqueries

In some cases, the SQLite database engine stores subquery results in a temporary file, which can slow down processing.

Defragment the database

Use the SQLite VACUUM command to defragment the database. This process also reduces the size of the database file.

Consider the order of columns in table declarations

The order of columns in a table declaration affects performance, especially in the absence of an index, because the SQLite database engine scans the columns in the order defined in the table declaration. Columns that contain small amounts of data that is frequently accessed should be placed before columns that contain large amounts of data that is infrequently accessed.