Werner Smit wrote:
> My question(s)
> If I want to use the "insert or replace" to populate my database of
> around 1 million records.
> And I want to do it as fast as possible.
> What are all the tricks I can use?
Obey the first rule of optimization: don't do it unless you're
sure you need to.
If you're sure you need to, here are a few hints from my own
experience. They all come with trade-offs.
Buy faster hardware with more memory. I am serious. Sometimes it is
much cheaper to wait for 6 months for CPU speeds and memory sizes to
increase than to spend time optimizing your code.
Don't define triggers, indices, unique constraints, check constraints,
or primary keys. Make sure foreign key checking is off (which it is by
default).
Give sqlite a large page cache. If you are 32 bits then sqlite can't use
more than 4Gb of memory, so keep that in mind. If you give sqlite more
memory than exists on your machine, you might go to swap hell, so don't
do that.
If you are CPU bound and if you can split your problem into orthogonal
chunks and if you have multiple CPUs, consider farming out the work to
worker processes and incorporating their results into the main database.
Depending on your use-case you can consider telling your operating
system to favor the disk cache over processes' memory when you are
running low on RAM. In linux this is accomplished by setting 'swappiness'
high, not sure about other OSs.
> I had a count(*) to check how many inserts was actually done(4 progress
> bar) - and this slowed my down very much.
That's because count(*) doesn't run in constant time. I'm not sure, but
it may be linear. Which would imply that your algo as a whole is
quadratic instead of its original (likely constant) asymptotic behavior.
> Took it out, and want to use "select total_changes() " to keep track of
> inserts. Any problem with that?
You sound like you are writing a multi-threaded program. Are you sure
that total_changes() is only counting changes due to your insertions?
Consider keeping a loop execution counter and using that for your status
bar.
Good luck,
Eric
--
Eric A. Smith
Electricity is actually made up of extremely tiny particles
called electrons, that you cannot see with the naked eye unless
you have been drinking.
-- Dave Barry
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
