SQLite Optimization Method

For example, if you insert 1 million data records to a database

Sqlite3_exec (dB, "insert into name values 'lxkxf', '24';", 0, 0, & zerrmsg );

It will repeatedly Open and Close database files for 1 million times, so the speed will of course be slow. Therefore, we should use "Transactions" in this case ".

The specific method is as follows: add

Rc = sqlite3_exec (dB, "begin;", 0, 0, & zerrmsg );

// Execute the SQL statement

Rc = sqlite3_exec (dB, "commit;", 0, 0, & zerrmsg );

In this way, SQLite will first cache all SQL statements to be executed in the memory, and then write them into the database at one time when the commit is executed. In this way, the database file is opened and closed only once, the efficiency is naturally greatly improved. There is a set of data comparison:

Test 1: 1000 Inserts

Create Table T1 (a integer, B integer, C varchar (100 ));
Insert into T1 values (1,13153, 'Thirteen thousand one hundred into Ty three ');
Insert into T1 values (2,75560, 'venty five thousand five hundred sixty ');
... 995 lines omitted
Insert into T1 values (998, 66289, 'sixty six thousand two hundred eighty nine ');
Insert into T1 values (999,24322, 'twenty four thousand three hundred twenty two ');
Insert into T1 values (1000,94142, 'Ninety four thousand one hundred forty two ');

SQLite 2.7.6:
13.061

SQLite 2.7.6 (nosync ):
0.223

Test 2: Use transaction 25000 Inserts

Begin;
Create Table T2 (a integer, B integer, C varchar (100 ));
Insert into T2 values (1,59672, 'entity ty nine thousand six hundred seventy two ');
... 24997 lines omitted
Insert into T2 values (24999,89569, 'hthty nine thousand five hundred sixty nine ');
Insert into T2 values (25000,94666, 'Ninety four thousand six hundred sixty six ');
Commit;

SQLite 2.7.6:
0.914

SQLite 2.7.6 (nosync ):
0.757

It can be seen that the database efficiency is greatly improved after transactions are used. However, we should also note that the use of transactions also has a certain amount of overhead, so you do not need to use operations with a small amount of data to avoid extra consumption.