2

I have a DAL which is used to persist a large number of time-series measurements into a RDBMS database (MS SQL Server). There is also a web app which connects to this database and displays trends online (usually charts several months of data, with, say, 5-min min/max/average aggregates for various quantities and IoT devices).

Due to the amount of data, I would like to cache the aggregated results into a separate table, and it would be also pretty cool if I could have an additional layer of in-memory caching. However, devices often send historical data with older timestamps, meaning that the cache often needs to be invalidated, and there are two apps which access this data (the service which inserts/updates and occasionally reads the data, and the web application which reads the data).

I have looked into several options, like SQL triggers, or simply invalidating the table manually, but I am not sure how to propagate the cache invalidation to the web app properly.

Is there a way to somehow detect SQL events in my web app and invalidate cache? Or have a separate process for in-memory cache, which can also be invalidated? Or should I perhaps create my own third service which would be a proxy for all db operations, providing an in-memory cache of the data (this sure seems like an overkill)?

Lou
  • 4,244
  • 3
  • 33
  • 72
  • If you are using a SQL Server database you may want to consider using a stored procedure. – jdweng Oct 17 '16 at 09:55
  • @jdweng: can you elaborate? Why would stored procs be specific to SQL Server, and how is that related to caching? – Lou Oct 17 '16 at 09:59
  • The database is access by multiple applications so updating a table can't be performed in any one application. You can create a view (a temporary table) in SQL in a stored procedure and add a time-stamp to the view. Then the stored procedure will handling the updating to the view. The applications can call the stored procedure which will take care of updating the view. The view will be the cache. – jdweng Oct 17 '16 at 10:07
  • What do you mean by SQL Events? – Neo Oct 17 '16 at 11:24

1 Answers1

0

I do not believe a web application can detect sql server events directly.

Neo
  • 3,309
  • 7
  • 35
  • 44