Clever Geek Handbook

Cache data in SQL CE database

Background

I have a SQL CE database that is constantly updated (every second).
I have a (web application) that allows the user to view data in real time. At some point, the user can click the "take a picture" button, and he will open the picture in another window.
And then there are "print" and "download" buttons in that form, which will either generate a page to print, or will transmit the data as a CSV file, but the same snapshot of data must be used , i.e. i cant go to db to get the latest data for this.

More details

  • SQL CE dabatase is exposed through WCF web service.
  • The snapshot consists of 500 records, 10 columns each. Enough expiration time on picture of 2 hours.
  • This is a low traffic application, so I don't expect more than a few (5) connections at the same time.
  • Reducing the snapshot doesn't really matter, the user can just generate a new one. Database access
  • implemented with native WCF web service using Linq-to-SQL.
  • The ASP.NET MVC website is hosted by UltiDev Cassini.
  • the database and website will most likely be in the same folder when deployed. The entire application is tied to the intranet.

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

Solution 1: Every time I need to take a snapshot, I will create a table in the database. Since there are no temporary tables in SQL CE, I will need to clear them myself.

Solution 2: In-memory snapshot cache on the DB server or on the web server.

Question: Is there something wrong with the proposed solutions? Any other solutions?

+2


source to share


2 answers


A typical use pattern is considered . Do most snapshots end up being printed or exported, or both?
If so, we can also "get it in memory" (temporarily) in the form of a non-blocking (asynchronous) select from device to server. This way, the data will "be there" or well on its way when the user decides to use it.



If, on the other hand, many snapshots are not used efficiently, solution # 1 looks quite fine (perhaps the table can be named after the account / user, so it is guaranteed to be "self-cleaning" based on the number of snapshots a user can maintain at a given point in time ( although it looks like it's just one, even with a tolerance to lose it sometimes).

+1


source


500 rows by 10 columns isn't really that big. For simplicity, in this case, I can simply generate the CSV data at the same time that the snapshot start page is generated, and then put the CSV data in a hidden field on the snapshot page. The Print and Upload CSV buttons then submit the form containing the CSV data to the Print page, which generates a printable version of the published CSV data, or a page that feeds the CSV directly back to the client browser, respectively. at least you wouldn't have any flushing problems and you avoid caching anything on the server (either in the cache itself or in the database) that might never be used at all.



If you have cached the CSV data in a hidden area on the client side, you can even handle both print and CSV display entirely on the client side with javascript, although I don't know if it's worth it or not.

0


source






More articles:


All Articles