Solve the slow data insertion problem of SQLite3

When you use sqlite3 to insert data, it takes about ms to insert each data entry. For batch import, You can import things to increase the speed. However, assume that your business is separated by several seconds.

When you use sqlite3 to insert data, it takes about ms to insert each data entry. For batch import, You can import things to increase the speed. However, assume that your business is separated by several seconds.

When you use sqlite3 to insert data, it takes about ms to insert each data entry. For batch import, You can import things to increase the speed. But assuming that your business inserts several data records every several seconds, Ms is obviously not allowed. The solution is to add the following line of code after calling the sqlite3_open function:

Sqlite3_exec (db, "PRAGMA synchronous = OFF;", 0, 0 );

The above solution seems to be a permanent cure. Why does the speed increase by adding the above Code lines? The online explanation is as follows:

Disk Synchronization

1. How to set:

PRAGMA synchronous = FULL; (2)

PRAGMA synchronous = NORMAL; (1)

PRAGMA synchronous = OFF; (0)

2. 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.

3. Suggestions:

If there is a regular backup mechanism and a small amount of data loss is acceptable, use OFF.

Note the bold words in red. Conclusion: If your data does not have high requirements on security and integrity, you can set it to 0. After all, it is only "the database may be damaged". The chances of damage are high, I do not know yet ...... I have not encountered any damage yet. I don't know when it will happen.