SQLite Optimization in Android Development

To optimize SQLite, you can use SQL statements for batch processing. Instead of a single operation, you can use Cursor instead of SQL statements. For example, for batch DELETE/UPDATE, assembling conditions into SQL statements is much more efficient than querying and deleting conditions by Using CURSOR (A few years ago, I used a stored procedure to replace a single operation and reduced the time of a batch computing from one day to several minutes. For more information, see). The second is the optimization of the operation: For INSERT/UPDATE operations with a large number of use transactions, if the SELECT Operation is large, use the index.

Combined with the current work, we found that the operation is optimized. The following article can be translated and archived. The text is as follows:

SQLite has a simple SQL interface and is known for its low memory usage. Currently, SQLite has been widely used in Android and iOS development. This article mainly discusses how to optimize SQLite performance and resource occupation in Android applications.

1. Use Transaction)

By default, each SQL statement is packaged into a completely new transaction. For example, if you execute a basic database operation such as INSERT, It will be executed in a newly created transaction. When only one database operation is required at a time, it is wise to let SQLite perform transaction management on its own. However, if a large number of operations are to be performed at a time, such as when INSERT is called cyclically, the overhead is too large because each operation must be re-opened and written, close the journal file, which is used temporarily to save the intermediate results of the data operation. For details, see here (reference ).

If you explicitly use begin transaction and end transaction to display transactions before and after a series of SQL statements, you can avoid the above situation. For operations that do not change the data, this method can also speed up (as if the efficiency of a single operation in a database operation is much lower than that in a batch operation, if you can use SQL statements, you cannot use Cursor for operations ).

Note: In addition to initiating a transaction, you must be responsible for committing and rolling back the transaction.

In Android Application Development, you can use the following methods to use begin transaction and end transaction:

db.beginTransaction();try{  for(int i =0; i< LENGTH ; i++,sequenceNum++)  {  // execute SQL  }  db.setTransactionSuccessful();// marks a commit}finally{  db.endTransaction();}

2. Use Indexes

If indexes are not used in the database, when you use projection query to search for an unordered data table, you must perform a full-series query. This is usually not a problem. Every database, including SQLite, performs indexes on the dataset to reduce the search time.

Indexes maintain the order of one or several columns in a table, so that you can quickly locate a group of values without scanning the entire table. All the index information will be stored in an independent index table, so additional space will be occupied, but it is definitely worth the money, especially when you perform a large number of read and search operations in the database.

SQLite automatically creates an INDEX for each UNIQUE column, including the Primary Key column. You can also CREATE an INDEX by using create index.

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

3. Use a qualifier in the Where Branch

If the SQL statement Where is concatenated by a string, do not use the SQLite query operation '? 'To compile the query. The following are the benefits:

A. It is helpful for SQLite to cache these queries.

B. You can avoid reaching the upper limit of SQLite cache. When you use a string to concatenate A Where query, each query is considered as a different query, which can easily reach the cache upper limit.

C. Avoid illegal SQL injection.

Reprinted please indicate the source: http://blog.csdn.net/horkychen

Refer:

1. Key Points of SQLite query optimization performance

2. Android SQLiteDatabase