Friday, June 22, 2007 1:23 PM
The project I'm currently wrapping up is a large .NET Windows "Smart Client" application (which I'm sure you'll hear more about in upcoming posts). It actually has the capability of working in "offline" mode and we have a pretty extensive infrastructure to cache data locally and store it for offline use in a SQL Server 2K5 CE database (which we auto-deploy with the rest of the app via ClickOnce).
Unfortunately, we've recently hit somewhat of a snag when our offline cache grows significantly. We have a test case where (after loading a large amount of data into the application via a saved document) we end up with 90,000+ lines of data in a cache table that we want to write out to the SQL CE database when the application closes. Basically, it requires 90K of simple insert statements in a loop. This is where it's readily apparent that SQL CE is not a full SQL 2K5. These 90,000 inserts take over 10 minutes to complete.
We're currently evaluating any ways to increase the speed of this process (or provide the users the option to bypass). We can show a progress bar but who likes to watch a progress bar for 10 minutes when all you want is to exit an application? :)
Other than performance in situations like this (and lacking the ability to use stored procs) we have been pleasantly suprised with the SQL CE option for offline data. We've gotten pretty good at using it as well.
If you need this type of functionality (or want to know what else it is capable of) check it out here:
http://www.microsoft.com/sql/editions/compact/default.mspx
-Kev