----- Original Message ---- From: Jay Sprenkle <[EMAIL PROTECTED]> On 5/1/06, Ivan Voras <[EMAIL PROTECTED]> wrote: > > I've spent a long time debugging low performance of an > > application that uses :memory: database and have found > > that sqlite v2 is much faster than v3. After some > > digging around it seems that even two proof-of-concept > > programs that are identical except for used SQLite > > version reproduce this behaviour just fine:
> Testing is difficult to do correctly. As several people noted on this list > just > today the first time they ran a query it had much different > performance than subsequent > runs of the query. Did you run these tests more than one time? What's your > environment? The precaching trick desribed earlier can not be done on memory databases as they are already... in memory. The main reason first queries (sometimes it's more like the first few hundred queries if the db is big) are significantly slower are because of the way sqlite relies blindly on the OS caching mechanism for caching the indexes/primary keys in memory. In any case, when doing any kind of benchmarking that involves disk access, you must clear the OS disk cache so that the algorithm used by the OS is removed from the equation (and also to be able to compare results), otherwise all you're doing is benchmarking a moving target. To discard the disk caches: on linux: easy, just umount and mount the partition that contains the db file on windows: I don't know of any other way than clearing the whole cache with a tool such as Clearmem.exe (from the Windows Server 2003 Resource Kit, but you can find it on the web very easily) Hope this helps. Nicolas
