Search code examples
wcfcachingsql-server-ce

Cache data in SQL CE database

Background

I have an SQL CE database, that is constantly updated (every second).

I have a (web) application that allows a user to look at the data in real-time. At some point a user can click "take a snapshot" button, and it will open the snapshot in a different window.

And then on that form, there is "print" and "download" buttons that will either generate a page for printing, or will stream the data as CSV file - but same data snapshot has to be used, i.e. I can't go to the DB to get latest data for that.

Details

  • SQL CE dabatase is exposed through WCF web service.
  • Snapshot consists of up to 500 records, 10 columns each. Expiration time on the snapshot of 2 hours is sufficient.
  • It is a low-traffic application, so I don't expect more than few (5) connections at the same time.
  • Loosing snapshot is not a big deal, user can simply generate new one.
  • database is accessed by self-hosted WCF web service using Linq-to-SQL.
  • Web site is ASP.NET MVC hosted on UltiDev Cassini.
  • database, and web site are most likely be on the same box, when deployed. The entire app is intranet bound.

Problem I need to cache the snapshot of the data at the moment user pressed "take a snapshot" button, so that I can use same data to generate print page, or generate a file for download.

Solution 1:

Each time there is a need to generate a snapshot, I will create a table in the database. Since there are no temp tables in SQL CE, I will need to clean it up myself.

Solution 2:

Cache the snapshot in-memory on either DB server, or web server.

Question:

Is there anything wrong with proposed solutions? Any different solution suggestions?

Solution

  • A consideration is the typical usage pattern. Do most snapshots eventually result in either being printed or exported or both?

    If such is the case, we might as well "get it in memory" (temporarily) in the form of a non blocking (asynchronous) select statement from the device to the server. In this fashion the data will "be there" or well on its way when user decides to use it.

    If on the other hand many snapshot end up not being effectively used, Solution #1 seems quite ok (maybe the table could be named after the account/user, hence guaranteeing "self clean up" based on the number of snapshot a user can maintain at a given time (though it seems to be just one, with even the tolerance of loosing it sometimes).