Discussion on the impact of whether a transaction is enabled on the time required for data insertion in the database

According to the sqlite3 api, during subsequent tests, we found that for sqlite3, whether the transaction is enabled or not only inserts pieces of data, and the time gap is very obvious.

According to the sqlite3 api, during subsequent tests, we found that for sqlite3, whether the transaction is enabled or not only inserts pieces of data, and the time gap is very obvious.

Recently, I was engaged in the secondary development of sqlite3. When I saw the transaction in the API, I came up with the idea of testing whether the transaction was enabled or not to affect the time required for data insertion, according to the sqlite3 api, during subsequent tests, we found that for sqlite3, whether the transaction is enabled or not only inserts pieces of data, and the time gap is very obvious. let's first talk about the test environment: ubuntu 12.04, sqlite3 3.7.14.1, to test the insertion of pieces of data.

The test code when the transaction is not started is as follows:

Int insert_no_trans ()
{

Printf ("<---------------------> \ n"
"Go into function insert_no_trans () \ n ");

Int j = 0;
For (j = 0; j <10000; j ++)
{
Sprintf (SQL, "INSERT INTO [dev] ([id], [name], [age]) \
Values (% d, '% s', % d) ", j," JGood ", j );
If (SQLITE_ OK! = Sqlite3_exec
(Conn, SQL, 0, 0, & err_msg ))
{
Fprintf (stderr, "insert error: % s \ n", err_msg );
Exit (EXIT_FAILURE );
}

}
Printf ("INSERT all succussfully! \ N ");

Printf ("function insert_no_trans () end. \ n"
"<---------------------> \ N ");

Return EXIT_SUCCESS;
}


The test code for starting a transaction is as follows:
Int insert_with_trans ()
{

Printf ("<---------------------> \ n"
"Go into function insert_with_trans () \ n ");

Sqlite3_exec (conn, "begin;", 0, 0, 0); // start the transaction

Int j = 0;
For (j = 0; j <10000; j ++)
{
Sprintf (SQL, "INSERT INTO [dev] ([id], [name], [age]) \
Values (% d, '% s', % d) ", j," JGood ", j );
If (SQLITE_ OK! = Sqlite3_exec (conn, SQL, 0, 0, & err_msg ))
{
Is_succeed = false; // set the identifier to false upon failure.
Fprintf (stderr, "insert error: % s \ n", err_msg );
Break;
}

}

If (is_succeed)
Sqlite3_exec (conn, "commit;", 0, 0, 0); // submit the transaction
Else
{
Sqlite3_exec (conn, "rollback;", 0, 0, 0); // roll back the transaction
Exit (EXIT_FAILURE );
}

Printf ("INSERT all succussfully! \ N ");

Printf ("function insert_with_trans () end. \ n"
"<---------------------> \ N ");

Return EXIT_SUCCESS;
}

The test result is very disappointing. I used the linux time command to test the time. When the transaction is enabled, the time for inserting 1 million data records is only 0.4 s, in the case that the transaction is not started, because the time is too long, it is not completed after execution, according. the size of the db file and the time used are estimated. Assuming that the same number of data records are inserted per unit time, the estimated time is as high as 18 minutes. This is no longer an order of magnitude. google has this problem, and someone on the Internet has explained this. If transactions are not enabled, sqlite inserts a data record each time, write it to the disk once. During the entire execution process, I also observed that the hard disk lights were bright during the execution of the program when transactions were not started, which also proves this. When starting a transaction, it is necessary to execute an I/O operation after all the data is processed. The time is naturally very fast.