With the increasing amount of app information, it is no longer desirable to obtain data from the network every time, and the application of local database has become more and more common. When it comes to mobile, SQlite has to be mentioned. With the extensive use of local databases, FMDB has also attracted the attention of many programmers. FMDB is a great library that wraps SQLite in a more MVC friendly way, making it very convenient to use. Looking back, the amount of data in mobile DB is also increasing. It is no longer uncommon to have 100,000 pieces of data. For hundreds of thousands of pieces of data, the optimization of reading and inserting is very important for the optimization of user experience.

To optimize the

For DB without any optimization, hundreds of thousands of data reads, at least tens of seconds or even longer, even with loading pages and other buffers, are not enough.

1) Use database transactions

By default, FMDB performs transactions on each SQLite statement. Transactions are a security mechanism for rolling back data if an operation on a database fails. However, it is very time-consuming to execute transactions every time. In this case, when a large number of data operations are performed, comprehensive transactions can be performed after all statements are executed. Ex. :

-(void)shiwucaozuo { [_dataBase inTransaction:^(FMDatabase *db, BOOL *rollback) { for (int i = 0; i<100000; BOOL a = [DB executeUpdate:SQL]; if (! A) {*rollback = YES; return;}}}]; }Copy the code

In this way, the display control can effectively control the time, and optimization of insert and other operations can have an order of magnitude of growth optimization. Tip: After the data is queried, it can be loaded into the memory in the case of a small amount of data, avoiding every database query operation. For example, once the data is converted to a model, it can be stored in the dictionary as key-value, which will reduce DB operations faster the next time it is used

2) Use the Statement cache mechanism of FMDB

The cache mechanism of FMDB is to query the cache based on SQL as the key after DB operation. If there is no cache, it is added to the cache and two queries are performed. You can use FMDB’s cache to cache SQL. It is recommended to cache SQL without parameters and then directly fetch SQL statements from cache using the id returned from cache when preparing to use SQL statements. In this way, only one query operation is required. Caching only SQL statements without parameters helps improve query efficiency.

-(void)cacheSQl:(FMDatabase*) withSql:(NSString*)sql{
    id = [db cache:sql]'/ / db need to cache, next time you can pass the id back SQL.} can be directly obtained by [db executeUpdataWithStatementID] SQL.Copy the code

3) Change the log mode

Logging mode

SQLite has two main log modes: DELETE and WAL. Other log modes, such as TRUNCATE, PERSIST, and MEMORY, operate in the same way as the DELETE mode and are not expanded in detail. In DELETE mode, Shadow paging is used. In DELETE mode, data pages before changes are recorded in logs. In WAL mode, logs record changed data pages. When the transaction is committed, the DELETE mode will flush the log and DB files. After the transaction is successfully committed, the log files will be cleared. In WAL mode, log files are flushed to complete the submission process. When do data files update in WAL mode? The concept of checkpoint is introduced here. The checkpoint periodically overwrites the old page in the DB file with the new page in the log. The parameter wal_autocheckpoint is used to control the checkpoint timing and balance the read and write. In DELETE mode, write transactions update db-page directly and write old-page to the log, while read transactions read db-page directly because updates of all committed transactions are saved in db-Page. After the transaction is committed, the log file is deleted directly. If the transaction needs to be rolled back, the db-page is overwritten in the old-page log and the original content is restored. In WAL mode, write transactions write updates to log files without updating the DB-Page, and commit transactions without affecting the DB-Page, just persisting logs. If the transaction rolls back, do not write the log to the file. Since the latest data is in the log file, how do I get the latest data? WAL pattern achieves this through end-mark(transaction commit point). When the transaction starts, the system scans the log file to obtain the latest end-mark. When reading data, the system checks whether the page exists in the WAL log file or not, because the same page must be newer in the WAL file than in the DB file. If it exists, use it; otherwise, get the specified page from the db file. This process is slow in terms of flow, because in extreme cases, wal and DB files need to be scanned for each read. To improve performance, WAL mode has a WAL -index file. This file records the page number and the offset of the page in WAL file. WAL -index file is implemented by shared cache. So determining whether a page exists in a WAL file is essentially a memory read. Wal -index is stored in a hash table. Therefore, the query efficiency is high. Unlike traditional DBMSS, SQLite logs are essentially dirty pages, and redo logs are essentially db-pages overwritten with WAL log pages. This approach is simpler and more space-wasting, because a page is 1K, even if only 1byte is updated, it will result in 1K logging. We can change the log mode when the database is set up. Advantages of WAL log mode: 1) Read and write can be concurrent without blocking 2) Only one WAL file has significant performance advantages.

4) Adjust the time of data change index establishment.

During table creation, indexes are often required to improve search efficiency. However, index creation, especially when inserting a large amount of data, requires a large amount of performance loss. Therefore, you can manually add index indexes after the full pull data is completed.

5) Write synchronous (not recommended)

In SQLite, the database configuration parameters are implemented by pragMA, where the synchronous option has three optional states, full, Normal, and OFF. This blog post and the official documentation describe the Settings of these three parameters in detail. In short, full is the slowest, but keeps data safe from power outages and system crashes, while OFF can speed up some operations on the database, but can destroy the database if the system crashes or fails. In SQLite3, this option defaults to full, and it would be more efficient if we changed it to off before inserting more data. There is no need to set SQLite to full if you are just using SQLite as a temporary database. In the code, you do this by inserting the following statement directly after opening the database:

sqlite3_exec(db,"PRAGMA synchronous = OFF; ",0,0,0); 
Copy the code

When synchronous is set to FULL (2), the SQLite database engine pauses in an emergency to make sure data has been written to disk. This ensures that the database will not be damaged after a reboot in the event of a system crash or power failure. FULL Synchronous is safe but slow. When synchronous is set to NORMAL, the SQLite database engine stalls in most emergencies, but not as often as in FULL mode. In NORMAL mode, there is a small probability (but not non-existent) that a power failure will cause database corruption. However, in this case, it is likely that your hard drive has become unusable or some other unrecoverable hardware error has occurred. When synchronous OFF (0) is set, SQLite resumes without pausing after passing data to the system. If the application running SQLite crashes, the data will not be corrupted, but the database can be corrupted in the event of a system crash or unexpected power failure while writing data. On the other hand, some operations can be 50 times faster or more during synchronous OFF. But there are risks.

6) Query optimization FTS

FTS virtual table optimization for query is obvious. FTS3 and FTS4 are modules for SQLite virtual tables that allow the user to perform a full-text search of a set of documents from the most common () methods, but find many inconsistencies in the use of the following:

CREATE VIRTUAL TABLE table1 USING fts4(content TEXT) */ fts4 TABLE /* CREATE TABLE IF NOT EXISTS table1(content TEXT); /* Common table */Copy the code

Of course in FMDB:

NSString *storePath = @"Db path";
FMDatabase *db = [FMDatabase databaseWithPath:storePath];

[db open];

FMSimpleTokenizer *simpleTok = [[FMSimpleTokenizer alloc] initWithLocale:NULL];

[db installTokenizerModule];
[FMDatabase registerTokenizer:simpleTok withKey:@"simple"];

[db executeUpdate:@"CREATE VIRTUAL TABLE works_test USING fts4(id, title, title_tr, content, content_tr, dynasty, dynasty_tr, author, author_tr, tokenize=fmdb simple)"];

[db close];
Copy the code

Insert and other operations are the same as before. But instead of using #like#, which we usually like to use, it’s # match. of course, it’s said to be 1000 times faster than like.

SELECT * FROM works_test WHERE works_test MATCH 'something';
Copy the code

7) Finally about PRAGMA command usage

PRAGMA statements are SQL extensions to SQLITE data. They are unique to SQLITE and are used to modify operations in SQLITE libraries or data queries. It makes the request in the same form as SELECT, INSERT, etc., but with a few important differences:

  1. Specific PRAGMA statements may be removed, and new PRAGMA statements may be added in new versions. Therefore, backward compatibility is not guaranteed.
  2. Unknown PRAGMA commands do not have error messages, it is simply ignored.
  3. Some PRAGMA only work in the compile phase of SQL, not in the execution phase. This means that if you use THE C API sqlite3_prepare(), sqlite3_step(), sqlite3_Finalize (), the pragma command may only be run in the prepare() call, but not in the latter two apis. Alternatively, pragma might run while sqlite3_step() is executed. Which stage is executed depends on the pragma itself and which release version of SQLite it is.
  4. Pragma commands are unique to SQLite and are virtually impossible to maintain compatibility with other databases.

Sqlite has some useful pragma commands: auto_vacuum automatic_index cache_size case_sensitive_like checkpoint_fullfsync collation_list compile_options Count_changes ¹ database_list default_cache_size¹ EMPty_result_callbacks ¹ Encoding foreign_key_list foreign_keys Freelist_count full_column_names¹ fullfsync ignore_check_constraints INCREmental_VACUUM INDEx_info index_list integrity_check journal_mode journal_size_limit legacy_file_format locking_mode max_page_count page_count page_size Parser_trace ² quick_check READ_uncommitted recursive_triggers Reverse_unordered_SELECTS schemA_version secure_DELETE Short_column_names ¹ synchronous table_info temp_store temp_store_directory¹ user_Version vdBE_listing ² vdBE_trace ² Wal_autocheckpoint wal_checkpoint writable_SCHEMA Some of the ones marked 1 to the right seem to have been obsoleted. 2 is only used for debug, and only if SQLite is built under the precompiled macro SQLITE_DEBUG.

Let’s see how these commands are used:

  1. PRAGMA auto_vacuum; PRAGMA auto_vacuum = 0 or NONE | 1 or FULL or INCREMENTAL | 2; Here, 0 and NONE mean the same thing. The default value is 0, which disables auto VACUUM. Unless the SQLITE_DEFAULT_AUTOVACUUM macro is defined at compile time. When data is deleted, the database size does not change. Unused database file pages are added to freelist for future reuse. At this point, using the VACUUM command, you can recreate the entire database to reclaim unused disk space. With a value of 1, all freelist pages are moved to the end of the file, and the file is truncated each time a transaction commits. Note that automatic vacuum only truncates a freelist page from a file and does not perform operations such as fragmentation, that is, it is not as thorough as the vacuum command. In fact, automatic vacuum makes more debris. Automatic VACUUM is useful only if the database stores some additional information and it allows each database page to track its reference pages. It must be enabled without creating any tables. Auto-vacuum cannot be enabled or disabled after a table has been created. A value of 2 indicates incremental VACUUM, which means that instead of automatic vacuum every time a transaction is committed, a separate INCREmental_VACUUM statement needs to be called to trigger auto-vacuum. The database can be switched between 1 and 2 vacuum modes. But you can’t switch from None to Full or incremental. To switch, either the database is completely new (without any tables) or after vacuum is run separately. To change automatic VACUUM mode, first execute the auto_VACUUM statement to set the new mode, and then call VACUUM to reorder the database. The auto_VACUUM statement with no arguments returns the current auto_VACUUM mode value.

  2. PRAGMA automatic_index; PRAGMA automatic_index = boolean; Query, set, or clear automatic indexing functions. The default value is true (1).

  3. PRAGMA cache_size; PRAGMA cache_size = ; Queries or modifies the maximum number of database pages that can fit in an open database memory. The default value is 2000. This only changes the cache size in the current session. When the database is reopened, the default value will be restored. You can use default_cache_size to set the cache size for all sessions

  4. PRAGMA case_sensitive_like=boolean; The default behavior is to ignore the case of ASCII characters. ‘a’ LIKE ‘a’ will be true. When case_SENSItive_LIKE is disabled, the default LIKE behavior is used. When enabled, it is case sensitive.

  5. PRAGMA checkpoint_fullfsync PRAGMA checkpoint_fullfsync=boolean; Query or set the flag value of fullfsync. If this value is set, the F_FULLFSYNC synchronization method is called during the checkpoint operation. The default value is off. Only Mac OS-X operating systems support F_FULLFSYNC. In addition, if fullfsync is set, the F_FULLFSYNC synchronization method will be used in all sync operations, regardless of the checkPOINT_fullfsync flag.

  6. PRAGMA collation_list; Returns all sort orders for the current database connection definition.

  7. PRAGMA compile_options; This is to like, returning all the precompiled macros used when compiling SQLITE. Of course, prefixes starting with “SQLITE_” are ignored. It is actually returned by calling the sqlite3_compileoption_get() method.

  8. PRAGMA count_changes; PRAGMA count_changes=boolean; The command is disabled. Just to maintain backward compatibility. If you do not set this value, INSERT, UPDATE, and DELETE statements will not return many rows of changed data. In fact, sqlite3_changes() gets the number of rows changed.

  9. PRAGMA database_list; Returns a list of databases associated with the current database connection.

  10. PRAGMA default_cache_size; PRAGMA default_cache_size = Number-of-pages; Set the default cache SIE, in pages. Unfortunately, that order will also be scrapped.

  11. PRAGMA empty_result_callbacks; PRAGMA empty_result_callbacks = boolean; For backward compatibility only. If this flag value is cleared, the callback function provided by sqlite3_exec(), which returns 0 or more rows of data, will not be fired.

  12. PRAGMA encoding; PRAGMA encoding = “UTF-8”; PRAGMA encoding = “UTF-16”; PRAGMA encoding = “UTF-16le”; PRAGMA encoding = “UTF-16be”; The default value is UTF-8. Using the attach command will require the same character set encoding as the main database, and will fail if the new database encoding is different from main.

  13. PRAGMA foreign_key_list(table-name); Returns the list of foreign keys

  14. PRAGMA foreign_keys; PRAGMA foreign_keys = boolean; Query sets or clears foreign key limits. Foreign key limits are valid only when BEGIN or SAVEPOINT are not in the PENDING state. Changing this setting affects the execution of all prepared SQL statements. As of 3.6.19, the default FK enforcement limit is OFF. That is, foreign key dependencies are not enforced.

  15. PRAGMA freelist_count; Returns the number of unused pages in the database file

  16. PRAGMA full_column_names; PRAGMA full_column_names = boolean; deprecated.

    1. If there is an AS clause, the column name is alias after AS
    2. If the result is a plain expression rather than the column name of the source table, the text of the expression is used
    3. If the short_column_NAMES switch is set to ON, the source table column name is used without the prefix of the table name
    4. If both switches are set to OFF, rule 2 applies.
    5. The resulting COLUMN is a combination of source columns in the active TABLE: table.column

  17. PRAGMA fullfsync; PRAGMA fullfsync = boolean; The default value is OFF. Only MAC OS supports F_FULLFSYNC

  18. PRAGMA ignore_check_constraints = boolean; Whether to force the check constraint. The default value is off

  19. PRAGMA incremental_vacuum(N); N pages removed from freelist. Used to set this parameter. Truncate the same number of pages each time. This command is valid only in the mode of auto_vacuum=incremental. If the number of pages in a freelist is less than N, or if N is less than 1, or if N is ignored entirely, then the entire freelist is cleared.

  20. PRAGMA index_info(index-name); Get the named index information.

  21. PRAGMA index_list(table-name); Gets information about the index associated with the target table

  22. PRAGMA integrity_check; PRAGMA integrity_check(integer); Perform an integrity check of the entire library, looking for out-of-order records, missing pages, broken indexes, and so on.

PRAGMA journal_mode; PRAGMA database.journal_mode; PRAGMA journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF PRAGMA database.journal_mode = DELETE | TRUNCATE | PERSIST | | MEMORY WAL | OFF to set up the database journal_mode. DELETE is the default behavior. In this mode, the Journal file is deleted each time a transaction terminates, which causes the transaction to commit. TRUNCATE mode, by truncating the rollback journal to 0 instead of deleting it. In most cases, it is faster than DELETE mode (because files are not deleted). In PERSIST mode, instead of deleting the ROLLBACK Journal, at the end of each transaction, you simply populate the head of the journal with zeros, which prevents other database connections from rolling back. This mode is an optimization on some platforms, especially when deleting or truncating a file is more expensive than overwriting the first block of the file. MEMORY mode, which stores only rollback logs into RAM, saves disk I/O, but comes at the cost of stability and integrity. If a crash occurs, the database may be corrupted. WAL mode, also known as write-ahead log replaces Rollback Journal. This schema is persistent, spans multiple data connections, and remains in effect after the database is reopened. This mode is only available after 3.7.0. (After testing, it generates two files:.shm and.wal) in OFF mode, so there is no transaction support. Also note that for memory databases, there are only two modes: memory or OFF. Also, if there are currently active transactions, the transaction mode is not allowed to change.

  1. PRAGMA journal_size_limit PRAGMA journal_size_limit = N ; If “PRAGMA locking_mode= EXCLUSIVE” or “PRAGMA journal_mode= PERSIST” are used, the journal file will remain in the filesystem after committing the transaction. This may increase efficiency, but it also consumes space. A large transaction, such as VACUUM, consumes a lot of disk space. This setting limits the size of the journal file. The default value is -1.

  2. PRAGMA legacy_file_format; PRAGMA legacy_file_format = boolean; If the value is ON, the 3.0.0 file format will be used, and if it is off, the latest file format will be used, which may cause older versions of SQLite to fail to open the file. The value is off the first time the sqlite3 database in the new file format is opened. But the default value will be on.

  3. PRAGMA locking_mode; PRAGMA locking_mode = NORMAL | EXCLUSIVE the default value is NORMAL. The database connection releases the file lock at the end of each read or write transaction. In EXCLUSIVE mode, the connection will never release the file lock. In this mode, the shared lock is acquired and held on the first read and the exclusive lock is acquired and held on the first write. Release exclusive locks that will only be released if the database file is accessed (read or write) again if the database connection is closed or the lock mode is changed back to NORMAL. Simply setting it to NORMAL is not enough; the exclusive lock will only be released on the next access. There are three reasons to set the lock mode to EXCLUSIVE

    1. The application needs to prevent other processes from accessing the database files
    2. The number of system calls to the file system is reduced, resulting in a slight performance degradation
    3. WAL logging mode can be used in EXCLUSIVE mode without the need for shared memory. If a database name is specified, only the target database takes effect. Such as: PRAGMA main. Locking_mode = EXCLUSIVE; If the database name is not specified, it takes effect for all open databases. Temp or Memory databases always use exclusive lock mode. WAL logging mode is exclusive for the first time. After that, the lock mode cannot be changed until WAL logging mode is exited. If WAL logging mode is NORMAL for the first time, the lock mode can be changed without exiting WAL.

  4. PRAGMA max_page_count; PRAGMA max_page_count = N; Queries or sets the maximum number of pages in a database file

  5. PRAGMA page_count; Returns the number of pages in the database file

  6. PRAGMA page_size; PRAGMA page_size = bytes; Query or set the page size of a database file, which must be a power of 2 and be between 512 and 65536. When the database is created, a default size is given. The page_size command immediately changes the page size (if the database is empty, that is, if no tables have been created). If the new size is specified between running VACUUM commands and the database is not in WAL log mode, VACUUM will adjust the page size to the new size (there should be no restrictions on creating tables) SQLITE_DEFAULT_PAGE_SIZE The default value is 1024. The maximum default page size is 8192. On Windows, the default page size may sometimes be greater than 1024, depending on GetDiskFreeSpace() to get the true setting sector size.

  7. PRAGMA parser_trace = boolean; Used for debugging.

  8. PRAGMA quick_check; PRAGMA Quick_check (INTEGER) is similar to Integrity_check, but omits checking that index content matches table content.

  9. PRAGMA read_uncommitted; PRAGMA read_uncommitted = boolean; Read uncommitted switch. The default transaction isolation level is serializable. Any process or thread can set the read uncommitted isolation level, but SERIALIZABLE is still used except for those connections that share the cache of a page and table schema.

  10. PRAGMA recursive_triggers; PRAGMA recursive_triggers = boolean; All statement execution is affected. Before 3.6.18, this switch was not supported. The default value is off.

  11. PRAGMA reverse_unordered_selects; PRAGMA reverse_unordered_selects = boolean; When this switch is turned on, a SELECT statement without order by outputs results in the opposite order.

  12. PRAGMA schema_version; PRAGMA schema_version = integer ; PRAGMA user_version; PRAGMA user_version = integer ; Schema and user version are 32-bit integers (big-endian representations) at 40,60 bytes in the database file header. Schema versions are maintained internally by SQLite, and this value is increased when the schema changes. Changing this value explicitly is dangerous. The User version can be used by applications.

  13. PRAGMA secure_delete; PRAGMA database.secure_delete; PRAGMA secure_DELETE = Boolean PRAGMA database.secure_delete = Boolean When set to ON, the deleted content is overwritten with 0. The default value is determined by the macro SQLITE_SECURE_DELETE. That’s OFF.

  14. PRAGMA short_column_names; PRAGMA short_column_names = boolean; deprecated.

  15. PRAGMA synchronous; PRAGMA synchronous = 0 | OFF | 1 | NORMAL | 2 | FULL; Query the value of the sync flag. The default value is FULL.

  16. PRAGMA table_info(table-name); Returns basic information about the table

  17. PRAGMA temp_store; PRAGMA temp_store = 0 | DEFAULT | 1 | FILE | 2 | MEMORY; Query or set the temp_store parameter value. SQLITE_TEMP_STORE PRAGMA temp_store Storage used forTEMP tables 0 any file 1 0 file 1 1 file 1 2 memory 2 0 memory 2 1 file 2 2 memory 3 any memory

  18. PRAGMA temp_store_directory; PRAGMA temp_store_directory = ‘directory-name’; Sets or changes the directory location of temp_store. Deprecated.

  19. PRAGMA vdbe_listing = boolean; For the DEBUG

  20. PRAGMA vdbe_trace = boolean; For the DEBUG

  21. PRAGMA wal_autocheckpoint; PRAGMA wal_autocheckpoint=N; Sets the interval (in pages) for automatic WAL checkpoints. The default value is 1000.

  22. PRAGMA database.wal_checkpoint; PRAGMA database.wal_checkpoint(PASSIVE); PRAGMA database.wal_checkpoint(FULL); PRAGMA database.wal_checkpoint(RESTART);

  23. PRAGMA writable_schema = boolean; When set to ON, the SQLITE_MASTER table can perform CUD operations. It’s dangerous!!