Is there a way to load an entire SQLite database into memory for faster results, using the sqlite3 CLI tool? Thanks!
Add a comment
|
3 Answers
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?
1 Comment
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.
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
Comments
Pass --deserialize to the sqlite3 CLI tool.
