SQLite batch insert Optimization Method and sqlite Optimization

SQLite batch insert Optimization Method and sqlite Optimization
SQLite databases are essentially files on a disk. Therefore, all database operations are actually converted to file operations, and frequent file operations will be a very good process, it will greatly affect the speed of database access. 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 caches all the SQL statements to be executed in the memory, then, the database is written to the COMMIT at a time, so that the database file is only opened and closed once, and 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.

internalstaticvoid FastInsertMany(DbConnection cnn){using (DbTransaction dbTrans = cnn.BeginTransaction()){using (DbCommand cmd = cnn.CreateCommand()){　　　　try　　 {cmd.CommandText = "INSERT INTO TestCase(MyValue) VALUES(?)";DbParameter Field1 = cmd.CreateParameter();cmd.Parameters.Add(Field1);for (int n = 0; n < 100000; n++){Field1.Value = n + 100000;cmd.ExecuteNonQuery();}}　　dbTrans.Commit();　　　}　　　catch　　　{　　　　　dbTrans.RollBack();　　　}}}