True, but I think the cost associated with creating a transaction is
different with SQLite. I think SQLite needs to opening, writing to, and
closing the journal file each time it creates a transaction which is
apparently expensive. Maybe since SQL Server is a service it keeps keeps the
journal file open.
Joseph
On Sun, May 29, 2011 at 1:29 PM, Mark Hurd <markeh...@gmail.com> wrote:
> 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.)
>
--
w: http://jcooney.net
t: @josephcooney
