James Steward wrote:
I can receive up to 2,000,000 records in about 30 seconds from
the field, and I can't seem to jam them into an SQLite database
any faster than about 100 seconds at best, on my system.
So unless anyone can suggest some magic I have not thought
of, I will have to abandon the SQL database dream, and write a
less functional, data and application specific, database of my own.
James,
You haven't said what your system is, but to record your data in real
time you will need to insert about 70K records per second. That is high,
but not impossible for SQLite, so I wouldn't give up yet. I have had
SQLite doing 60K inserts per second on a standard 7200 RPM hard drive.
Do you actually need to insert records at this rate continuously, or
just for a short 30 second burst? If it is bursty, how much time do you
have between bursts?
You have said you tried both the TCL and C APIs, but you didn't say if
you were using prepared insert statements in the C API. If not, that
will save the overhead of parsing and code generation for each insert
statement. The prepare, bind step, reset mechanism will give better
perfomance.
If you can do post processing on the data, then you could look at
storing the data into separate databases on separate high speed (i.e.15K
RPM) hard drives. This should give you the raw I/O speed you need to get
all the info to disk. The you can run a second program that merges the
separate databases into a single one.
Do you need the ACID properties of SQLite, or can you simple repeat the
collection process if you have an OS crash or power fail while
collecting data? If not, then you can turn off the synchronous writing
with Pragma Synchronous=Off which should increase your write rate again.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
