In (early) praise of SQLite and EF4

[Greg Keogh]

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 and foreign keys

[Al Gonzalez]

Agree that it is a bit of a nuisance. However, since it only needs to be 
called once (per connection) you can use a factory method to create your 
context and make the call there.


-Original Message-
From: Greg Keogh
Sent: 5/28/2011 12:38 AM


>"Assuming the library is compiled with foreign key constraints 
enabled, it must still be enabled by the application at runtime, using 
the PRAGMA foreign_keys 
 command."


Good grief! I didn't realise it was a SQL command. I put the statement 
before my INSERT statements and it does raise an error when I break 
the FK. This is a nuisance, as I'd have to enable it continuously in 
code. And since I'm planning to use the EF4 EDMX, lord knows I would 
get down to that level.



>BTW, there is a newer version (1.0.72.0) at 
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki


Ooh, ta!

Greg

Re: SQLite and foreign keys

[Al Gonzalez]

Thanks for the heads up. I wonder if registration with VS was removed 
from the installer?


-Original Message-
From: Greg Keogh
Sent: 5/28/2011 12:58 AM


>BTW, there is a newer version (1.0.72.0) at 
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki


A warning: I installed both the x86 and x64 versions separately and 
SQLite no longer appears in the Add Connection | Data Source dialog. 
After reverting to 1.0.66.0 the data source was available again.


Greg

SQLite bulk insert performance

[Greg Keogh]

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

Re: SQLite bulk insert performance

[Joseph Cooney]

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  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

[Mark Hurd]

Isn't that exactly the same as the default mode for SQL Server?

On 29 May 2011 12:52, Joseph Cooney  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  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

[Joseph Cooney]

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  wrote:

> Isn't that exactly the same as the default mode for SQL Server?
>
> On 29 May 2011 12:52, Joseph Cooney  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  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