On Sat, 25 Sep 2010 10:35:18 -0700 (PDT), EQRecovery <[email protected]> wrote:
> I was pulling out my hair trying to figure out why > the insertion into a table was becoming extremely > slow as a table grew. > > I was creating a new table and populating it with > 100,000 rows of data (as a test case; I really > wanted to populate it with over a million rows). > > [Insertion A] When a Text Column was NOT Unique it would take: > 8875 ms = ~9 seconds > > [Insertion B] When a Text Column was Unique it would take: > 155781 ms = ~156 seconds Root casue probably is B-Tree page splitting of the index that maintains the unique constraint in case B. > Insertion B seemed to be IO/Disk bound and the CPU > Utilization drops to around 4-8% once the table > reaches a certain size. The amount of time it takes > as the table increases it exponential, which is the > main problem. > > Is there anything I can do to speed up the insertion > when the Text Field is Unique? * Use large transactions (~ 50,000 is a good start for an experiment) * Offer the data to sqlite sorted by that unique column * Enlarge cache_size. * Enlarge page_size. * pragma journal mode off. * pragma synchronous off. * Buy a faster disk. > My solution so far has been to put the database file > in a RAMDisk, to reduce the IO/Disk bottleneck, > but once the database grows too large, this won't be an option. Another option is to use an in-emory database, and after populating it, to flush it to disk using the backup interface. This obviously has the same size limitations as a RAMDisk. Note: An in-memory database will be deleted once you close the connection. > I'm also using System.Data.SQLite, although from the > changelog for sqlite I don't see anything fixed that > would resolve the issue. > > Thank you for your help. -- ( Kees Nuyt ) c[_] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
