Project database as SQL Server Compact database
I have just uploaded version 4.7x.0116 which has a new option for the format of the project database.
Previously, the project database was either:
- An Access database
- An XML file
Now there is a third option:
- SQL Server Compact database
There are two ways to select this format:
- For an existing project, you can switch the format via the project properties dialog (second tab)
- For a new project, you can set the default format in the Add-In's settings dialog, before scanning the project for the first time
The standard file extension for an SQL Server Compact database is .sdf, but I have used .mlsdf. Otherwise Visual Studio shows a database dialog when the file is added to the project, which I particularly wanted to avoid.
The option in the Add-In's settings dialog is new. Previously, the default was XML. For completeness, I have allowed you to select the Access database as the default, but actually I might remove this. I really only support the Access database for compatibility with older versions. In my opinion it is unreliable under Vista and Windows 7 (which might be related to using memo fields).
So why have I added this option?
Probably, it is pointless, but this is the reason. A number of users have problems with "out of memory" exceptions. One possible reason might be the memory required for the project database.
In the previous implementation, either Access database or XML, the complete project database is held in a dataset. Queries are made against the dataset, rather than against the database file. Using a strongly typed dataset loaded into memory makes a simple programming model. However, the bigger it gets, the more memory it will use. I don't really know how much memory a dataset uses, but I assume it is very inefficient.
The new option with SQL Server Compact edition uses a very similar dataset, but avoids using the built-in datatables. Instead it makes almost all queries directly against the database, and quickly discards the results (which can then be garbage collected).
Now I don't know how the SQL Server Compact database handles memory allocation. It might even load the whole database into memory. However, I guess that it is more scalable than a simple dataset. That is, the size of the database is not limited by the size of memory.
Does it help?
As far as I can tell, no.
However, I think that some users are working with projects which are much, much bigger than any project of mine. There is therefore a chance that it might help in cases with extremely large projects.
Phil