Reproduced SQLite3 Performance Optimization

SQLite3 performance tuning is achieved primarily through pragma directives.

such as adjustment: Space release, disk synchronization, cache size, and so on.

I. Space Release 1. How to query:

PRAGMA auto_vacuum;

Meaning: Query the auto-vacuum tag of the database.

2. Mark Meaning:

Meaning of the auto-vacuum tag: Normally, when a transaction is committed that deletes data from the database, the database file does not change size. Unused file pages are flagged and reused in later additions. In this case, use the vacuum command to release the resulting space. But the vacuum efficiency is very low! 3. How to set:

PRAGMA auto_vacuum = 0 | 1;

When opening auto-vacuum, that is, performing pragma auto_vacuum=1;

When you commit a transaction that deletes data from the database, the database file shrinks automatically (the vacuum command does not work in the Auto-vacuum Open database). The database stores some information internally to support this feature, which makes the database file a little larger than when the option is not turned on.

4. Note: You can change the auto-vacuum tag only if no tables are built in the database. Attempts to modify an existing table will not result in an error.

5. Recommendations:

Do not open. That is, "PRAGMA auto_vacuum = 0;" Executes the command.

Two. Cache size 1. How to query:

PRAGMA cache_size;

Query the number of database file pages that SQLite stores in memory at a time.

2. Mark Meaning:

Each page uses about 1.5K of memory, and the default cache size is 2000. If you need to use an update or delete command that changes a large number of rows, and you don't mind sqlite using more memory, you can increase the cache to improve performance.

3. How to set up

PRAGMA cache_size = number-of-pages;

Modifies the number of database file pages that SQLite stores in memory at a time.

4. Precautions:

When using cache_size pragma to change the cache size, the change is only valid for the current conversation, and the cache size reverts to the default size when the database is turned off and reopened. To permanently change the cache size, use default_cache_size pragma.

5. Recommendations:

Modify to 8000, that is, execute the command "PRAGMA cache_size = 8000;" three. Like operator PRAGMA Case_sensitive_like; PRAGMA case_sensitive_like = 0 | 1;

tag Meaning: The default behavior of the LIKE operator is to ignore the case of the latin1 character. So the value of ' a ' like ' a ' is true by default. You can change this default behavior by opening case_sensitive_like pragma. When Case_sensitive_like is enabled, ' A ' like ' a ' is false and ' a ' like ' a ' is still true.

1. Precautions:

SQLite3.6.22 version is not supported.

2. Recommendations:

Open it. That is, execute the command "PRAGMA case_sensitive_like = 1;" Command. Otherwise the text string in the search will go wrong.

four. 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 turned on, the above statement returns a row of data with an integer value-the number of rows that the statement inserts, modifies, or deletes.

1. Precautions:

The number of rows returned does not include the number of rows changed, such as inserts, modifications, or deletions that are generated by the trigger.

2. Recommendations:

Open for easy commissioning. That is, execute the "PRAGMA count_changes = 1;" Command.

five. Page size PRAGMA page_size; PRAGMA page_size = bytes; Queries or sets the Page-size value.

1. Precautions:

You can set page-size only if the database is not created. The page size must be an integer multiple of 2 and greater than or equal to 512 less than or equal to 8192.

The upper limit can be changed by modifying the value of the macro definition sqlite_max_page_size at compile time. The upper limit is 32768. six. Disk synchronization 1. How to query:

PRAGMA synchronous;

directive meaning : Query the setting of "synchronous" tag, return integer value;

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 pauses during an emergency to determine that the data has been written to disk. This causes the system to crash or power supply problems to ensure that the database is not damaged when it is re-started. The full synchronous is safe but very slow.

When synchronous is set to normal, the SQLite database engine pauses for most emergencies, but not as frequently as in full mode. In normal mode, there is a small chance (but not nonexistent) that a power failure causes a database corruption. But in fact, in this case it is likely that your hard drive is no longer available, or another unrecoverable hardware error has occurred.

When set to synchronous OFF (0), SQLite continues without pausing after passing data to the system. If the application running SQLite crashes, the data is not compromised, but the database may become corrupted if the system crashes or the data is written to an unexpected power outage. On the other hand, some operations may be 50 times times faster or more synchronous off. In SQLite 2, the default value is normal. And in 3, modify to full.

4. Recommendations:

If there is a mechanism for regular backups, and a small amount of data loss is acceptable, use off. Seven. Memory Mode

1. How to query: PRAGMA Temp_store;

directive meaning : Query the setting of "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), a compile-time c preprocessing macro Temp_store is used to define the location where temporary tables and temporary indexes are stored.

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

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

4. Precautions:

When changing the Temp_store settings, all existing temporary tables, indexes, triggers, and views will be deleted immediately.

5. Recommendations:

Use 2, which is memory mode.

Attached instruction table set:

Serial number	Instructions	Meaning	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 not supported)
4	Count_changes	Change the number of rows	0
5	Page_size	Page size	1024
6	Synchronous	Hard disk size	2
7	Temp_store;	Memory mode	0

(End.)

Click to open link

Top

0

Step

Reproduced SQLite3 Performance Optimization