How to control SQLite in memory DB consumption usi

问题:

I have 100 GB data. I want to load it in-memory using SQLite in VB.Net. I have 32 GB RAM. I want SQLite to take 24 GB RAM and other 8 GB will remain free for other OS tasks. When it hits the 24 GB RAM limit It should start flushing data to some disk file automatically.

First: I used in-memory DB.

Dim cn As SQLiteConnection = New SQLiteConnection("Data Source=:memory:")

But it consumes 31.8 GB RAM, and then OS (Windows 7) jump in and takes the control and start using virtual memory (which saves data to disk). But it really really slows down the Computer. In short when to load huge data (by using in-memory method), SQLite eats up all ram and halts the computer.

Second: I loaded the data using temporary DB.

Dim cn As SQLiteConnection = New SQLiteConnection("FullUri=file:")

But when SQLite consumed near 2GB RAM it frees it by flushing data to file “C:\Users\Administrator\AppData\Local\Temp\etilqs_d4Fsx23dc” and start loading next data. In short when to load huge data (by using temporary DB method), it uses very less RAM and More Disk.

First scenario eats all ram and second consumes very less RAM. I need middle way that should be in my own control etc. 24 GB RAM for SQLite and 8 GB for OS, when it hits the 24 GB RAM limit, It should start flushing data to disk file automatically.

回答1:

To control the amount of memory that SQLite uses for an on-disk database, use PRAGMA cache_size. However, any unused memory will be used by the OS for the file cache, so SQLite's cache should not be set too large.

To speed up writes to disk (but make them as unreliable as with an in-memory database), use PRAGMA journal_mode = OFF.