SQLiteIt is a lightweight relational database management system, widely used in embedded devices, mobile applications and small desktop programs. One of its powerful features is throughPRAGMACommand, users can query and set database configuration parameters to optimize query performance and database management.PRAGMAThe command provides a variety of functions to control database behavior, including cache size, index settings, query optimization, etc. In this article, we will explore how to use SQLite'sPRAGMACommands to optimize query performance and improve database response speed.
In SQLite,PRAGMAIt is a special command used to control the behavior of the database, query optimization, and performance tuning.PRAGMACommands can be used to set various configurations of the database, such as cache size, database schema, transaction behavior, etc.
1. Space release
1. How to query:
PRAGMA auto_vacuum;
Meaning: Query the auto-vacuum tag of the database.
2. Marking meaning:
The meaning of auto-vacuum tag:
Normally, when submitting a transaction that deletes data from the database, the database file does not change the size. Unused file pages are marked and used again in a later addition operation. In this case, use the VACUUM command to free the deleted space. But Vacuum is very inefficient!
3. How to set:
PRAGMA auto_vacuum = 0 | 1;
When auto-vacuum is turned on, that is, execute pragma auto_vacuum=1;
When a transaction that deletes data from the database is submitted, the database file automatically shrinks, (VACUUM command does not work in a database enabled by auto-vacuum). The database will store some information internally 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 only be changed if no tables are created in the database. Attempting to modify the table in the event of an existing table will not cause an error.
Don't open it. That is, "PRAGMA auto_vacuum = 0;" execute the command.
2. Cache size
1. How to query:
PRAGMA cache_size;
Query the number of database file pages stored in memory at one time by SQLite.
2. Marking meaning:
Each page uses about 1.5K memory, and the default cache size is 2000. If you need to use UPDATE or DELETE commands that change a large number of multiple lines, and don't mind SQLite using more memory, you can increase the cache to improve performance.
3. How to set it up
PRAGMA cache_size = Number-of-pages;
Modify the number of database file pages stored in memory in SQLite at one time.
4. Notes:
When changing the cache size using cache_size pragma, the change is only valid for the current conversation, and the cache size is restored to the default size when the database is closed and reopened. To permanently change the cache size, use default_cache_size pragma.
Modify it to 8000, that is, execute the command "PRAGMA cache_size =8000;";
3.LIKE operator PRAGMA case_sensitive_like;
PRAGMA case_sensitive_like = 0 | 1;
Marking meaning:The default behavior of the LIKE operator is to ignore the case of the latin1 character. Therefore, by default, the value of 'a' LIKE 'A' is true. This default behavior can be changed 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. Notes:
SQLite version 3.6.22 is not supported.
2. Suggestions:
Open. That is, execute the command "PRAGMA case_sensitive_like = 1;" command. Otherwise, searching for Chinese strings will make an error.
4.LIKE operator PRAGMA count_changes;
PRAGMA count_changes = 0 | 1;
Query or change the count-changes tag.
Under normal circumstances, the INSERT, UPDATE and DELETE statements do not return data.
When count-changes is enabled, the above statement returns a row of data containing an integer value - the number of rows inserted, modified or deleted by the statement.
1. Notes:
The number of rows returned does not include the number of rows changed by inserts, modifications, or deletions generated by the trigger.
2. Suggestions:
Open for easy debugging. That is, execute the command "PRAGMA count_changes = 1;".
5. Page size PRAGMA page_size;
PRAGMA page_size = bytes;
Query or set the page-size value.
Notes:
Page-size can only be set if the database is not created. The page size must be an integer multiple of 2 and greater than or equal to 512 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.
6. Disk synchronization
1. How to query:
PRAGMA synchronous;
Meaning of instructions: Query the settings of the "synchronous" tag and return the 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 in an emergency to determine that the data has been written to disk. This will ensure that the database will not be damaged after restarting if the system crashes or power is in trouble. FULL synchronous is safe but slow.
When synchronous is set to NORMAL, the SQLite database engine will pause in most emergency moments, but not as often as in FULL mode. There is a small probability (but not non-existent) that a power failure may cause database corruption in NORMAL mode. But in fact, in this case, it is very likely that your hard drive will no longer be used, or other irrecoverable hardware errors have occurred.
When set to synchronous OFF (0), SQLite continues directly without pausing after passing data to the system. If an application running SQLite crashes, the data will not be damaged, but the database may be damaged if the system crashes or accidentally powered down while writing data. On the other hand, some operations may be 50 times faster or even more when synchronous OFF.
In SQLite 2, the default value is NORMAL. and modified to FULL in 3.
4. Suggestions:
If there is a mechanism for regular backup and a small amount of data is acceptable, use OFF.
7. Memory mode
1. How to query:
PRAGMA temp_store;
Meaning of instructions: 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), the 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 a 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 memory.
4. Notes:
When changing the temp_store setting, all existing temporary tables, indexes, triggers and views will be deleted immediately.
5. Suggestions:
Use 2, i.e. memory mode.
Attached instruction list set:
Serial number |
instruction |
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 does not support it) |
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 |
8. Summary
SQLite'sPRAGMAThe command provides developers with flexible configuration options that can significantly improve database query and write performance. By adjusting cache size, synchronization mode, WAL mode,Index optimizationYou can customize and optimize SQLite for specific application scenarios. Reasonable usePRAGMACommands not only improve performance, but also ensure the stability and reliability of the database in high-load environments.
This is the end of this article about 7 suggestions for SQLite3 database access performance optimization. For more related SQLite3 performance optimization content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!