CODEWITHSUNDEEP
iossqlite
iKiR
iKiR

Reputation: 860

SQLite readonly optimizations in multi-threaded environment

I am trying to get maximum performance from SQLite DB which is used in readonly mode on iOS device. So far I found these things have positive impact on performance:

sqlite3_exec(ret, "BEGIN TRANSACTION", NULL, NULL, NULL); // open transaction after open DB and close transaction before close DB
sqlite3_exec(ret, "PRAGMA journal_mode = OFF", NULL, NULL, NULL);
sqlite3_exec(ret, "PRAGMA cache_size = 20000;", NULL, NULL, NULL);
sqlite3_exec(ret, "PRAGMA locking_mode = EXCLUSIVE", NULL, NULL, NULL);

I've got 20-25% performance increase comparing to original version without pragmas above.

Is there something else that can help with performance? One thing I am concern about is that even it is readonly access I have multiple threads which can read data from DB, is it safe to use pragmas above for multi-threaded scenario?

Upvotes: 2

Views: 1511

Answers (1)

CL.
CL.

Reputation: 180070

Ensure that you have proper indexes for all you important queries.

You should VACUUM the database after you have created it.

You should start the transaction only after you have configured the parameters that can affect transactions.

Multiple threads reading from the same database work just fine. (But with exclusive locking, you will get problems if any thread ever tries to write.)

If your queries read only a subset of a table's columns, you can speed them up by creating a covering index for these columns; this allows SQLite to avoid reading the values of the unneeded columns. However, if there are any other queries that do read those other columns, both the covering index and the original table will have to be read into the cache, which will reduce the overall performance.

Upvotes: 1

Related Questions