Sqlite3 Performance Optimization

Sqlite3 performance adjustment is mainly achieved through The Pragma command.

Such as space release, Disk Synchronization, and cache size adjustment.

I. Space release
1. How to query:

Pragma auto_vacuum;

Description: queries the auto-vacuum tag of a database.

2. Mark meaning:

Description of auto-vacuum Tag:
Normally, when a transaction is committed to delete data from the database, the database file size does not change. Unused file pages are marked and used again in subsequent add operations. In this case, use the VACUUM command to release the deleted space. However, vacuum is very inefficient!
3. How to set:

Pragma auto_vacuum = 0 | 1;

When auto-vacuum is enabled, that is, execute Pragma auto_vacuum = 1;

When a transaction is committed to delete data from the database, the database file is automatically shrunk (the vacuum command does not work in the database enabled by auto-vacuum ). The database stores some internal information to support this function, which makes the database file slightly larger than when this option is not enabled.

4. Notes:
The auto-vacuum tag can be changed only when no table is created in the database. If you try to modify an existing table, no error is returned.

5. Suggestions:

Do not open. That is, run the "Pragma auto_vacuum = 0;" command.

Ii. cache size
1. How to query:

Pragma cache_size;

Queries the number of pages of database files stored in memory by SQLite.

2. Mark meaning:

Each page uses about 2000 KB of memory. The default cache size is. if you need to change a large number of multi-line update or delete commands, and you don't mind using more memory for SQLite, you can increase the cache to improve performance.

3. How to Set

Pragma cache_size = number-of-pages;

Modify the number of database files stored in the memory of SQLite at a time.

4. Notes:

When cache_size Pragma is used to change the cache size, the change is only valid for the current dialog. When the database is closed and re-opened, the cache size is restored to the default size. To change the cache size permanently, use default_cache_size Pragma.

5. Suggestions:

Change to 8000, that is, execute the command "Pragma cache_size = 8000;
Iii. Like Operator
Pragma case_sensitive_like;
Pragma case_sensitive_like = 0 | 1;

Tag description:
The default behavior of the like operator is to ignore Latin1 characters in case. Therefore, by default, the value of 'A' like 'A' is true. You can enable case_sensitive_like Pragma to change this default behavior. When case_sensitive_like is enabled, 'A' like 'A' is false while 'A' like 'A' is still true.

1. Notes:

Sqlite3.6.22 is not supported.

2. Suggestions:

Open. That is, run the "Pragma case_sensitive_like = 1;" command. Otherwise, an error occurs when searching for a Chinese string.

Iv. Like Operator
Pragma count_changes;
Pragma count_changes = 0 | 1;
Query or change the count-changes tag.

Normally, insert, update, and delete statements do not return data.

When Count-changes is enabled, the preceding statement returns the number of rows inserted, modified, or deleted for a row containing an integer.

1. Notes:

The number of rows returned does not include the number of rows generated by the trigger, such as insertion, modification, or deletion.

2. Suggestions:

Open to facilitate debugging. That is, run the "Pragma count_changes = 1;" command.

5. Page size
Pragma page_size;
Pragma page_size = bytes;
Query or set the page-size value.

1. Notes:

Page-size can be set only when no database is created. The page size must be an integer multiple of 2 and greater than or equal to 512 and less than or equal to 8192.

The upper limit can be changed by modifying the sqlite_max_page_size value of the macro definition during compilation. The upper limit is 32768.
6. Disk Synchronization
1. How to query:

Pragma synchronous;

Description: Query the settings of the "synchronous" Mark and return an integer;

2. How to set:
Pragma synchronous = Full; (2)
Pragma synchronous = normal; (1)
Pragma synchronous = off; (0)

3. Parameter meaning:

When synchronous is set to full (2), the SQLite database engine will pause in an emergency to confirm that the data has been written to the disk. This ensures that the database will not be damaged when the system crashes or the power supply goes wrong. Full synchronous is safe but slow.

When synchronous is set to normal, the SQLite database engine is paused in most emergency periods, but not as frequently as in full mode. In normal mode, there is a small probability (but not non-existent) that a power failure will cause database damage. But in fact, in this case, it is very likely that your hard disk is no longer available, or there are other unrecoverable hardware errors.

When synchronous off (0) is set, SQLite continues directly after passing data to the system without pausing. If the application running SQLite crashes, the data will not be damaged, but the database may be damaged if the system crashes or data is written into the database without unexpected power failure. On the other hand, some synchronous off operations may be 50 times faster or more.
In SQLite 2, the default value is normal, and the value is changed to full in 3.

4. Suggestions:

If there is a regular backup mechanism and a small amount of data loss is acceptable, use off.
VII. Memory mode

1. How to query:
Pragma temp_store;

Description: Query the settings of the "temp_store" parameter;

2. How to set:
Pragma temp_store = default; (0)
Pragma temp_store = file; (1)
Pragma temp_store = memory; (2)

3. Parameter meaning:

When temp_store is set to default (0), use the C pre-processing macro temp_store during compilation to define the location for storing temporary tables and temporary indexes.

If it is set to file (1), it is stored in the file. Temp_store_directorypragma can be used to specify the directory where the file is stored.

When set to memory (2), temporary tables and indexes are stored in the memory.

4. Notes:

When the temp_store settings are changed, all existing temporary tables, indexes, triggers, and views will be deleted immediately.

5. Suggestions:

Use 2, that is, the memory mode.

Appendix:

Serial number	Command	Description	Default Value
1	Auto_vacuum	Space release	0
2	Cache_size	Cache size	2000
3	Case_sensitive_like	Like case sensitive	(Note: sqlite3.6.22 is not supported)
4	Count_changes	Number of changed rows	0
5	Page_size	Page size	1024
6	Synchronous	Hard Disk size	2
7	Temp_store;	Memory mode	0

(End .)

Click Open Link