Dennis Cote wrote:
Joe Wilson wrote:
I think some other factor is at play here.
Yes there is another factor at work here. [...] I suspect there are
optimizations that could be made to the memory I/O routines to speed
them up, they should at least be able to run slightly faster than file
based I/O.
Dennis Cote
I still find it rather hard to accept that disk databases are as fast or
faster than memory databases, so I ran a *simple* test on WinXP and they
are. It's so counterintuitive. :(
So, I used the Task Manager to watch the sqlite shell run a simple bit
of table stuffing SQL, shown below. For :memory: databases, long periods
were observed where the VM size crept up but I/O write bytes did not,
followed by periods where I/O bytes increased. For disk I/O VM size
stayed constant at about 4MB and I/O bytes increased steadily
throughout. The tests took about 3m5s and 3m47s, so the disk based
database was slower in this case, but not by much.
On the face of it, it seems that :memory: databases cause sqlite to
spend a lot of time mallocing large amounts of memory by requesting a
large number of small chunks, but I wouldn't have expected this to be as
slow as disk I/O.
Has anyone done a proper profiling analysis? I don't really buy the disk
caching suggestion because if we have a large transaction, then surely
we still have to wait an appreciable time after the commit for the data
to be sync'd to the disk. If we extrapolate through extremes, are we
saying that disk caching makes really slow hard disks and floppies as
fast as memory I/O. Seems very odd. Where does the sync time go?
Martin
Noddy SQL follows:
begin;
select datetime("now");
create table t(id INTEGER, t TEXT);
insert into t values (1,'Hello');
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
end;
select datetime("now");
