How to efficiently use SQLite. Net (C #)

As a lightweight embedded database, SQLite is still very useful. Rain marks are highly recommended ~~~~~~

Today, a friend tested SQLite and concluded that the efficiency of SQLite was too low. It took 2 minutes to insert 1000 records in batches!
Below is the test code he sent me. My dizzy ~~~~~~ Using system. Data;
Using system. Data. Common;
Using system. Data. SQLite;

// Create a database file
File. Delete ("test1.db3 ");
Sqliteconnection. createfile ("test1.db3 ");

Dbproviderfactory factory = sqlitefactory. instance;
Using (dbconnection conn = factory. createconnection ())
{
// Connect to the database
Conn. connectionstring = "Data Source = test1.db3 ";
Conn. open ();

// Create a data table
String SQL = "CREATE TABLE [test1] ([ID] integer primary key, [s] Text collate nocase )";
Dbcommand cmd = conn. createcommand ();
Cmd. Connection = conn;
Cmd. commandtext = SQL;
Cmd. executenonquery ();

// Add Parameters
Cmd. Parameters. Add (CMD. createparameter ());

// Start timing
Stopwatch watch = new stopwatch ();
Watch. Start ();

// Insert 1000 records consecutively
For (INT I = 0; I <1000; I ++)
{
Cmd. commandtext = "insert into [test1] ([s]) values (?) ";
Cmd. Parameters [0]. value = I. tostring ();

Cmd. executenonquery ();
}

// Stop timing
Watch. Stop ();
Console. writeline (WATCH. elapsed );
}

Ah ~~~~ For a common sense error, I add a few lines of code (Add code mark "// <-------------------"). Using system. Data;
Using system. Data. Common;
Using system. Data. SQLite;

// Create a database file
File. Delete ("test1.db3 ");
Sqliteconnection. createfile ("test1.db3 ");

Dbproviderfactory factory = sqlitefactory. instance;
Using (dbconnection conn = factory. createconnection ())
{
// Connect to the database
Conn. connectionstring = "Data Source = test1.db3 ";
Conn. open ();

// Create a data table
String SQL = "CREATE TABLE [test1] ([ID] integer primary key, [s] Text collate nocase )";
Dbcommand cmd = conn. createcommand ();
Cmd. Connection = conn;
Cmd. commandtext = SQL;
Cmd. executenonquery ();

// Add Parameters
Cmd. Parameters. Add (CMD. createparameter ());

// Start timing
Stopwatch watch = new stopwatch ();
Watch. Start ();

Dbtransaction trans = conn. begintransaction (); // <-------------------
Try
{
// Insert 1000 records consecutively
For (INT I = 0; I <1000; I ++)
{
Cmd. commandtext = "insert into [test1] ([s]) values (?) ";
Cmd. Parameters [0]. value = I. tostring ();

Cmd. executenonquery ();
}

Trans. Commit (); // <-------------------
}
Catch
{
Trans. rollback (); // <-------------------
Throw; // <-------------------
}

// Stop timing
Watch. Stop ();
Console. writeline (WATCH. elapsed );
}

The execution takes 0.2 seconds. Is the gap too big?

Why is there such a big gap between simply enabling a transaction? Simple: SQLite starts a transaction for each operation by default. Therefore, at least 1000 transactions are started for 1000 inserts of the original code, and "transaction enabling + SQL Execution + transaction disabling" naturally takes a lot of time, this is why the transaction is so fast. In fact, this is the basic knowledge of database operations. It is important to note that the poor code efficiency is not half past one.

[Last modified by yujia, at 22:09:08,] Comment feed:Http://www.rainsts.net/feed.asp? Q = Comment & id = 363