Thanks for the comments. Yes we did look at this before we posted our original plea for help. We have a BEGIN/COMMIT around all the relevant code.
Rob > On 4 Jul 2015, at 20:28, droedel <sqlite at droedel.fastmail.fm> wrote: > > Rob Willett <rob.sqlite at ...> writes: > > [snip] >> The headline figures are we have gone from 213 secs to process 20 files > down to 90 secs to process 20 files. We >> are running approx 2.5x faster. To get this improvement the biggest change > was simply adding COLLATE >> NOCASE to the table schema. This saved around 120-130 secs which was > brilliant. >> > > Did you consider putting all SELECT-statements in an explicit transaction > (BEGIN TRANSACTION / COMMIT) ? When the statements are in one transaction, > sqlite only needs to acquire/release the lock once. During some benchmark > tests I did a few years ago, this reduced query time from 640 ms to 210 ms > for 10 000 trivial SELECT statements. > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users