SQLite performance optimization instance sharing, sqlite performance optimization instance

SQLite performance optimization instance sharing, sqlite performance optimization instance

The first cache database to be found in iOS development was SQLite, which was also used as the backbone in the future. In the past, SQLite was not used to read and write large amounts of data, therefore, there is not much focus on performance optimization. This time, we have optimized the performance of batch read/write of a large number of data in a specific scenario, increasing the performance by 10 times.

The general application scenarios are as follows:

Each time the program starts, it pulls some data from the server and synchronously updates the two tables in the local database. If the two tables do not exist, the data is written and Its fields are updated. When there is less data, there are dozens and thousands more.

Because cached data may be read and written asynchronously at the same time, a background synchronization queue is created, and all cache database operations are in this queue, then I monitored the key code execution time for writing the database. It took 30 seconds to update one thousand pieces of data to the database. The disk was written at 1.5 Mb/s, although there was no primary thread, this consumption is intolerable even in the background.

The core database operations are like this.

for 1000 : {Select -> Update Or InsertSelect -> Update Or Insert}

Because two tables are involved, there will be two. After testing, the Select statement has almost no messages at a time, but the Update or Insert ([FMDatabaseQueue executeUpdate:]) will consume a lot, because it will write data to the disk, and then think about whether it is possible to splice all the SQL statements, the last only one; then think about SQLite not a Transaction, so I tried to use the transaction operation of FMDB. Before the loop starts, [db beginTransaction] And the loop ends [db commit]. Just pack it.

General logic after adding a transaction:

beginTransactionfor 1000 : {Select -> Update Or InsertSelect -> Update Or Insert}commit

The test results were very good. The entire time was reduced from 30 seconds to about 2.8 seconds, and only two lines of code were added.

Summary:

The pitfalls and barriers that have been stepped on are all future experiences.

Although transactions are used to improve the performance, it is not safe to do so. Fortunately, the absolute consistency requirement on this part of data is not too high.
Sometimes the test of the simulator and the real machine cannot reproduce the same problem. Because the architecture, CPU, and hard disk of the simulator are different, the best performance test is based on the real machine. This problem is not solved in the simulator during testing. Because the hard disk speed is higher than the read/write speed of the real machine, many problems are avoided and are not found during testing.
When designing and designing databases, consider how to scale up and upgrade the database in the future, and how to improve the read/write performance.

Articles you may be interested in:

• SQLite Optimization Method
• Android database creation (SQLite) Save image example
• Summary of the use of Date and Time Functions in SQLite3
• SQLite3 auto-incrementing primary key knowledge
• C # detailed description of operating SQLite method instances