Here's a serious heads-up for you:
Last night I was bulk migrating about 200000 rows from my old SQL Express
database into my new SQLite one. I wondered why it took about 10 minutes to
insert 1000 rows into one table, and when it got to the 60000 row table I
was curious about how long that would take. So I left the machine running
while we met friends for dinner at a Turkish restaurant. Four hours later
it's still running the same step. I noticed that a journal file was
flickering madly in Windows Explorer, so I guessed it was some transactional
problem.
My code is plain ADO.NET like this:
using (SQLiteCommand ...)
{
using (SQLiteCommand ...)
{
for (...)
{
ExecNonQuery(... INSERT ...)
}
}
}
This morning a few web searches hinted that I had to use PRAGMA synchronous
= OFF. That's too weird, so I put a using DbTransaction around the bulk
inserts and now the whole migrations runs in 10 seconds.
I'm going to cc a copy of this post to the authors of SQLite, as this is a
shocking gotcha. I'm utterly gobsmacked by the poor performance of the
inserts without a transaction around them.
Greg
