Am 19.08.2010 23:56, schrieb Simon Slavin: > On 19 Aug 2010, at 9:27pm, Taras Glek wrote: > >> I really appreciate that sqlite got this feature to reduce >> fragmentation, but why not expose this as a pragma? > Do you have figures which suggest that reducing fragmentation leads to any > improvement in performance ? Yes, see below. > It might be worth noting that fragmentation is normally seen as an issue only > under Windows which is very sensitive to it however. Other operating systems > use different ways of handling disk access, however, real figures from > real-world examples may disprove this classic view. Also, many installations > of SQLite are on solid state devices where, of course, fragmentation has no > effect at all. > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Hello Simon,
I agree completely. In my case (which is certainly not typical), a (several GB) large database is built up in several batches, one table at a time, while in parallel many intermediate files on the disk are created. This resulted in a very fragmented database file. After that, also several times, the data is selected in a way that uses 80-90% of the data in the database, using joins of all tables and sorting. The fragmentation was not a problem for me, but one of my customers did not like it. As far as i understood, some automatic tool monitored disk fragmentation and generated alarms; also, a backup tool slowed down. So, while inserting, at strategic places, I created a dummy table with a blob field and filled it with a very large empty blob. Then I dropped the table. In this way i simulated the new feature. Under Windows, the insert speed did not change measurably, but the speed of the later selects increased by about 15-20%. Also, my customer was happy. With the new feature available, i can remove my own workaround, which does not work so well annyway. Many thanks to the developers. Martin _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
