SQLite performance Optimization

1, the database performance

1.1 Bulk transaction inserts for improved data insertion performance

Because SQLite is the default every time the insert is a transaction, you need to read and write to the file, then reduce the number of transactions can be simplified book disk read and write times to achieve performance gains.

1.2 Single SQL is better than more than one SQL

The results show that for dozens of SQL inserts when you switch to a single SQL performance has improved, but it is important to note that, instead of a single single readability, there will be SQL extra long error.

1.3 Read and write operations are mutually exclusive, while the write operation can hibernate for the read operation

Because of the majority of transactions inserted in the first step, which can result in an increase in insertion time, it also affects the speed of data presentation, so you can hibernate operations during the insertion process to show the data out of time for the read operation.

1.4 Using the Index

The benefit of an appropriate index is that reading is faster, and of course the effect is that the time of the data insertion modification increases, because the index changes are maintained. However, indexing is necessary for most of the databases that read more than write operations. For information on how to design an index, refer to the following article:

Index optimization

1.5 Using federated Indexes

Too many indexes will slow down the speed of reading, a typical case is to be at the same time according to the provinces and cities and counties to query, but also according to the date of the day, if each index so that the reading speed will be significantly reduced.

For this hierarchical relationship of keywords, you can consider the joint index, such as first according to the province query, and then according to the provinces and cities to query, the level of progression to the provinces and counties of the query way, you can use the joint index, the effect is very good.

1.6 Do not use too many indexes

1.7 Adding query conditions

When you add a limit of 1 to a single piece of data, so the search will not be queried again, greatly speeding up the speed

1.8 Map The index of the field in advance.

Reduce the time of getcolumnindex, can shorten half of time

2, the database design

2.1 Query speed through redundancy

2.2 Reduce data to improve query speed

For example, the drop-down operation, first clear the old data, and then insert new data to ensure that the total amount of data in the database is small, improve query speed.

2.3 Avoid large data multi-table joint query

And 2.1 of the way is actually the same principle, but here need special to illustrate the following, such as a file table, as well as a multimedia file table, you can design a file table, a Multimedia association table to hold multimedia data, file information is in the file table, and then through the Foreign Key Association.

However, if the two table data is many, the primary key also inconsistent with the data from the server down the sequence also inconsistent then, two tables of the union query out the data is much slower, this time can use redundancy to invoke the query speed.

Reference: HTTP://WWW.JIANSHU.COM/P/3B4452FC1BBD

SQLite performance Optimization