"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]>
