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 

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 

Good luck,

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

Reply via email to