Lottery Post Journal

Anyone else noticing the big performance boost since late last night?

I have been trying to get an advanced caching technique to work, and finally figured out what I needed to change last night.  Good thing I didn't give up at 1:00 AM when I started getting tired!

Basically the way it works is that I set up a sort of message conduit, called a SQL Dependency Cache, between the SQL Server 2005 database and the ASP.NET 2.0 server.  The web server notifies the database that some of the important queries that are made often should be monitored, and new values retrieved only if a change has been made to the affected row(s) in the database.  Then the web server stores the results of the latest query in memory (in "cache").

So the database watches those rows, and if a change is made, it sends a message to the web server, notifying it that what it has in cache for those rows is no longer valid, so the next time the web server needs that data, it fetches the data from the database rather than the cache.  And then a new database monitor is placed on those rows until the next time it is changed again, and so on.

An example of a data that is cached would be information about a particular member, such as their username, profile info, number of posts made, board settings, etc.  It is accessed every single time a new page is presented, so it's obviously a very good candidate for caching.  An example of changed data would be if the member posted a new message to the forums, so their number of posts counter would be incremented by one.  Once that happens, the old data stored on the web server for that person is no longer valid, so the next time the data is needed it is gotten from the database instead of the cache.

It is actually as complicated as it sounds, which is why this kind of stuff can be so difficult to implement, and why it's a big deal once it finally works.  It's also very cool, from my perspective, to see the huge direct impact it has on everything working much faster.