Hello team, this isn't really a bug report, it's just a heads-up about a
trap for new players. I've just started evaluating the SQLite 1.0.66.0
ADO.NET provider, and I'm most impressed by the whole experience. For your
possible interest I have pasted below a copy of a post I just made into the
Australian .NET users group forum.
Cheers,
Greg Keogh
----------------
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.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
