Saturday, 30 June 2012

VistaDB vs SQL Server Express for Multi-User apps

For the past 4 years I've been using VistaDB (version 3 or 4) in a number of development projects. For a SQL relational database with minimum dependencies, i.e. all it requires is the .Net framework, VistaDB is hard to beat and makes deployment as an embedded database real easy, adding little more than 1MB to an installer. Overall, as an embedded database in single user applications, I haven't found anything to compete with it.

VistaDB however does have a few drawbacks that make it unsuitable for larger, multi-user applications which means I'm now looking at using SQL Server Express in some new applications. VistaDB will still be a part of some of my future apps but not part of apps where I want to offer true multi-user capability.

In my experience, the key drawbacks of VistaDB for multi-user apps are:-

1. The lack of a client/ server model. Instead, each VistaDB client directly access the database file and Windows itself manages file locks and such. The result is that when 2 or more users try to access a database file simultaneously access times go through the roof and the app crawls along.

2. VistaDB's lack of paging support, e.g. something like MySQL's LIMIT, OFFSET commands, means that it is hard to avoid reading whole SQL tables of data from the DB to present in lists. Reading large amounts of data from a shared file can compound the Windows shared file performance drop from point 1.

3. In a multi-user app with concurrent access, SQL transactions are essential to ensure consistent updates across tables. Unfortunately, VistaDB transactions are extremely slow causing application performance to drop.

The points above aren't complaints about VistaDB - multi-user apps aren't what VistaDB is intended for. My only disappointment with VistaDB is that it's so good, I just wish there was a simple, easily deployable, client/ server version.

As I gain more experience with SQL Server Express, I'll try and list more pros and cons. One good tool I've found so far for use with SQL Server is OpenDBDiff. This is a free, open-source tool for comparing schemas of two different SQL Server databases and generating SQL scripts to make the schemas match. I can see this tool being essential to ease field upgrades of SQL Server DBs. With VistaDB today, I generate schema update code by hand, so it's a nice, early bonus to find an automatic way of doing this for SQL Server.

No comments: