On Fri, Oct 08, 2010 at 09:09:09AM +0200, Michele Pradella scratched on the wall: > I was thinking this too, but I take this for last chance: my hope is I > can delete 5 millions of records in few seconds, science fiction? :)
Science fiction of the worst B-grade sort. Think about the numbers. You're talking about updating a significant chunk of a multi-gigabyte file. The WAL file tells you the changes amount to ~600MB of writes. That's a whole CDs worth of data. These days that might not be much for storage, but it is still a lot of data to move around. Even if your storage system has a continuous, sustained write ability of 20MB/sec, that's a half minute. How fast can your disk copy 600MB worth of data? But you're not just writing. You're doing a lot of reads from all over the file in an attempt to figure out what to modify and write. Both the reads and the writes (the integration, at least) are scattered and small, so you're not going to get anywhere near the sustained performance levels. 10x slower would be extremely good. Or think of it in more physical numbers... If you're using a single vanilla disk, it likely spins at 7200 RPMs. If it takes five minutes to update 5,000,000 records, that's an average of almost 140 records per disk revolution. That's pretty good, considering everything else that is going on! The only possible way to manipulate that much data in a "few seconds" is to load up on RAM, get a real operating system, and throw the whole database into memory. Or spend many, many, many thousands of dollars on a very wide disk array with a very large battery-backed cache and a huge pipe between your host and the array. Big storage is cheap. Fast storage is not. Don't confuse the two. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
