>> For >> example, if we create 1000 tables and insert records to them in batch, > > Do you really have 1000 different schemas so you need 1000 different tables > to store data ? Can these not be merged into one table ?
Unfortunately, the 1000 tables can have slightly different schemas and I do need separate tables because we frequently need to work with subsets of tables. Selecting subsets of records from a huge table is not feasible. >> the data of each table will likely spread evenly across the whole >> database, and almost all pages need to be read when we go through >> records of a table. > > Do a VACUUM during downtime, probably overnight. Of course, you will need > disks which are up to three times the size of your database to make sure the > VACUUM can complete. The problems with VACUUM are that it needs days to vacuum our databases and there is no way (e.g. a progress bar) to know when the process will end. One of the applications of the process I described (copying tables to multiple databases) is to split the original database into multiple smaller ones and merge them into a new database. This is essentially a VACUUM process but I can at least know the progress (number of tables copied), process samples incrementally (no need to wait for days), and I hope I can get better performance by parallelizing the copying process. Bo _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
