2007-04-16: Is there a way to forcibly suspend file writes on a temporary basis? Basically, if there is some process that cannot be slowed down by disk writes (media playback), but we will be issuing INSERT and UPDATEs to the database, is there a way to have those transactions not be commited to file, but keep those changes in-memory? It seems that we could keep the 'dirtyCache' flag set to zero when we're in this mode and it would not perform writes (that's in testing now). We don't want to have to maintain two databases (one in-memory and one on-disk, shuffling rows between the two) and simply suspending disk writes would solve a lot of problems here.
2007-04-16: I have run a simple test to compare the performance of disk-based database and memory-resident database in sqlite3, the result was the same as the wiki item at 2006-09-08 said. Why the memory-resident database is so slow? Is there any way to improve it?
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-10-31: That's all quite relative to disk speed. For example, I'm writing an app that runs off of flash memory and the difference in speed is quite noticeable even with a single insert.
2006-06-28:
pPg->dirty = 1; as makeDirty(pPg);
2007-04-25:"Thanks for the information. It's really helpful"
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?
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