I wouldn't bother e-mailing the SQLite folks. This is by design, and is a
well known behaviour with SQLite. From memory if you don't explicitly have a
transaction then one gets created for each operation, which slows things
down.
Joseph
On Sun, May 29, 2011 at 10:27 AM, Greg Keogh <g...@mira.net> wrote:
> 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
>
--
w: http://jcooney.net
t: @josephcooney
