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 define. > 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 http://www.sqlite.org/c3ref/last_insert_rowid.html Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
