SQLite speed evaluation code

Source: Internet
Author: User


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 he sent me Code . 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.