Hi, We ran into an interesting "feature" with SQLite recently. We're building a large and complex system which uses a MySQL database; but in case of a network failure we also build a backup SQLite database every night and transfer to the client. So when the network goes kaboom (which, unfortunately, happens), the application seamlessly switches to the local SQLite database and the users rarely notice anything at all.
However, we noticed an interesting thing. When we first open the SQLite database and start running queries on it, the first query always takes an enormous time to complete: up to 30-45 seconds. It's not a really complex query, mainly just a lookup from a large table, and subsequent queries typically run in less than 200 milliseconds. But we've been totally unable to explain this initial delay. And, furthermore, when we vary the query, sometimes there's additional delays of similar time before everything runs smoothly again. It almost seems like the first time we use a particular index, the entire index is rebuilt on-the-fly, and then, after that, it is used. But we've been unable to spot any differences in the binary files. Since our database framework presently do not have support for the 3.x branch, we're using version 2.8.17. The database generated every night by one of our nightly processes is typically 50-80 MB in size. Any light upon what goes wrong would be much appreciated. :) regards, Mats Gefvert Visionutveckling AB
