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.
