Recommendations for using SQLite on mobile devices

In burzhunete there is an interesting document on optimizing SQLite on the BlackBerry platform. However, the ideas that it contains, generally speaking, refer 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 under mobile devices. We accept your suggestions in the comments.

Always use UTF-8 encoding.

This is the best option in terms of speed and memory size (inside SQLite translates all requests into UTF-8, since it only has this encoding parser).
')

Store as little data as possible in the database

This advice is not from Captain Obvious. Do not use SQLite simply because it is "fashionable." Think of alternatives: XML, for example. For using SQLite you have to pay with waste of resources.

Always explicitly manage transactions.

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

Carefully consider creating indexes.

Indexes speed up sampling, but slow down data modification. Sometimes it is 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 data1, data2, data2 columns, 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, the performance in the real scenario will surprise you unpleasantly.

Think about how to store BLOBs

The official site provides a test that shows that when using BLOBs you should:
- put the page size larger (8192 or 16384);
- large BLOBs stored 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 the field with reference to the entry in this table. This is advice from the SQLite developers themselves.

Use temporary tables

If you create a table, you specify "TEMP"

 CREATE TEMP TABLE tab(...) 

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

Use parameterized queries

That is, pass values ​​to SQL queries not in the form of literals inside SQL, but by binding them to parameters. This will simultaneously protect your code from SQL injection attacks and increase speed.

Add AUTOINCREMENT to the PRIMARY KEY definition

Column

 id INTEGER PRIMARY KEY 

is a synonym for the ROWID (unique record identifier) ​​field. 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 "real uniqueness" identifiers add to the definition of AUTOINCREMENT:

 id INTEGER PRIMARY KEY AUTOINCREMENT 

Now, under any circumstances, the new line will get a unique id (and rowid) value (well, or you will exhaust the 8-byte counter).

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

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

For each foreign key field, create an index

Suppose 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 should check whether some record in the detail table refers 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 takes a very long time.

Deleting data from a database does not reduce its file size.

To compress the database file, run

 VACUUM 

This operation takes time.

Well, the most important advice.

Think carefully about the database schema

Usually, first the data is normalized as much as possible, and then denormalization is performed for optimization. For example, if a user often requires 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 they should be separated into a separate table.

Updated

How to run multiple SQL statements?

Combine them into one line through ";".

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

Before inserting a large number of entries, execute

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

Insert records. The best performance can be achieved by compiling (prepare) an insert statement with parameters and repeatedly executing it with the required values. After inserting, restore the settings (the default settings are given):

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

Creating indexes on the table is better after insertion, but not before. Note that when the log is turned off, the database may be corrupted if the application “crashes” during the insertion process.

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