In (early) praise of SQLite and EF4
Folks, just in case anyone was wondering ... my experiments so far indicate that SQLite works nicely with Entity Framework 4. I expected horrors, but I guess the authors of the SQLite ADO provider have obeyed the rules. A couple of little irritations have surfaced. The INTEGER type in SQLite which is used for primary keys is an Int64 whereas under SQL Server and SQL CE it was an Int32. I had to adjust my code to prevent cast errors on ExecScalar calls. Bulk inserts of a thousand rows or so into SQLite tables seems excruciatingly slow. Perhaps there is some bulk insert trick or transaction gotcha affecting this that I'm not aware of yet. I have UNIQUEIDENTIFIER columns in the SQL Server tables I'm migrating to SQLite, but my reading so far is confusing about what underlying string value is expected to be stored by the caller in such a column in SQLite. I guess it's any string value which can be TypeConveter'd to and from a GUID. That's fine, but I can't find a NEWID() equivalent and some people seem to have invented their own ones by a messy combination of functions. I gave up and used a BIGINT SQLite column instead and filled with DEFAULT (ABS(RANDOM()). This seems like a good replacement for a GUID. I'm still unsure about the corresponding CLR type widths for INT, INTEGER, BIGINT, etc. More reading will sort this out I guess. I haven't pushed EF4 really hard yet other than proving it works, but it looks promising for more intense work. This is great, as I'm planning to re-jig one of my apps so that it uses SQLite and has absolutely no install footprint like it used to have with SQL CE. Greg
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
