Lightweight Database Cache Dependencies: Part II
In the previous installment of this discussion of database cache dependencies a-la ASP.NET 2.0, I alluded to some of the problems current implementations have, including the one in ASP.NET 2.0. Since the previous post my idea and its implementation got traction with patent lawyers, so I'm going to have to wait and see how it pans out with them and whether I may share any code (I also get to be the product architect at my company, so my hands are tied). At least, I can share my idea, and y'all can take it from there.
All implementations of database cache dependencies I've seen rely on triggers, additional tables, extended stored procs, and other heavy-duty stuff that requires more privileges than what is reasonable.
I can't help wondering why you can't use binary checksums to watch for changes. SQL Server supports a number of very helpful functions that read table, row or expression checksums.
In my code I cache table checksums and poll to detect changes. It's all done in one stored proc. My SQL Server login with minimal rights fits the bill just fine and I don't need to set up anything in addition to the stored proc. I could also build a SQL query on the fly and do away with the stored proc altogether!
I don't know if I'm overlooking some critical issue with this approach, but this solution has been powering my site by caching blog lists, articles and syndication feeds.
As with any approach there are gains and losses. I'm ignorant as to whether Oracle allows you to compute checksums the same way. Also, I'm polling for table changes, but you can get crafty enough and watch for changes in individual rows.
I'm going to leave it at that. I'd love to hear about your experiences with this technique.