SQLite performance Optimization in Android

Databases are a common technique used in application development and are no exception in Android applications. Android uses the SQLite database by default, and in application development, we use the largest number of non-additions and deletions. Even if the operation is simple, it is possible to find data slowly, insert data time-consuming and so on, if this problem arises, we need to consider optimizing the database operation. This article will introduce some useful database optimization operations, hoping to help you better use the database during the development process.

Build an index

Many times, we have heard that you want to find Fast indexing. It's true that the index of a data table is similar to a phonetic index or a radical index in a dictionary.

Explanation of the Index

Revisit the process of looking up a dictionary when we were young:

• For those who already know pinyin, such as 中 the word, we just need to find in the phonetic index zhong , we can determine the word in the dictionary page number.
• For words that do not know pinyin, such as 欗 this word, we only need to find the word in the radical index, we can find the page number that determines the word in the dictionary.

Yes, the index does just that, so that we don't need to find the entire data table for quick access.

Build an index

The basic syntax for creating an index is as follows

1	CREATE INDEX index_name ON table_name;

Create a single-column index

1	CREATE INDEX index_name ON table_name (column_name);

Is the index really good?

Undoubtedly, the index accelerates the speed at which we retrieve data tables. However, as the western proverb "There is sides of a coin", the index also has drawbacks:

• For additions, updates, and deletions, using an index slows down, for example, if you want to delete a word from the dictionary, you also need to delete the word's information in the phonetic index and the radical index.
• Indexing increases the size of the database, such as the phonetic Index and the radical index in the dictionary, which actually increases the number of pages in the dictionary, making the dictionary thicker.
• Indexing a table with a smaller amount of data tends to be inefficient.

Therefore, the use of the index needs to consider the actual situation of trade-offs, for the query operation of a large scale, business requirements for queries higher requirements, or the recommended use of indexes.

Compiling SQL statements

SQLite wants to perform the operation, it needs to compile the SQL statements in the program into corresponding sqlitestatement, such as select * from record this sentence, executed 100 times need to compile 100 times. For bulk processing of inserts or updates, we can use explicit compilation to reuse Sqlitestatement.

To be able to reuse sqlitestatement is also relatively simple, basically as follows:

• Compile the SQL statement to get the Sqlitestatement object, use the parameter ? instead
• The Sqlitestatement object is specifically data-bound in a loop, and index in the Bind method starts at 1, not 0

Please refer to the following simple code of use

123456789

PrivatevoidInsertwithprecompiledstatement(SqlitedatabaseDb){ StringSql="INSERT into"+Tabledefine.Table_record+"( "+Tabledefine.Column_insert_time+") VALUES (?)"; SqlitestatementStatement=Db.Compilestatement(Sql); IntCount=0; while (count < 100 { count statement. Clearbindings statement. (1system. Currenttimemillis statement. Executeinsert }} /span>

Explicitly using transactions

In Android, either using Sqlitedatabase's Insert,delete, or Execsql, the transaction is turned on to ensure that each operation is atomic so that the result is either the correct result after the operation or the result before the operation.

However, the implementation of the transaction is dependent on the file named Rollback Journal, which is used to complete atomic operations and rollback functions. Since it belongs to the file, it conforms to the Unix file paradigm (open-read/write-close), so for batch modification operations there will be repeated open file read-write and then close the operation. Fortunately, however, we can explicitly use transactions to reduce the journal file open shutdown that is brought by batch database updates down to 1 times.

The specific implementation code is as follows:

123456789

PrivatevoidInsertwithtransaction(SqlitedatabaseDb){ IntCount=0; ContentvaluesValues=NewContentvalues(); Try{ Db.BeginTransaction(); While(Count++<100){ Values.Put(Tabledefine.Column_insert_time,System.Currenttimemillis()); Db.Insert(Tabledefine.Table_record,Nullvalues } db. } catch (exception e ) { e. finally { dbendtransaction (); }} /span>

In the above code, if no exception is thrown, we consider the transaction to be successful and the invocation to db.setTransactionSuccessful(); ensure that the operation is actually in effect. If an exception occurs during this procedure, the bulk data is not inserted into the existing table.

Query data optimization

For query optimization, there are a few suggestions for micro-optimization in addition to indexing.

Get data column information on demand

In general, we are in our own time-saving purpose, for finding and using code like this

123	private void badquery (sqlitedatabase db) { db. (tabledefine. Table_recordnullnullnullnullnullnull) ; /span>

The second parameter type of the above method is string[], meaning to return the colum information of the result reference, passing NULL indicates that all the column data needs to be obtained. It is recommended that you pass the string data objects that you really need to indicate the column information you need, and this will increase your efficiency.

Get the column index in advance

When we need to traverse the cursor, it's common practice to do so

123456

PrivatevoidBadquerywithloop(SqlitedatabaseDb){ CursorCursor=Db.Query(Tabledefine.Table_record,NewString[]{Tabledefine.Column_insert_time},Null,Null nullnull Null ; while (cursor. Movetonext{ long Inserttime = cursor. Getlong (cursor. Getcolumnindex (tabledefine. Column_insert_time} /span>

But if we're going to get columnindex out of the loop, the effect will be better, and the modified code would look like this:

12345678

PrivatevoidGoodquerywithloop(SqlitedatabaseDb){ CursorCursor=Db.Query(Tabledefine.Table_record,NewString[]{Tabledefine.Column_insert_time},Null,Null,Null,Null,Null); IntInserttimecolumnindex=cursor. (tabledefine. Column_insert_time while (cursor. Movetonext{ long Inserttime = cursor. Getlong (inserttimecolumnindex } cursor. /span>

Capacity adjustment of the Contentvalues

Sqlitedatabase provides a convenient contentvalues that simplifies our mapping of column names to values, Contentvalues internally uses HashMap to store key-value data, and the initial capacity of contentvalues is 8. If the added data is more than 8, it will be doubled, so it is recommended to measure the content of the contentvalues, set a reasonable initialization capacity, and reduce unnecessary internal scaling operations.

Close cursor in time

Using a database, it is common to forget to close the cursor. For information on how to find the cursor that is not closed, we can use Strictmode, please poke here Android Performance Tuning tool Strictmode

Time-consuming asynchronous

Database operations, which are local IO, are often time-consuming, and if poorly handled, can easily lead to ANR, so it is recommended to put these time-consuming operations into an asynchronous thread, and it is recommended that a single-threaded + task-queue-processing handlerthread be implemented asynchronously.

SOURCE download

Sample source, stored on GitHub with address Androidsqlitetuningdemo

SQLite performance Optimization in Android