Now it's coming back a bit. I remember a bulk load was running for over 4 hours and I wondered what the hell was wrong. It turns out you have to wrap the inserts in an explicit transaction, otherwise it seems to create a silent transaction for each insert and it's slow beyond belief. The clue to this was when I noticed a temp "journal" file rapidly flickering in and out of existence next to the DB it was loading. I emailed the authors of SQLite to tell them to make sure this behaviour is clearly documented. I decided to "pulse" the transactions so that while counting through the approx 100000 rows to insert I do a ++count % 1000 == 0 and close and open the transaction. That works fine.
You need a similar technique when doing bulk inserts with the ESENT database, as it has a modest limit on the transactions it can hold in memory. Kindly, they recommend this technique in one their support web pages. I can't really remember the SQLite FK issue at the time, but it seems reasonable that I stumbled across it and later forgot. -- Greg
