On 13 Jul 2010, at 5:33pm, Werner Smit wrote:

> I currently use the following pragma's (for speed)
> temp_store = 2
> page_size=4096
> cache_size=8192
> synchronous=off
> Any others I could try?

Don't get too involved in the PRAGMAs until you have a good reason to.  The 
default values are pretty good.  How much slower is it than you want it to be ? 
 If you need, say, 5% improvement we might suggest some things, but if you need 
50% improvement we might suggest more radical (and harder to program) 
solutions.  If you're optimizing just for the sake of it, find something better 
to do.

If you're doing a huge amount of database loading first it's faster to do it 
before you create any INDEXes, then to create your INDEXes once your TABLEs are 
populated.  Once your database is initialised do you expect to do more reads or 
more writes ?  Which one you do more of suggests how many INDEXes you should 

> I also wrap my statements (about 500 inserts at a time) with a begin/end
> transaction.

As JD wrote, at 500 writes in a transaction you're wasting a lot of time in 
overheads.  Try 50,000.

> After these 500 i take a few seconds to read more data so sqlite should
> have time to do any housekeeping it might need.

Unlike, for example MySQL, SQLite does nothing in the background.  The only 
functions it runs are the ones you call directly: it has no server process and 
no daemon.  However, your hardware may be caching writes or something, so your 
hardware may be taking advantage of the pauses.

> I had a count(*) to check how many inserts was actually done(4 progress
> bar) - and this slowed my down very much.
> Took it out, and want to use "select total_changes() " to keep track of
> inserts.

Much better.  As an alternative (and I'm not saying it's better than what you 
already have) take a look at


sqlite-users mailing list

Reply via email to