Performance optimization of database SQLite in Android

1. Index
Simply put, the index is like a book directory, catalogs can quickly find the number of pages, the database index can help to quickly find data, instead of full-table scan, the appropriate index can greatly improve the efficiency of database query.
(1). Advantages
The speed of database retrieval is greatly accelerated, including single table query, table query, group query and sort query. is often one to two orders of magnitude performance improvement, and increases with the number of data levels.

(2). Disadvantages
Index creation and maintenance are consumed, the index consumes physical space, and increases as the amount of data increases.
It is necessary to maintain the index when the database is modified, so it will affect the performance of adding and deleting.

(3). Classification
A. Creating indexes directly and indirectly creating indexes
Create directly: Use SQL statement creation, Android can be in Sqliteopenhelper oncreate or Onupgrade directly excusql create statements, statements such as

CREATE INDEX Mycolumn_index on MyTable (myclumn)

Indirect creation: Defining a PRIMARY KEY constraint or uniqueness key constraint, you can indirectly create an index, and the primary key defaults to a unique index.

B. General index and uniqueness Index
Normal index:

CREATE INDEX Mycolumn_index on MyTable (myclumn)

Uniqueness Index: guarantees that all data in the indexed column is unique and can be used for both clustered and nonclustered indexes, with the statement

CREATE UNIQUE coustered INDEX myclumn_cindex on MyTable (MyColumn)

C. Single index and composite index

Single index: The index build statement contains only a single field, such as the normal index and the uniqueness index creation example above.
Composite index: Also called a composite index, which contains multiple fields in an index-building statement, such as:

CREATE INDEX Name_index on username (firstname, LastName)

Where FirstName is the leading column.

D. Clustered and nonclustered indexes (clustered index, clustered index)
Clustered index: The physical index is the same as the physical order of the base table, and the order of the data values is always ordered in order, with the statement:

CREATE CLUSTERED INDEX mycolumn_cindex on MyTable (MyColumn) with Allow_dup_row

Where with allow_dup_row indicates that a clustered index with duplicate records is allowed

Non-clustered index:

CREATE unclustered INDEX mycolumn_cindex on MyTable (MyColumn)

Index defaults to nonclustered indexes

(4). Usage Scenarios
In the above mentioned the pros and cons, then certainly will be used when the index has a bit of understanding and a little confused, then the following summary:
A. When a field data update frequency is low, the query frequency is high, there are often scope queries (<, =, >=, <=) or order by, and group by occurs when the index is recommended. and the greater the choice, the more advantageous the index, where selection refers to the number/total number of unique values in a field.
B. Frequent simultaneous access to multiple columns with duplicate values for each column consider building a composite index

(5). Index usage rules
a.   for composite indexes, Use the most frequently used column as a leading column (the first field in the index). The composite index is not used if the leading column is not in the query condition when queried.
select * FROM student where class = 2 not used to index

b.   Avoid calculations on indexed columns, and any calculation of the WHERE clause column will result in index invalidation at query time
select * from Student where ToChar (grade) = ' 2′

E. Query columns are consistent with index column order
F. Replacing exists clauses with multi-table joins
G. Placing the highest number of filter records on the front
H. Good at using stored procedures, which makes SQL more flexible and efficient (SQLite does not support stored procedures:: >_<::)

(6) Index inspection

An index is established for whether an index is used by an SQL statement to see if the index is used by the execution plan.

2. Use transactions
The two great benefits of using transactions are atomic commits and better performance.
(1) Atomic submission
Principle submission means that all modifications within the same transaction are either done or not, and if a modification fails, it is automatically rolled back so that all modifications do not take effect.

(2) More excellent performance
SQLite creates a transaction for each insert, update operation by default, and commits immediately after each insert and update.

This process is repeated 100 times if the data is actually inserted 100 times, and the execution statement----commits the transaction. If we display the Create transaction, execute 100 statement-by-commit will make this creation transaction and commit this process only once, through this one-time transaction can make a significant increase in performance. In particular, when the database is located in the SD card, time can save two orders of magnitude or so.

Sqlte shows the use of transactions, the sample code is as follows:

PublicvoidInsertwithonetransaction () {Sqlitedatabase db =Sqliteopenhelper.getwritabledatabase ();//Begins a transactionDb.begintransaction ();Try{//Your SQLSfor (int i = 0; I < 100; i++) {Db.insert (Yourtablename,Null, value); }// marks the current transaction as successful db.settransactionsuccessful ();} Catch (Exception e) { // process it e.printstacktrace ();} finally { // end a transaction db.endtransaction ();}}        

where Sqliteopenhelper.getwritabledatabase () indicates that the Write table permission is obtained.

3. Other optimization
(1) The stitching of the statement uses StringBuilder instead of string
This is not much to say, a simple string addition causes the creation of multiple temporary objects to consume performance. StringBuilder has a much better space pre-allocation performance. If you have a general understanding of the length of the string, such as about 100 characters, you can specify the initial size directly from new StringBuilder (128), reducing the redistribution when space is not enough.

(2) Reading and writing table

Call Sqliteopenhelper when writing a table. Getwritabledatabase (), call Sqliteopenhelper when reading a table. Getreadabledatabase (), getreadabledatabase performance is more excellent.

(3) Fewer result sets and fewer fields are returned when querying.
When querying only the required fields and result set, more result sets consume more time and memory, and more fields lead to more memory consumption.

(4) Less use of Cursor.getcolumnindex

The time consumed by the observed cursor.getcolumnindex in the performance tuning process is similar to that of Cursor.getint. You can use static variables to remember the index of a column when you are building a table, calling the index instead of each query directly.

 static final String http_response_table_id = android.provider.basecolumns._id; public static final String http_response_table_response = "RESPONSE" public list<object> GetData () {... cursor.getstring ( Cursor.getcolumnindex (Http_response_table_response)); ...}           

Optimized for

PublicStaticFinal String http_response_table_id =android.provider.basecolumns._id;PublicStaticFinal String http_response_table_response = "RESPONSE"public  Static final int Http_response_table_id_index = 0public static final int http_response_table_url_index = 1; public list<object> GetData () {... cursor.getstring (http_response_table_response_index), ...}       

4. Asynchronous Threads
SQLite is a relational database commonly used in embedded development, fully open source.
Unlike the Web Common database MySQL, Oracle db, SQL Server,SQLite is a built-in database, database server in your program, without network configuration and management, database server and client run in the same process, Reduces the consumption of network access and simplifies database management. However, SQLite in concurrency, database size, network limitations, and table-level locks, so there is no need for multi-threaded operation.

Android data in a few hours table query may be time-consuming, will not lead to ANR, but more than 100ms will also make users feel delay and lag, can be put in the thread running, but sqlite in concurrency limitations, multithreading control is more troublesome, this time can use a single pool, Performing a DB operation in a task, returning results through handler and interacting with the UI thread, does not affect the UI thread, but also prevents exceptions that can be caused by concurrency. The instance code is as follows:

Executorservice singlethreadexecutor = executors.newsinglethreadexecutor (); Singlethreadexecutor.execute (  New Runnable () {    void//null, value); Handler.sendemptymessage (XX);}});

http://blog.csdn.net/jiangwei0910410003/article/details/17006791

Performance optimizations for database SQLite in Android