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 firstname.lastname@example.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users