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. - 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? - What is the optimal time to create indices - during or after the data load? For reference, my present load commands: drop table mytable; create table mytable (stuff1 text not null, stuff2 text not null, stuff3 integer unsigned not null, r1 integer unsigned not null, r2 integer unsigned not null, r3 integer unsigned not null, r4 integer unsigned not null, r5 integer unsigned not null, r6 integer unsigned not null, r7 integer unsigned not null, r8 integer unsigned not null, r9 integer unsigned not null, r10 integer unsigned not null, r11 integer unsigned not null, primary key (stuff1, stuff2, stuff3, r1, r2, r3, r4, r5, r6, r7, r8, r9, r10, r11) on conflict ignore); create index stuff21 on mytable (stuff2, stuff1, stuff3, r1, r2, r3, r4, r5, r6, r7, r8, r9, r10, r11); .mode tabs begin exclusive; .import '/home/bartzk/mydir/mytable.txt' mytable 2; end; commit; Note that I installed a patch to get the additional argument to .import. Any ideas? Thanks, Kevin
