>> Other than using a SSD to speed up random access, I hope a VACUUM >> operation would copy tables one by one so content of the tables would >> not scatter around the whole database. If this is the case, disk >> caching should work much better after VACUUM... fingers crossed. > > VACUUM will defragment, too. Unless your free space is fragmented. So yes, > probably a good move.
Dear all, I moved the database to another (faster) machine with a SSD and a regular HD. The performance of the query improved dramatically on the SSD drive. More specifically, the time to sequentially execute 'select count(*) from table_XX' on two tables took 17s instead of 7m, and running the query concurrently on two and four tables took the same 9s instead of 5m and 13m before. This firmly proved that random disk access speed was the bottleneck. Because the vacuum operation does not provide a progress bar, I wrote a script to copy tables one by one to another database on the regular HD. The time to count the number of rows for a table decreased from about 4m to within a minute (53s). Disk access is still a bottleneck but this is already far better than before. In summary, the problem with my database was that, because all tables were created at the same time and filled evenly, records of the tables were spread all over the 288G database. It was very slow to read pieces of a table from a regular HD to run a query. Copying the database to a SSD driver with much faster random access speed, or copying all tables one by one to a new database dramatically improved the query performance. Many thanks again for all the help from the list, Bo _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
