SQLite batch add data

Today, we encountered a situation where we inserted more than 1400 pieces of data into the SQLite database. It takes one or two minutes to complete the result each time.

After searching, the solution is found here, and the required time is less than 2 seconds!

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 ();

}

}

}

It took two minutes because I submitted the update transaction when executing the executenonquery method. By default, the update operation will automatically commit the transaction. That is to say, the next SQL statement is a transaction by default, if you do not understand, you can view the transaction isolation level.


The changed code puts all updated SQL statements in one transaction and submits the transaction only when the commit method is executed.


The communication with the database is completed twice, but the old method performs 100000 communications, while the new method only performs one communication.


Of course there is a difference in time ......

Today, we encountered a situation where we inserted more than 1400 pieces of data into the SQLite database. It takes one or two minutes to complete the result each time.

After searching, the solution is found here, and the required time is less than 2 seconds!

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 ();

}

}

}

It took two minutes because I submitted the update transaction when executing the ExecuteNonQuery method. By default, the update operation will automatically commit the transaction. That is to say, the next SQL statement is a transaction by default, if you do not understand, you can view the transaction isolation level.


The changed code puts all updated SQL statements in one transaction and submits the transaction only when the Commit method is executed.


The communication with the database is completed twice, but the old method performs 100000 communications, while the new method only performs one communication.


Of course there is a difference in time ......