ASP.Net - C # - SQL Server - Updating Cached DataTable with Modified Records Only

Question

I have a large DataTable cached in my web application that is the result of a complex query that returns a large dataset. Although this data table is cached, a query running to “refresh” this cache still takes a lot of time, mainly because of the large amount of data returned.

To speed this up, I am considering applying a timestamp approach to my tables to limit my query to only returning rows that have changed.

Then I am going to combine this smaller dataset with my cached data.

Has anyone done something similar to this, or is there anything out there that is already processing this?

I feel that it could be a re-creation of the situation with the wheel, if I immediately dive.

Answer 1

Personally, I used the timestamp method before, and it works well - it makes caching more efficient, only getting data that has changed since the last read.

As an alternative, I would suggest the SqlCacheDependency class, which will take care of updating the cache. I can not comment on any real pluses + minuses of this, or a performance comparison compared to a timestamp, since I did not use it myself.

Here is another useful article about SqlCacheDependency here

Update: Yes, I don’t think it really will update the data. It looks like you have to do it yourself. From the second link:

When data changes - and only then - cache elements, based on this data are canceled and removed from the cache. At the next request, this item is from the cache, if it is not in the cache, you can re-add the upgrade version to the cache and assured that you have the latest data

It also mentions special notes on SQL 2005 implementation in the 2nd link:

SQL Server 2005 controls changes to the result set for a particular SQL command. If a change occurs in a database that will change the results of the set of this command, the dependency causes the cached item to be invalidated. This allows SQL Server 2005 to provide a number of levels of notification.

I personally think that I will be suitable for a time-stamped approach (this is what I did before), since I do not see at first glance that SqlCacheDependency will give any performance advantages - I think it will be less efficient (just easier to implement). One day I will turn around to actually try SqlCacheDependency to do the right performance analysis :)

Update 2: Regarding merging new data into existing data, I think the Merge method is for the data you want.

The Merge method is used to combine two DataTable objects, which have basically similar schemes. Merging is typically used in a client application to incorporate the latest changes from a data source into an existing DataTable.
...
...
When combining a new DataTable source into a target, any source rows with a DataRowState Unchanged, Modified, or Deleted value are mapped to target rows with the same primary key value. Source rows with an added DataRowState value are added to new target rows with the same primary key values ​​as the new source rows.

You just need to make sure that you define the column (s) in the datatable, which are the primary key.