iOS platform SQLite Performance Optimization


In the ios platform,Permanent storage of data so few,比如说 coredata,For example realm,There are several options of nosql,But unfortunately,Several programs to support functions are still too few nosql,So let their selection is very sad - after all,,If it is a simple application of the words,It is not as good as other programs to facilitate quick - although the trend is nosql。

This time we talk about another common storage solutions --sqlite,This is very powerful stuff,It is a framework sql with c achieve without server,It is a framework document can be achieved with a simple database of,Most databases have query function,Performance is also good。


Commonly used words,The default configuration is sufficient for sqlite,But there are some cases,We still need faster experience - such as when the input method thesaurus。Many people think,With sqlite soon,But as the input method thesaurus is clearly too slow,Is sqlite step faster it can not? of course can,sqlite default in order to meet with the very conservative environment so most people set,We can make it faster。


Pragma is a unique configuration statement sqlite,There are co-ordinated with the corresponding parameter,We can be modified after the database connection,But when the next connection,Still need to re-configure。

You can see the full hereAll parameters Pragma,However, there is a significant impact on the performance parameters so few,Not many。

As you execute pragma as in the implementation of sql statements in sqlite,No return value。


Size must be a multiple of 2,In General,4096 is the default should now be a,But if not,Then you manually set about,all in all,You'd better set it to your system disk page size。


Close synchronization,Database only when a sudden power equipment such situation may cause database corruption - ah,This situation,I think it is acceptable。


When there are multiple processes at the same time you do not need to access the database (in our own app to access the database in it) it does not need the default normal,Set the lock mode to exclusive mode,You can guarantee the same time only one process accesses the database,This avoids unnecessary conflicts control,Increase database speed。


Logs can ensure the integrity of the data submitted,Once you submit a problem,Database can be achieved rollback。but,Now ios stability in most cases is sufficient,If you will not even write to database,Then simply log mode off the good。This can greatly accelerate the speed of sqlite。


This cache,The default is 0,There are many people recommend 1,I suggest you test multiple times,My own feeling is that there is no difference between a little and,But nothing like the size of the difference between the ......


Configuring Memory Mapping,But in fact it did not seem to open,It depends on your version of sqlite。


If you're like me just for querying,Then enable this option。

The singleton pattern

Having the parameter configuration,Let's talk about other aspects of,For example, the overall usage posture。If you do not have other processes to access the database throughout the application (generally should not have),Then do a one-piece pattern,Then held long enough links,And there is no need to connect and disconnect continues,It also prevents you accidentally cause database resources at the same time to compete for data loss and file corruption。

For example,,Such:


Properly indexed fields you need to frequently queried,And avoid using too complex sql statement to ensure that the query will use the index。An effective way to test is to use the built-in sqlite explain Feature to view execution flow statement,If it contains idx ,That almost all the same。

Execution is expected to explain the use of view sql statement

Avoid inappropriate sql statement

In General,You should get only the content you want to get,Do not do it SELECT * FROM This type of brain damage statement,The same time,When acquiring content,For example, you might write like this:

If a large number of query,Then you'd better write this:

Use the field index to get content,Find avoided,Greatly accelerate the speed value。

Create a table

Create a table where you can also talk about optimization,For example, if your energy field is not empty,It is not empty,The only natural that if you can on the best!



That is all I pocketed input in the development process to explore some performance optimization sqlite,It may not apply to all developments。And advanced compiler optimization has yet to reach,We hope to be able to help you。

Further reading

Performance Optimization of SQLite on iOS with Xamarin

Original article written by Gerber drop-off:R0uter's Blog » iOS platform SQLite Performance Optimization

Reproduced Please keep the source and description link:

About the Author


The non-declaration,I have written articles are original,Reproduced, please indicate the link on this page and my name。

Leave a Reply

Your email address will not be published. Required fields are marked *