SQLite Optimization in Android development

About the optimization of SQLite, the first is to be able to use the SQL Statement batch processing, do not single-stroke operation, the cursor is more can not use. For example, a batch of delete/update, assembly of conditions into SQL statements, will be more efficient than the use of cursor one of the search-and-delete efficiency is much higher ( several years ago, the use of stored procedures instead of a single-pen operation, a batch calculation time from one day to less than a few minutes, reference ). Next is the optimization of the operation: use the transaction for more insert/update operations, and use the index if the select operation is large.

Combined with the current work, find the optimization for the operation, the following article can be translated to archive. The following is the text:

SQLite has a simple SQL interface and is known for its low memory footprint. Today, SQLite has been widely used in the development of Android and iOS. This article focuses on how to optimize the performance and resource usage of SQLite in Android applications.

1, using transactions (Transaction)

By default, each SQL statement is wrapped in a completely new transaction, such as executing a basic database operation such as INSERT, which is executed in a newly created transaction. It is certainly wise to let SQLite do its own transaction management at a time when only one database operation is needed. But if there is a lot of work to do at one time, such as loop call Insert Add, it is too expensive, because each operation to reopen, write, and then close the journal file, this file is temporarily used to save data operation of intermediate results, details see here (Reference).

This can be avoided if you explicitly use transactions with BEGIN transaction and end transaction before and after a series of SQL statements. For those that do not change the data, such a way can also speed up (as if the operation of a single pen in the database operation efficiency will be much lower than the batch operation, if the SQL statement can be done, it is not possible to use the cursor to operate).

Note: In addition to initiating a transaction, you must also be responsible for the commit and rollback operations on the transaction.

Begin TRANSACTION and end TRANSACTION can be used in Android app development in a way similar to the following:

[Java]View Plaincopyprint?

1. <span style="Font-family:microsoft Yahei;"  >db.begintransaction ();
2. try{
3. For (int i =0; i< LENGTH; i++,sequencenum++)
4. {
5. //Execute SQL
6. }
7. Db.settransactionsuccessful (); //marks a commit
8. }
9. finally{
10. Db.endtransaction ();
11. }</span>

2. Using the Index

If you do not use the index in the database, when you use a map query (projection query) search in a data table that is not sorted, there is no avoidable need to perform a full sequence lookup. This situation is usually not a problem, and each database, including SQLite, indexes the dataset to reduce the time to find.

The index maintains the order of a column or columns in a table, so that you can quickly navigate to a set of values without sweeping through the table. All index information is stored in a separate index table, so there is extra space to occupy, but absolute value, especially when you are doing a lot of reading and searching in the database.

SQLite automatically creates an index for each unique field, including the primary key (Primary key) field, or it can be created with the CREATE index displayed.

Note: If your query is too complex to use the index you created, then you need to think about the structure of your database.

3. Using qualifiers in the where branch

If a string is used to stitch the where of the SQL statement, use SQLite's query operation to take '? ' To compile the query. Here are some of its benefits:

A. It is beneficial for SQLite to cache these queries and indexes.

B. You can avoid reaching the upper limit of the SQLite cache. Using a string to stitch a where query, each of which is considered a different query, makes it easy to reach the upper limit of the cache.

C. Illegal SQL injection can be avoided.

About the optimization of SQLite, the first is to be able to use the SQL Statement batch processing, do not single-stroke operation, the cursor is more can not use. For example, a batch of delete/update, assembly of conditions into SQL statements, will be more efficient than the use of cursor one of the search-and-delete efficiency is much higher ( several years ago, the use of stored procedures instead of a single-pen operation, a batch calculation time from one day to less than a few minutes, reference ). Next is the optimization of the operation: use the transaction for more insert/update operations, and use the index if the select operation is large.

Combined with the current work, find the optimization for the operation, the following article can be translated to archive. The following is the text:

SQLite has a simple SQL interface and is known for its low memory footprint. Today, SQLite has been widely used in the development of Android and iOS. This article focuses on how to optimize the performance and resource usage of SQLite in Android applications.

1, using transactions (Transaction)

By default, each SQL statement is wrapped in a completely new transaction, such as executing a basic database operation such as INSERT, which is executed in a newly created transaction. It is certainly wise to let SQLite do its own transaction management at a time when only one database operation is needed. But if there is a lot of work to do at one time, such as loop call Insert Add, it is too expensive, because each operation to reopen, write, and then close the journal file, this file is temporarily used to save data operation of intermediate results, details see here (Reference).

This can be avoided if you explicitly use transactions with BEGIN transaction and end transaction before and after a series of SQL statements. For those that do not change the data, such a way can also speed up (as if the operation of a single pen in the database operation efficiency will be much lower than the batch operation, if the SQL statement can be done, it is not possible to use the cursor to operate).

Note: In addition to initiating a transaction, you must also be responsible for the commit and rollback operations on the transaction.

Begin TRANSACTION and end TRANSACTION can be used in Android app development in a way similar to the following:

[Java]View Plaincopyprint?

1. <span style="Font-family:microsoft Yahei;"  >db.begintransaction ();
2. try{
3. For (int i =0; i< LENGTH; i++,sequencenum++)
4. {
5. //Execute SQL
6. }
7. Db.settransactionsuccessful (); //marks a commit
8. }
9. finally{
10. Db.endtransaction ();
11. }</span>

2. Using the Index

If you do not use the index in the database, when you use a map query (projection query) search in a data table that is not sorted, there is no avoidable need to perform a full sequence lookup. This situation is usually not a problem, and each database, including SQLite, indexes the dataset to reduce the time to find.

The index maintains the order of a column or columns in a table, so that you can quickly navigate to a set of values without sweeping through the table. All index information is stored in a separate index table, so there is extra space to occupy, but absolute value, especially when you are doing a lot of reading and searching in the database.

SQLite automatically creates an index for each unique field, including the primary key (Primary key) field, or it can be created with the CREATE index displayed.

Note: If your query is too complex to use the index you created, then you need to think about the structure of your database.

3. Using qualifiers in the where branch

If a string is used to stitch the where of the SQL statement, use SQLite's query operation to take '? ' To compile the query. Here are some of its benefits:

A. It is beneficial for SQLite to cache these queries and indexes.

B. You can avoid reaching the upper limit of the SQLite cache. Using a string to stitch a where query, each of which is considered a different query, makes it easy to reach the upper limit of the cache.

C. Illegal SQL injection can be avoided.

SQLite Optimization in Android development