Re: SQLite bulk insert performance
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 20 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 6 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
Re: SQLite bulk insert performance
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 20 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 6 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.)
Re: SQLite bulk insert performance
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 20 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 6 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
