SQLite Speed Evaluation Code _sqlite

Today, a friend of the test SQLite, and then came to the conclusion that: SQLite efficiency is too low, batch insert 1000 records, incredibly time-consuming 2 minutes!
Here is the test code he sent me. I'm dizzy ~~~~~~.

Using System.Data;
Using System.Data.Common;
Using System.Data.SQLite;

Creating a Database File
File.delete ("test1.db3");
Sqliteconnection.createfile ("test1.db3");

DbProviderFactory factory = sqlitefactory.instance;
using (DbConnection conn = factory. CreateConnection ())
{
Connecting to a database
Conn. ConnectionString = "Data source=test1.db3";
Conn. Open ();

Creating data Tables
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 the timer.
Stopwatch watch = new stopwatch ();
Watch. Start ();

Insert 1000 consecutive records
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);
}



Using System.Data;
Using System.Data.Common;
Using System.Data.SQLite;

Creating a Database File
File.delete ("test1.db3");
Sqliteconnection.createfile ("test1.db3");

DbProviderFactory factory = sqlitefactory.instance;
using (DbConnection conn = factory. CreateConnection ())
{
Connecting to a database
Conn. ConnectionString = "Data source=test1.db3";
Conn. Open ();

Creating data Tables
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 the timer.
Stopwatch watch = new stopwatch ();
Watch. Start ();

Dbtransaction trans = conn. BeginTransaction (); <-------------------
Try
{
Insert 1000 consecutive records
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);
}

It takes 0.2 seconds to execute. Is that a little too big a difference?

Why is it that there is so much difference between simply enabling a transaction? Quite simply, SQLite the default to start a transaction for each operation, then the original code 1000 inserts to open at least 1000 transactions, "Transaction Open + SQL execution + transaction shutdown" Naturally consumes a lot of time, which is also the reason why so fast after the start of the transaction. In fact, this is the basic knowledge of database operations, we should remember that bad code efficiency is not the 1:30 point.