Hi, > I am running into some issues that seem related to the current database > file size.
I think it has to do with the file system cache: if you database is small, the entire database is held in your systems file cache. Once the database exceeds a certain size, real disk operations have to be done to access the data. SQLite is not very good in managing huge amounts of data in the database. When you are in the "slow mode", check your CPU usage and disk usage. I'd bet that CPU is very low and the disk is seeking a lot.... Regular VACUUM might help. Because it puts the data into a good order on disk. Are you using indicees? Do you have an integer primary key on the table, but the data is not inserted in key order? Do you delete and insert records a lot? Those operations can lead to heavy fragmentation of your database. The calculation is simple: suppose you have disk with 10ms average seek time and 30Mb/s read speed and a database of 4Gb. It takes about 130 sec to read the entire database. In the same time you can do 13000 seeks (in the worst case of fragmentation). Unfortunately the SQLite tables are not optimized for disk access. Records are accessed in key order which can be much more expensive than to access the table or index in disk-order. So, VACUUM brings the key order close to the disk order and therefore your database is much faster. However, if you wait to long to do the vacuum (and your database is extremely fragmented) vacuum might "take forever", because every record access involves a seek operation. I'm not sure what happens to huge indicees after VACUUM. Maybe they are fragmented again. But I don't know. That's at least my theory after experimenting with SQLite quite a bit. I haven't tested 3.3.15 which seems to have some performance improvements... Michael -- http://MichaelScharf.blogspot.com/ ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
