CODEWITHSUNDEEP
iossqlitelocking
didi_X8
didi_X8

Reputation: 5068

sqlite optimize read performance

I'm using sqlite in an IPhone app for a readonly database. One use case involves issuing a lot of select statements, each returning around 3 rows. It's not possible to reduce the number of queries, because the parameters for the next query depend on the result of the previous query. The query itself is quite simple:

SELECT int1, int2, int3, int4 , int5, int6, int7 FROM sometable WHERE (int1 = ? AND int2 = ?) or (int3 = ? and int4 = ?) ORDER BY ROWID

The table has an index (int1, int2) and an index (int3, int4). All int's have datatype INTEGER

The query is done via C-API. A statement is compiled with sqlite3_prepare_v2() and used for all queries. After each query, sqlite3_reset() on the statement is executed, before binding the new parameters.

The database file is opened with flags SQLITE_OPEN_READONLY and SQLITE_OPEN_NOMUTEX.

Profiling on IPhone shows that big part of the time is spent in sqlite3_step() -> sqlite3VdbeExec->sqlite3BtreeBeginTrans->sqlite3PagerSharedLock->line pVfs->xAccess()

I'm no sqlite expert, but to me it looks like there's time wasted on unneeded locking. Unneeded, because it's guaranteed that there's no other access to the database while this queries are done. I also wonder about sqlite3BtreeBeginTrans. Are transactions created for select statements?

Can anyone tell me how to further optimize this?

Upvotes: 1

Views: 3720

Answers (1)

didi_X8
didi_X8

Reputation: 5068

The correct answer from the sqlite-user mailing list is to use EXCLUSIVE locking mode:

There are three reasons to set the locking-mode to EXCLUSIVE: [...] 2) The number of system calls for filesystem operations is reduced, possibly resulting in a small performance increase.

Speedup about 40%...

Upvotes: 2

Related Questions