Why SQL is not so widespread in large desktop applications?

Question

As a software developer, I've worked on projects ranging from tiny home-made apps to medium-size enterprise applications. In nearly every project I used a database or regretted choosing not to use it from the beginning.

Now, I am wondering a few things about the databases and their usage in general applications:

• Why Windows itself doesn't use any "central" SQL database? For example:
  • Errors Reporting data is stored in a bunch of files,
  • Windows Update stores everything in flat files,
  • Icons cache is stored in a very strange single file which doesn't seem to be accessed through SQL, etc.
• Why so many large applications avoid using databases? For example, wouldn't Microsoft Outlook gain by using a real database instead of reinventing the wheel by having its own format for .pst files and storing some data in registry?

If database adds an additional layer of an overall complexity and a tiny performance loss, it is a price of a huge advantage of making the code simpler in most circumstances, especially when it comes to the storage of small organized chunks of data instead of large binary streams. So why so few products are actually using databases? Probably the only application I know which actually uses Sqlite database is Firefox, and maybe Microsoft Exchange (but the last one is not a desktop application)?

Also, wouldn't a set of applications, like Microsoft Office or Microsoft Expression, benefit from having an unified SQL database, making it easier to deploy the applications, to update/upgrade the data, to share data between those applications, to make backups, etc.?

Answer 1

1) Generally speaking the overhead of running a full RDBMS is too great and it would be adding needless load to the system and complexity.

Having one installed makes your life easier as a developer but makes the life of the owner of the machine worse as their machine is likely to run slower and with more issues. In developer vs. user confrontations the user should almost always win.

2) Many data stores have specific needs which are not met by something like SQL Server Express.

For instance, error logs should be written to the simplest possible thing to maximise the chance that the write will happen and the data will be available. SQL Server is never going to be that simple.

For more complex applications the argument tends to be more around optimising for very specific user cases - flat files can be lightning fast.

Answer 2

Many applications embed SQLite. Quoting from their website:

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.".

You just use one of the available APIs, create a database, tables, etc and the SQL engine will store everything in a binary file that you can put anywhere within the user's file system. Best of all, it's FLOSS.

Answer 3

Windows ships with and uses a database engine named Extensible Storage Engine (ESE).

ESE is used by Active Directory, Exchange Server, Windows Mail and Desktop Search amongst many other Windows services and applications.

For development, open source ESE wrappers exist:

• ESE C#/C++ toolkit 1.2
• ManagedEsent for .NET

With ESE shipping with every modern version of Windows you have no database deployment concerns. Architecturally ESE fills a similar role to embedded databases such as SQLite.

Answer 4

Relational databases are not the most efficient data structure for every domain. Lots of desktop applications do use embedded databases, you just can't readily tell they are doing it if all you see are a couple of data files.

Answer 5

Why Windows itself doesn't use any "central" SQL database?

Actually, this idea has been in development at Microsoft for some time. It is called WinFS. It is an intriguing technology, and it is available to MSDN subscribers. Some days soon, I'm going to install it on a VM and play around with it.

Answer 6

Databases offer flexibility and like every feature there is almost certainly a cost. If a flat-file will work, then you can assume no one is going to need to query the data in unanticipated ways like in an ad hoc report.

Databases also do a good job of handling multiple connections/inputs. Again, if this isn't a requirement for your desktop app, why add the additional complexity? Other than through the user interface/the actual installation of Outlook itself, how many other applications or users are trying to write to the Outlook file on your computer?

It would be easier if all data were in a relational database from a "I want to be able to query all data any way I see fit with a single language like SQL" perspective, but this is rarely a priority with many applications.

Most programmers will look at it from:

• I know what the app is suppose to do, this is all it is suppose to do

• my app is the only entitiy that will ever access and/or manipulate these data

• a simple file structure of my own design will work

• for at least one application I'd like to avoid relying on other people's code and have complete control.