CODEWITHSUNDEEP
sqldatabaseperformancesqlite
Enrique
Enrique

Reputation: 10117

sqlite tune read only table

I have a single table T in my sqlite database that is just used to perform selects.

This table T is accessed concurrently by several processes (at most 16). Since this table is 'read-only' how can I tune it to increase the performance of the queries?

When the journal mode = delete, I was receiving the following error message: RS_SQLite_fetch: failed first step: database is locked even though the concurrent processes were just doing selects. Then I changed journal mode = wal, and I am not receiving that message anymore. So my questions are:

  1. What parameters can I set to tell the database that the table is read-only.

  2. Can I set the journal mode = off permanently? Documentation says that every time the database is opened the journal mode has to be set. It can only be set permanently if the mode is wal or delete.

  3. Can I specify some option to store or cache that table in memory? I cannot use the string :memory: because the table will be accessed by different processes.

  4. What other parameters can I tune?

Upvotes: 1

Views: 510

Answers (1)

CL.
CL.

Reputation: 180050

SQLite does not lock tables. All locking is done on the entire database.

To prevent any write to any other table from interfering with concurrent reads, you must use WAL mode, or move this table into a separate database.

journal_mode = off does not disable locking.

Upvotes: 1

Related Questions