I am thinking about smart client architecture and trying to decide what the best local data store would be, either VistaDB or Sqlite. I thought about SQL Server 2005 Express, but the install requirements put it at a severe disadvantage to either of these super light databases.
Anyone out there have any experience with either of these databases? What about using NHibernate with them? I am leaning towards sqlite, especially after I looked at these benchmarks and the fact that there is already a nhibernate dialect for sqlite. (not to mention it’s free, open source, and has a very high space limitation)
-James
Reader Comment – Brenton House says,
“What about Sql Server Everywhere Edition? I don’t think it’s out yet,
but it probably is going to ship with SQL Server 2005 SP1. It doesn’t
require any kind of an install.
http://www.microsoft.com/sql/letter.mspx
http://pluralsight.com/blogs/jimw/archive/2006/04/06/21167.aspx“
Reader Comment - Jeff Lewis says,
I’m using sqllite on an Windows Mobile smart client app and it rocks.
All of the other db’s I tried were too slow in this low memory
environment, but with sqllite, I was able to get reasonable query times
for the very large db that the app uses.
Reader Comment – Catherine Devlin says,
”
I’ve never used VistaDB, but I really love SQLite. It’s just so ridiculously incredibly easy…
It’s
a problem if you want multiple processes to make updates at once,
however, because it does lock the entire file for update.
Also,
I’ve seen the query performance become disappointing when the file size
grows beyond a couple GB. You need a lot of data to get it there,
though, because it’s very low-fat (unlike, say, Oracle, which wants
about a GB before you add a single row of your own data).”
{ 21 comments }
you should look at Firebird also…
Sqlite 3 is ery cool. Make sure you check out the datatypes page, though. There are only 4 of them (I count NULL as a value not a type) Integer, Real, Text, Blob. Dates are not a native type.
VistaDB does not have full support for ADO.NET 2.0 but it looks like there are some ADO.NET 2.0 wrappers for Sqlite: sqlite.phxsoftware.com/…/
/>
Did you ever find out the benchmarks for VistaDB?
What about Sql Server Everywhere Edition? I don’t think it’s out yet, but it probably is going to ship with SQL Server 2005 SP1. It doesn’t require any kind of an install.
http://www.microsoft.com/sql/letter.mspx
http://pluralsight.com/blogs/jimw/archive/2006/04/06/21167.aspx
Firebird embedded is worth a look. It has a reasonable range of data types and a good .net provider.
http://firebird.sourceforge.net/index.php?op=devel&sub=netprovider&id=examples
I’m using sqllite on an Windows Mobile smart client app and it rocks. All of the other db’s I tried were too slow in this low memory environment, but with sqllite, I was able to get reasonable query times for the very large db that the app uses.
I have used Sqlite to write NHibernate Query Analyzer.
Granted, the SQLite tools are command line utils, but at least they are _very_ familiar to use.
There are also a couple of GUI front ends.
Never had a problem with performance, so can’t tell you about that. Very easy to use, but be aware that it is not very big on checking you.
It does not enforce data type constraints or size constraints, for instance.
I also don’t see a way to define FK, so it may be a problem.
In general, if you’re using NHib to handle those for you, you can just use it.
I’ve never used VistaDB, but I really love SQLite. It’s just so ridiculously incredibly easy…
It’s a problem if you want multiple processes to make updates at once, however, because it does lock the entire file for update.
Also, I’ve seen the query performance become disappointing when the file size grows beyond a couple GB. You need a lot of data to get it there, though, because it’s very low-fat (unlike, say, Oracle, which wants about a GB before you add a single row of your own data).
Wow, tons of great feedback. I think I am going to go with Sqlite and maybe checkout Sql Server Everywhere when it comes out. The nice thing about nhibernate is that it will be easy to switch.
To clarify some comments made here about VistaDB:
1. VistaDB includes full support for ADO.NET 2.0 and .NET 1.1. with a fully managed .NET Data Providers. Native integration with both Visual Studio 2003 and 2005.
2. VistaDB supports both single and multi-user data access (Shared and Exclusive database locking)
3. Snapshot isoloation level Transaction Processing is supported.
4. VistaDB Server (optional) provides true client/server data management. It is 100% TCP/IP based so it’s ideal for LAN or for accessing data over the Internet. The server is built using our embedded engine, so technically, you could develop your own server.
5. VistaDB supports a wide range of data types:
BLOB, Boolean, Character, Currency, Date, DateTime, Double, GUID, Int32, Int64, Memo, Picture and Varchar. NULL values are supported for each data types and Identify types are also supported.
Features are listed here if you’d like to learn more:
http://www.vistadb.net/features.asp
Anthony Carrabino
http://www.vistadb.net
(I am biased toward SQLite, so read the following with that in mind)
It really depends on what you want out of the database engine and what development environment you’re using. SQLite is smaller than VistaDb, but VistaDb has stored procs. SQLite’s ADO.NET 2.0 provider is far superior to VistaDb’s ADO.NET providers, though. VistaDb has very limited support for VS2005’s design-time environment, but SQLite’s support is pretty extensive. VistaDb also can’t be embedded on a smartdevice — no idea if that’s even important to you.
If your database is properly indexed, you can get pretty impressive speed with SQLite even with millions of rows in your tables. In some of the benchmark tests I ran a few months ago, VistaDB scored at the bottom consistently.
http://sqlite.phxsoftware.com/forums/622/ShowPost.aspx
The best thing about SQLite is that its public domain. No GPL, LPGL, no licensing restrictions whatsoever. You can dig into the engine and the ADO.NET 2.0 provider as far as you want to go.
Robert Simpson
http://sqlite.phxsoftware.com
Have you considered MySQL? I havent used it, but I hear good things about it…
James, you should take a look at this article….
http://weblogs.asp.net/jgalloway/archive/2006/04/12/442615.aspx
James – I’d definitely advise an abstraction layer like NHibernate. I did some simple tests with SQLite, made great progress, and decided it would work for me. Then when I tried to update and read from the table on different threads it all fell apart.
I think SQLite is okay for very simple database needs where small size is important.
Firebird is better if you need a solid database and you need a free product (open source project, etc.).
VistaDB seems like the best solution as long as you don’t mind paying for it.
I tested some others – SharpHSQL seemed promising, but wasn’t really stable when I last tested.
But whatever you do, go with an abstraction layer. I’m restarting an old project, and I plan to use NHibernate on top of Firebird, then switch to SQL Mobile when it’s released.
Check out Mimer SQL Mobile. It is full featured with stored procedures, views , triggers, multiuser transactions for Pocket PC.
http://developer.mimer.com/
Data provider written in 100% managed code is available as well
Welcome to furniture world: leather sofa, platform beds and sofa, outdoor fireplace. Furniture sites: [url=http://platformbeds.i3log.com]platform beds[/url] | [url=http://www.nivea.co.jp/frameset.php?content=http://cheep.info/leather-sofa.htm]leather sofa[/url] | [url=http://www.nivea.com/frameset.php?content=http://serben.info/outdoor-fireplace.htm]outdoor fireplace[/url].
I’m developing a client/server application using SQLite and so far speed/flexbility are great. Installation is also a snap.
BTW: if you want to migrate from SQL Server to SQLite, I’ve written a small utility in C# that can do this for you: http://www.codeproject.com/…/convsqlserverto
Good Luck
Liron
btw, your blog is running on vistadb
GraffitiCMS uses it
Actually I configured it to use SQL Server instead of VistaDB so I could have good backups and better performance.
Sqlite rocks. VistaDB is very very slow. u can test it yr self.
Comments on this entry are closed.