Is there a way to load an entire SQLite database into memory for faster results, using the sqlite3 CLI tool? Thanks!
Asked
Active
Viewed 1.2k times
3 Answers
5
This article provides a good example, namely:
sqlite> attach database ':memory:' as mydb;
sqlite> .schema
CREATE TABLE log(ts,msg TEXT);
sqlite> create table mydb.log as select * from log;
sqlite> select * from mydb.log order by ts desc limit 5;
However, I was a bit disappointed, since the improvements in speed I was hoping for were not met. I guess I'm not alone. The data set I tried is > 300MB, well beyond the default page cache size given there, so you would imagine that loading the entire database into RAM would yield noticeable results, but wasn't really the case. Any thoghts?
Community
- 1
- 1
ezequiel-garzon
- 3,047
- 6
- 29
- 33
-
You'd need to describe what improvements you are trying to obtain... Sounds like perhaps you need to define some indices and make sure they are used as intended. If you post more information, such as the structure of your table(s), and the SQL that needs improvement, perhaps I or others will be able to help. – Jul 03 '11 at 09:04
4
I'm not sure of what you are trying to accomplish here, but I have two ideas to propose:
1- Copy everything from your database to some attached in memory database. This link will tell you how to attach an in memory database: http://www.sqlite.org/lang_attach.html
2- Increase your cache size, keep transactions in memory, and keep the "temp store" in memory: http://www.sqlite.org/pragma.html#pragma_cache_size http://www.sqlite.org/pragma.html#pragma_journal_mode http://www.sqlite.org/pragma.html#pragma_temp_store
