To optimize the performance of a SQLite® database on a BlackBerry® device, you must consider both the database design and how your application uses the SQLite API to interact with the database.

Consider the following guidelines:

• Store as little data as possible. SQLite caches frequently accessed database pages. By storing less data you can increase the probability that the SQLite library retrieves requested data more quickly from the cache rather than from the relatively slow flash memory.
• Use temporary tables. Do this only if you do not need the data to be available following a reset of the BlackBerry device.
• Prepare generic statements that use named variables. Execute the statements when they are required by iterating through the variable values, binding the values to the named variables in each iteration.
• Use explicit transactions. Otherwise, a transaction begins before each statement is executed and ends after the statement is executed. This is inefficient. It requires the opening, reopening, writing to, and closing of the journal file for each statement.
• Avoid subqueries. By default, the SQLite® library stores the subquery results in a temporary file.