Isn't that exactly the same as the default mode for SQL Server?
On 29 May 2011 12:52, Joseph Cooney <joseph.coo...@gmail.com> wrote:
> 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
--
Regards,
Mark Hurd, B.Sc.(Ma.)(Hons.)
