This page is about using in-memory SQLite databases.
----
2006-09-08: It appears that if you're doing a small number of transactions, that in-memory vs. on-disk doesn't make much difference. I have 138000 rows to insert, and as long as I do them all inside of one transaction, the run time is pretty much the same. However, if I don't use any transactions and just do a bunch of inserts, then the on-disk db is horribly slow compared to memory. Check out the note about transactions in PerformanceTuning.
----
2006-06-28: The patch for SQLite 3.2.2 to add the dump feature is not working on SQLite 3.3.6 because the dirty list mechanism is changed. To work dumping again, substitute just one line in marking all pages dirty.
pPg->dirty = 1;
as
makeDirty(pPg);
----
2006-02-22: "If the name of the database is an empty string or the special name ":memory:" then a new database is created in memory. "
NOTE: There is a difference between a database created with an empty string and a database created with string ":memory:"! For the one, created with "" there exists a temp file in the temp-folder under Windows. The performance of the ":memory:"-database is much better than that of the ""-database. This seems to be true on unix systems as well. A journal file will show up in /var/tmp with "" but not with ":memory:".
----
2006-03-13: Is there a way to read a database into memory, perform operations on the in-memory copy, and then commit those changes back out to the hard copy? I suspect it has something to do with transactions? My objective is to vastly reduce the disk I/O operations.
2006-02-20: A simple TCL-Implementation for loading a DB into memory:
proc loadDB {dbhandle filename} {
if {$filename != ""} {
#attach persistent DB to target DB
$dbhandle eval "ATTACH DATABASE '$filename' AS loadfrom"
#copy each table to the target DB
foreach {tablename} [$dbhandle eval "SELECT name FROM loadfrom.sqlite_master WHERE type = 'table'"] {
$dbhandle eval "CREATE TABLE '$tablename' AS SELECT * FROM loadfrom.'$tablename'"
}
#create indizes in loaded table
foreach {sql_exp} [$dbhandle eval "SELECT sql FROM loadfrom.sqlite_master WHERE type = 'index'"] {
$dbhandle eval $sql_exp
}
#detach the source DB
$dbhandle eval {DETACH loadfrom}
}
}
2006-01-07: I want to read a database from file to memory and use it
via :memory: - can I do that?
(The DB is distributed on CD/DVD and it is accessed many times - reading it
into memory would speed things up considerably.)
----
2005-03-25: :memory: doesn't seem to work if using sqlite3_open16(), rather
than sqlite3_open(). I
assume this is a bug?
----
2005-03-25: I want to create a database first in memory and then dump it on a
file. How can I do this?
2005-07-03: I've written a patch for SQLite 3.2.2 to add the
feature you asking for.
----
2004-09-15: I've written an article about memory databases in
combination with PHP:
SQLite
in-
memory databases.
----
2004-08-08: SQLite would be really useful for me if it supported shared memory
databases, so that
unlike ':memory', multiple processes could share the same in-memory database. I
am currently using
Turck MMcache to share data among PHP scripts; if I could use SQLite instead
with similar performance
it would be really good.
2005-11-02: If you are on Linux, perhaps you could use the shared memory
filesystm (tmpfs). It looks just like a normal filesystem, except the Linux
kernel stores it all in memory. Some distributions come out of the box
with /dev/shm mounted in this way, so you can share memory just by sharing
files in this directory.
----
2003-04-15: The in-memory database is now in the CVS tree, though it is still
mostly untested.
2003-05-09: In-memory databases are now a feature of the standard SQLite
library. To open an in-
memory database, use filename ":memory:".
----
_While connections to on-disk databases should not be carried across a Unix
fork(), is there any reason
not to do it with an in-memory database?_
(2003-10-21 by drh:) Not that I know of.
DK: Of course, when you do the fork() you are creating a new copy of the
in-memory db for the child
process. So subsequent edits to the db in the child won't show up in the
parents copy, and vice-versa.
NgPS: Multiple connections to ":memory:" within a single process create a fresh
database each time:
* (defvar db (sql:connect '(":memory:")
:database-type :sqlite
:make-default nil
:if-exists :new))
DB
* (sql:execute-command "create table a (id integer primary key, x varchar)"
:database db)
T
* (sql:execute-command "insert into a values (NULL, 'aaa')" :database db)
T
* (sql:execute-command "insert into a values (NULL, 'bbb')" :database db)
T
* (sql:execute-command "insert into a values (NULL, 'ccc')" :database db)
T
* (sql:query "select * from a" :database db)
(("1" "aaa") ("2" "bbb") ("3" "ccc"))
* (sql:query "select * from sqlite_master" :database db)
(("table" "a" "a" "3" "create table a (id integer primary key, x varchar)"))
* (defvar db2 (sql:connect '(":memory:")
:database-type :sqlite
:make-default nil
:if-exists :old))
DB2
* (sql:query "select * from sqlite_master" :database db2)
NIL