Ayende @ Rahien

It's a girl

SQLite vs. SQL CE

Those two seems to be the most common embedded databases in the .NET world. This is important to me, since I want to do testing against embedded database.

SQL CE can be used with SQL Management Studio, which is nice, but it has three major issues for me so far:

  • It doesn't support memory only operations. SQLite does, and it means a difference of 12 seconds vs. 40 seconds in running ~100 tests that hit the DB. This is important, especially kicking up everything seems to take about 10 seconds anyway (using Test Driven.Net)
  • It doesn't support paging (WTF!)
  • It doesn't support comparing to a sub query, so this is not legal:
    select * from account where 1 = (select allow from permissions)

Right now I am experimenting with just how much I can twist around to get everything to break.

I am on the third NH bug this week, and counting :-)

Comments

Steven Harman
01/22/2008 03:13 AM by
Steven Harman

So... are you advocating SQLite? At least until SQL CE can overcome those three issues?

Ayende Rahien
01/22/2008 03:13 AM by
Ayende Rahien

I changed to use that, yes.

Andrew
01/22/2008 03:46 AM by
Andrew

And it doesn't support batching either.

Ayende Rahien
01/22/2008 03:53 AM by
Ayende Rahien

That actually doesn't bother me much, batching is most often useful for remote DBs, there is little sense to use it with embedded DB

Bob
01/22/2008 04:24 AM by
Bob

I compared SQLite 3.3 with SQLCE-2.0 for a WinCE .NET project last year. I really wanted SQLite to work because you can only read 2.0 sdf files on WinCE. . For small DBs SQLite is great. Unfortunately, I ran into serious memory and performance problems with SQLite when the number of records got large (>10000). Ended up using SQLCE.

Ayende Rahien
01/22/2008 04:56 AM by
Ayende Rahien

That is surprising. Can you give more details about it?

M
01/22/2008 06:20 AM by
M

Having used SQL CE for a while I can say without a shadow of a doubt that it strikes me as being a bit half baked. Why call it SQL Server Compact Edition and it lacks procedures, views and triggers? It supports only a subset of SQL, which can lead to nasty gotchas.

There's a whitepaper on Microsoft that tries to explain why a lack of features like views, triggers and procedures is a good thing. Talk about spin! I don't buy it one bit,

If VistaDB can do it, and so can Codegear Blackfish, I'd love to know why with all their experience and considerable headstart Microsoft have been unable to do it

Darius Damalakas
01/22/2008 07:55 AM by
Darius Damalakas

Another possibility is embedded Firebird, thouhg in-memory only databases are not supported. Might be a good choice.

Frans Bouma
01/22/2008 09:28 AM by
Frans Bouma

CE desktop is a stupid design decision. Instead of using a trimmed down SQL express codebase, they opted for the mobile codebase. What's stupid is this:

  • it has a different SQL dialect So you can't switch between a CE desktop and a sqlserver 2005 db by just switching a connection string

  • it uses a different client. This alone is a showstopper.

I'd also opt for firebird embedded. The nice thing about firebird embedded is that you can switch from embedded to server by switching the connection string.

Jon Davis
01/23/2008 04:00 AM by
Jon Davis

SQL CE is a joke. It's like Microsoft went to their enterprise database folks and said, "Hey, let's scale this big thing down to fit into a mobile device!" Uh-uh, no, that's not the way to build a mobile database platform. To build a mobile database platform you scratch everything you know about prior database implementations, you re-discover your basic requirements (parse SQL, store data, do it fast), and you go to experts in mobile development and have them get to work, passing along any knowledge you can give them about lessons learned from the database world.. and let THEM decide how and if to implement the suggestions in accordance to the appropriateness of THEIR trade and THEIR experience with mobile software API consumers.

SQLite is not Oracle or mySQL shrunken to mini, and it's rediculous for Microsoft to expect people to take SQL Server 2000 shrunken to mini as a solid mobile platform.

SQL CE exists because a database runtime was needed for the Windows CE environment. People use it because it's there. Not because it's good.

Jon Davis
01/23/2008 04:37 AM by
Jon Davis

Heh. Scratch everything I said. I don't know SQL CE's background. Perhaps I had it backwards. Dunno.

I do know this: given everything I've heard, it sounds like SQLite is faster, and SQLite is capable and competent, the only question I would raise about SQLite is its deployability to the target environment.

Jon

Mark
01/23/2008 10:43 AM by
Mark

I use SQLCE 3.5 with NHibernate at the moment. I am sometimes frustrated by it's lack of sub-select and trigger support.

I went for CE because I was interested in MSSynchronization services, and deployment via clickonce and in the general hope that MS would build in the same level of power and robustness that I have come to exect from the server edition.

Also, blog posts like this helped persuade me :)

http://www.ayende.com/Blog/archive/2007/01/27/6916.aspx

Ayende Rahien
01/23/2008 10:51 AM by
Ayende Rahien

Yes, datetime handling are a PITA in SQLite. I really need to write a set of functions for it to make it nicely usable from NH

Stuart C
01/23/2008 01:57 PM by
Stuart C

SqlCe does not support smalldatetime either. The best alternative I have found is VistaDB which is fully managed C# and can run in memory with 'full' SQL2005 TSQL support. There is currently a CodePlex project developing an nhibernate dialtect for it, in alpha stage right now.

Jay
01/24/2008 09:59 PM by
Jay

I've been looking for ways to speed up unit testing against a database, and it really doesn't seem like there are any really good options out there.

SQL CE seemed like a good option but turned out to be a fairly bad product by all accounts.

VistaDB seems to be getting better and in theory is fairly SQL 2005 compatible, so you could develop against SQL 2005 while testing against VistaDB and without running into strange issues later, but would need additional licenses which could be OK if I intended to ever use it for anything other than unit testing - but at this point I don't.

Firebird seems neat and is fully supported by LLBLGen, which I like (Hi Frans!). It's also neat that (in theory) the only thing needed to switch from the embedded to server versions is a connection string change, but it doesn't support in memory databases to the best of my knowledge, so we're not really that much better off for unit testing performance purposes.

SQLite can do the in-memory thing, but it's not supposed by LLBLGen, and seems to have some other issues in general and also specifically with NHibernate, so I'm not sure what to think about it just yet.

If I primarily used NHibernate, I'd probably default to using SQLite at this point.

If VistaDB was literally a drop in replacement for SQL2005 (read: connection string change only), I'd probably purchase go that route, but it doesn't seem to be yet.

I wish there was an easier answer for this.

Anon
01/29/2008 04:00 AM by
Anon

select * from account where 1 = (select allow from permissions)

Does that query make sense?

Shouldn't it be:

select * from account where 1 in (select allow from permissions)

or something like:

select * from account, permissions where account.userid = permissions.userid and allow = 1

Ayende Rahien
01/29/2008 05:23 AM by
Ayende Rahien

Anon,

The real query is:

select * from account where 1 = (select allow from permissions limit 1)

I don't want the whole result set, I want the top one.

The second query affect the amount of data that the query return.

Comments have been closed on this topic.