Best practices for using SQLite on mobile devices

In the bourgeois there is an interesting document on SQLite optimization on the BlackBerry platform. However, the ideas that it contains, generally speaking, apply to any mobile platform. We decided to develop this list and create a kind of reference text in RuNet with a set of useful, introductory tips for programming SQLite on mobile devices. We accept your suggestions in the comments.

Always use UTF-8 encoding

This is the best option for speed and memory size (inside SQLite translates all queries into UTF-8, since it has a built-in parser only for this encoding).

Keep as little data as possible in the database

This advice is not from Captain Evidence. Do not use SQLite simply because it is "fashionable." Think of alternatives: XML, for example. SQLite is a waste of resources.

Always explicitly manage transactions

Otherwise, a separate, implicit transaction will be created and committed to each SQL command. Group data changes.

Think carefully about creating indexes

Indexes speed up sampling, but slow down data modification. It is sometimes useful to use covering indexes when data fields are included in the index definition:

CREATE INDEX idx ON tab(key1, key2,  data1, data2, data3);

Now, if you select (by the condition on key1 and key2) the data of the columns data1, data2, data2, SQLite can take their values ​​from the index itself (there is no need for additional reading of data from the table). But such an index takes up more space. Therefore, the following advice:

The best way to test something: test on a specific device

And, of course, not on an empty base. Fill it with random data, if not suitable. Otherwise, performance in a real scenario will unpleasantly surprise you.

Think about how to store blobs

A test is given on the official website , which shows that when using BLOBs, you should:
- put a larger page size (8192 or 16384);
- Large blobs to store in separate files.
If you decide to store blobs in the database, create a separate table for them:

CREATE blobs(blobid INTEGER PRIMARY KEY, data BLOB)

and in the data tables, put a field with a link to the record in this table. This is advice from SQLite developers themselves.

Use temporary tables

If when creating the table, you specified "TEMP"

CREATE TEMP TABLE tab(...)

then the created table will be destroyed automatically when the database is closed.

Use parameterized queries

That is, pass values ​​to SQL queries not as literals inside SQL, but by binding them to parameters. This will simultaneously protect your code from SQL injection attacks and improve performance.

Add AUTOINCREMENT to the PRIMARY KEY definition

Column

id INTEGER PRIMARY KEY 

is synonymous with the ROWID field (unique record identifier). The trick is that SQLite does not guarantee that new rows will receive new keys if records were previously deleted from the table. If you need a “true uniqueness” of identifiers, add to the AUTOINCREMENT definition:

id INTEGER PRIMARY KEY AUTOINCREMENT

Now, under any circumstances, the new line will receive a unique id (and rowid) value (well, or you’ll run out of an 8-byte counter).

Use foreign keys (FOREIGN KEY) only if you really need it

SQLite has a fairly advanced mechanism for supporting foreign keys. By default, it is disabled (for compatibility). On the one hand, foreign keys guarantee integrity. On the other hand, they require resources. If you still decide to use them, then ...

For each foreign key field, create an index

Let's say there are a couple of tables:

CREATE TABLE master(mid INTEGER PRIMARY KEY ...);
CREATE TABLE detail(fk_master REFERENCES master(mid) ON DELETE CASCADE ...);

When deleting a record from the master table, SQLite must check to see if any record in the detail table is referring to it and makes an additional selection:

SELECT rowid FROM detail WHERE fk_master = ?

As a rule, developers forget to create an index using the FK key, and this selection works for a very long time.

Removing data from the database does not reduce the size of its file

To compress the database file, run

VACUUM

This operation takes time.

Well, the most important advice.

Think carefully about the database schema

Usually at first the data is normalized as much as possible, and then denormalization is performed to optimize. For example, if the user is often asked for the name of his department, this name can be duplicated in the user table in a separate field. Think about what data is changing most intensively in your database, perhaps it should be highlighted in a separate table.

Supplemented by

How to execute several SQL statements at once?

Combine them in one line through ";".

How to quickly add a lot of records (bulk insert)?

Before inserting a large number of records, do

 PRAGMA synchronous = OFF; 
 PRAGMA journal_mode = OFF;
 BEGIN;

Insert notes. The highest performance can be achieved by compiling the prepare statement with parameters and repeatedly executing it with the required values. After insertion, restore the settings (the default settings are given):

 COMMIT;
 PRAGMA synchronous = NORMAL; 
 PRAGMA journal_mode = DELETE;

Creating indexes on a table is best after insertion, not before. Note that if the log is disabled, the database may be corrupted if the application crashes during the insertion process.

PS. Use a modern manager to administer the SQLite database.