Kevin, I'm sure the SQLite mailing list would very much like to hear how long it takes to insert 400 million rows with (and without) an index using a PRAGMA cache_size=30000000 on a 48G RAM machine. And how big the final database turns out to be.
You might also want to play with a larger page_size setting, such as PRAGMA page_size=4096; or PRAGMA page_size=8192; I find that if you match the SQLite page_size the the OS' page size, you generally get better performance. --- [EMAIL PROTECTED] wrote: > "Kevin Bartz" <[EMAIL PROTECTED]> wrote: > > Hi SQLite, > > > > > > > > Thanks for building such a wonderful product. I've used BerkeleyDBs in > > the past and have been pleased to have SQLite's SQL interface. That > > said, I have some questions about optimizing .import. > > > > > > > > I'd like to load a tab-delimited file that's about 40G and has 400M > > rows, and I'd like to build two indices on it. I load it with .import. > > Questions: > > > > > > > > - How can I make this load as fast as possible? I've tried > > wrapping the .import command in a transaction, but didn't notice any > > change in the rate of file size growth. > > The ".import" command implemented by the shell already wraps > everything into a transaction for you. > > > > > - How can I get SQLite to use a large amount of memory for > > indexing? Loading this table into MySQL, I have the server allocate a > > buffer pool with around 48G of memory, which speeds things up a lot. Can > > SQLite do that? > > You have a machine with 48G of memory? Wow. > > PRAGMA cache_size=30000000; > > > > > - What is the optimal time to create indices - during or after > > the data load? > > > > Index creation is the slow part since during index creation, records > have to be inserted an random points all throughout the table, not just > at the end. The .import should go a lot faster without any indices. > > Experience shows that creating indices after the insert tends to run > faster - probably because of increased locality of reference. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
