I want to use data caching on my .net C# application. So far I added data caching and added sql cache dependencies on specific tables. But thats not good enough. These tables will be updated too frequently but not relevant to a lot of the cached objects. This will make the data caching almost useless because it will be flushed to frequently. I want to implement sql cache dependency on specific rows for each object. How can I do that?
Using SQL cache dependency, you could cache your product information and create a dependency on a database table or row change. When the data changes—and only then—the cache items based on that data are invalidated and removed from the cache.
Cache dependencies allow the application to automatically clear cached data when related objects are modified. The system uses dummy cache keys to create dependencies between cached data and other objects. Dummy keys are cache items without any data that represent objects or groups of objects.
SQL Server caches temporary objects (temporary tables and table variables), that are created in a stored procedure. Temporary objects that are created either in dynamic SQL statement or by using sp_executesql are not cached. Temp table caching can lead to better performance by reducing Tempdb Allocation Contention.
In SQL Server, the buffer cache is the memory that allows you to query frequently accessed data quickly. When data is written to or read from a SQL Server database, the buffer manager copies it into the buffer cache (aka the buffer pool).
You need to understand how SqlDependency works. You subscribe a result set and get notified when that result set has changed. You can subscribe any kind of result set, that means any kind of query, as long as it conforms to the restrictions of the supported statements. It really makes no difference if is a table or a view.
So technically you can subscribe for specific notifications by submitting a query specific for that row, ie. with a hard coded WHERE clause. You would have to change your code to retrieve and cache only the needed data on a row-by-row basis as opposed to retrieving entire tables and caching them in memory. Heck, you'd have to do that anyway if you're at least concerned about the size of those tables. Caching entire tables should only be done for catalog and reference data that change infrequently or not at all.
You can also choose to retrieve and cache partitions of the data, ie. individual ranges of keys (say between 'A' and 'D', 'E' and 'H' etc and subscribe to be notified for that specific data partition.
If you want to understand how SqlDependency works my blog has some articles covering it, including common programming pitfalls of SqlDependency and deployment problems with SqlDependency.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With