SQLite Optimization Method _sqlite

For example, insert 1 million data into the database, by default, if you are simply executing

Sqlite3_exec (db, "INSERT into name values ' lxkxf ', ';", 0, 0, &zerrmsg);

will open the database file 1 million times repeatedly, so the speed will certainly be slow. So we should use "transaction" for this situation.

It is as follows: Before executing the SQL statement and after the SQL statement has finished executing, add

rc = sqlite3_exec (db, "BEGIN;", 0, 0, &zerrmsg);

Execute SQL statement

rc = sqlite3_exec (db, "COMMIT;", 0, 0, &zerrmsg);



This will sqlite all the SQL statements to be executed in memory, and then wait for commit to write to the database one at a time, so that the database file is only turned off once, the efficiency of the natural greatly improved. There is a set of data comparisons:



Test 1:1000 Inserts

CREATE TABLE T1 (a integer, b integer, C VARCHAR (100));
INSERT into T1 VALUES (1,13153, ' Thirteen thousand one hundred Fifty ');
INSERT into T1 VALUES (2,75560, ' Seventy five thousand five hundred sixty ');
... 995 lines omitted
INSERT into T1 VALUES (998,66289, ' Sixty six thousand two hundred Eighty ');
INSERT into T1 VALUES (999,24322, ' Twenty four thousand three hundred Twenty ');
INSERT into T1 VALUES (1000,94142, ' Ninety four thousand one hundred Forty ');

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, ' Fifty nine thousand six hundred Seventy ');
... 24997 lines omitted
INSERT into T2 VALUES (24999,89569, ' Eighty nine thousand five hundred sixty ');
INSERT into T2 VALUES (25000,94666, ' Ninety four thousand six hundred Sixty ');
COMMIT;

SQLite 2.7.6:
0.914

SQLite 2.7.6 (nosync):
0.757




It is obvious that the use of transactions has greatly improved the efficiency of the database. But we also have to note that the use of transactions also has a certain cost, so the small amount of data operations can not be used, so as to avoid the cost of consumption.