Stepping Pit (Running) pits (zssure): Analysis of operation efficiency of SQLite database

Background:

In the past, most of the database operations were in the use phase, and did not really consider the efficiency of database SQL statement execution. In response to other members of the project team in the near future, assist in manually modifying the database. In order to improve the efficiency of writing a C # test project related operations. Due to the large amount of database records (more than 10K), in the process of writing a program to find different operational efficiency is very far, the record is as follows, to be followed by the time to further analyze the various databases of SQL statement execution efficiency.
The database modification requirements related to delete, modify, add a variety of operations, in order to compare the execution efficiency of the program in various ways, only select the "delete" Operation for analysis. Details are as follows:

For loop-by-delete:

At first, the most straightforward idea was to delete the qualifying records one after the For loop, as shown in the following code:

The following results are performed:

"Knowledge Point Supplement": during the use of the loop operation, you can see the temporary files that generated the xxx.db3-journal in the database files directory, and the size is changing at any time. ( See Blog for a description of the journal file )journal is a staging document that SQLite uses for rollback operations , and when a database write fails, the journal document can be restored to the data before it was changed. By observing the journal file found in the native directory, its size is changed at any time so it can be guessed that every step of the loop in the code is opened and written to the database, resulting in time-consuming exceeding the tolerance limit.

Using transactions (Transaction):

SQLite defaults to start a transaction for each operation , that is, each delete in the loop code above and each time a select has started a transaction, as "Transaction open +sql execution + transaction shutdown" is a natural time consuming. Instead of manually explicitly calling sqlitetransaction to put all SQL operations in the loop into the same transaction, only one transaction can be opened and closed at a time. ( See blog post )
In addition, SQLite database is essentially a disk file , so all the database operations will be converted to the operation of the file, and the frequent file operations will be a time-consuming I/O process, greatly affecting the database access speed.
The code to explicitly call transaction is as follows:

The execution effect is:

This shows that efficiency compared to the former "violence" mode has a significant number of levels of ascension, and in the program execution process does not see the xxx.db3-journal temporary file generation.

Use the like query + combine sql:

Analysis of the above operation process can be found, the previous operation is divided into two parts: the first step, select query to meet the requirements of the data to be deleted; Second, use the For loop to delete the results of the previous select. These two operations have opened up redundant list variables, and repeated operation of the SQLite database files, so the efficiency of execution has yet to be improved.
Here we use like for conditional queries, and then use the combined SQL statement to directly delete the result that satisfies the condition, the code is as follows:

The following results are performed:

This shows that on the basis of the use of transaction transaction, through improving the overall process of the program to reduce the number of open and close the database, the efficiency has been further improved. Comparing the above three operating efficiencies, the results are as follows:

Summarize:

With the advent of ORM technology, the operation of the database is becoming more and more simple, and the use of Telerik OpenAccess for database related operations has been introduced before. It is because of the emergence of a number of new technologies in the actual process of the project to pay less attention to the lowest level, the most basic execution efficiency, for a single SQL statement, one-time operation often do not take any consideration, directly using the most primitive "savage" way. In addition, because most of the previous projects are stand-alone version, there will be no large number of users in the Web application concurrency, so the performance of the database is not enough to feel deep. With the advent of cloud computing, big data, and distribution, database technology, especially execution efficiency, is becomingmore important, "Remember" to be more attentive in this regard.




[Email protected]
Date: 2015/08/16

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Stepping Pit (Running) pits (zssure): Analysis of operation efficiency of SQLite database