| SQLite research-Performance Optimization Many people use it directly, but do not notice that SQLite also has configuration parameters, so you can adjust the performance. Sometimes, the results will have a great impact. It is mainly implemented through The Pragma command. For example, space release, Disk Synchronization, and cache size. Do not open. As mentioned above, vacuum is very inefficient! Pragma auto_vacuum;
Pragma auto_vacuum =0 | 1; Query or set the auto-vacuum tag of the database. 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. When auto-vacuum is enabled, 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. 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. It is recommended to change to 8000 Pragma cache_size;
Pragma cache_size =Number-of-pages; Query or modify the number of database files stored in memory by SQLite. 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. 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, useDefault_cache_sizePragma. Open. Otherwise, an error occurs when searching for a Chinese string. Pragma case_sensitive_like;
Pragma case_sensitive_like =0 | 1; The default behavior of the like operator is to ignore Latin1 characters in case. Therefore, by default'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'False'A' like 'A'Still true. Open. Easy to debug 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. The number of rows returned does not include the number of rows generated by the trigger, such as insertion, modification, or deletion. Pragma page_size;
Pragma page_size =Bytes; Query or set the page-size value. 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. If a regular backup mechanism is available and a small amount of data is lost, use off. Pragma synchronous;
Pragma synchronous = full;(2)
Pragma synchronous = normal;(1)
Pragma synchronous = off;(0) Query or change the settings of the "synchronous" tag. The first form (query) returns an integer. 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. Use 2, memory mode. Pragma temp_store;
Pragma temp_store = default;(0)
Pragma temp_store = file;(1)
Pragma temp_store = memory;(2) Query or change"Temp_store"Parameter settings. 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. When memory (2) is set, temporary tables and indexes are stored in the memory. 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 the temp_store settings are changed, all existing temporary tables, indexes, triggers, and views will be deleted immediately. After testing, through the above adjustments in BBS applications, the efficiency can be improved by more than twice. |
