Android_sqlite _ performance optimization skills

• Compared with the encapsulated contentprovider, the execution efficiency of original SQL statements is high, for example, the execution efficiency of rawquery and execsql statements is relatively high.

• When you need to modify multiple data records at a time, you can use the SQLite transaction method for batch processing. We define sqlitedatabase dB objects and the execution sequence is

DB. begintransaction ();

// SQL statement for adding, deleting, or modifying data

DB. settransactionsuccessful (); // The processing is successful.

DB. endtransaction (); // This sentence is very important. It tells the database that the processing is complete. At this time, the bottom layer of SQLite will execute specific data operations.

• Laying a good foundation for SQL statements is important for queries and the structure of allocation tables. Some traditional database optimization techniques are also practical, such as creating indexes, adding redundant fields, and querying by page.

• Do not forget to execute the cursor. Close () method when performing the cursor operation.

• Avoid subqueries. In some cases,SQLiteThe Database Engine stores the subquery results in temporary files, which prolongs the processing time.

The above 1, 3, 4, and 5 are all well understood, but at 2nd, why can transactions improve performance?

When we execute one (only one insert) insert statement without starting the transaction, the database adds the transaction for the statement by default. After the statement ends, will perform an automatic commit operation to refresh the data to the disk data file.

If there are multiple insert statements, the database performs an automatic commit operation for each insert statement when the transaction is enabled.

Because the commit operation will refresh the data to the disk data file, and the operation to write the data file to the disk is very time-consuming, because verification and Index Update are required.

Therefore, if a transaction is used to process multiple statements, only one commit is required, which greatly saves time and improves performance.