On Fri, May 23, 2008 at 10:20:45AM -0400, Stefan Arentz scratched on the wall: > I have an interesting problem. I need to generate a large table > periodically. The table contains a unique SHA1 hash code and 6 integer > values and has about 6 million rows. Generating this table is fast. I > can set it up in less than 90 seconds on a slow iMac (2.16 Ghz and > slow disk). The thing that takes a (relatively) long time is the index > creation on the unique hash code .. 720 seconds.
> Doing this all on an in-memory database takes about 150 seconds in > total. Is it possible to build a database in memory and then dump it > to disk? Not at this time. > Are there any other tunable options that can improve indexing speed? The biggest thing you can do to improve indexing performance is to increase the size of the page cache. Assuming you're using the default 1K page size, each page takes up about 1.5K of RAM in the cache. The default cache size is 2000 pages (3MB), but you should crank this up as high as you can while having a reasonable chance of still keeping the whole thing in physical RAM... say 75% of your machine's total RAM, if you've got a gig or two. Just be aware that the value is the number of pages, not the number of KB. The other thing that helps a little is to turn synchronous to off while you're creating the index. This is normally a dangerous thing to do, but if you're just dumping data into a database chances are you can re-start from scratch if things go wrong. You may also see some performance from setting the temp_store to memory. The biggest single thing is the page cache, however. http://www.sqlite.org/pragma.html PRAGMA page_cache PRAGMA synchronous PRAGMA temp_store -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
