CODEWITHSUNDEEP
c#databasecachingsqlite
user1034912
user1034912

Reputation: 2267

Does SQLite Connection needs to remain open(connected) to take advantage of Pragma Cache_size?

I've always thought that the best practice to manage database connections is to connect to it, perform a query and immediately close the database connection

However, as for SQlite, I stumbled across their website the following snippet with regards to PRAGMA CACHE_SIZE From the SQlite Website

PRAGMA cache_size; PRAGMA cache_size = pages; PRAGMA cache_size = -kibibytes;

Query or change the suggested maximum number of database disk pages that SQLite will hold in memory at once per open database file. Whether or not this suggestion is honored is at the discretion of the Application Defined Page Cache. The default page cache that is built into SQLite honors the request, however alternative application-defined page cache implementations may choose to interpret the suggested cache size in different ways or to ignore it all together. The default suggested cache size is 2000 pages.

If the argument N is positive then the suggested cache size is set to N. If the argument N is negative, then the number of cache pages is adjusted to use approximately N*1024 bytes of memory. Backwards compatibility note: The behavior of cache_size with a negative N was different in SQLite versions prior to 3.7.10. In version 3.7.9 and earlier, the number of pages in the cache was set to the absolute value of N.

When you change the cache size using the cache_size pragma, the change only endures for the current session. The cache size reverts to the default value when the database is closed and reopened.

Doe this means that for me to benefit from Memory Caching, I need to remain connected to the SQLite Database File througout the duration of my appllication? The moment I disconnect the database, all cached memory is lost?

Upvotes: 0

Views: 773

Answers (1)

CL.
CL.

Reputation: 180070

The cache is indeed dropped when closing the connection.

Both in SQLite and in all other databases, opening a connection has some overhead. Using many short-lived connections is never a good idea.

In situations where you do have many independent, short-lived pieces of work (such as a web server), you typically work around these inefficiencies by using connection pooling.

Upvotes: 1

Related Questions