Implementing the Design on the Client
In this article, the client I refer to is the Web application. I implemented this article's client solutions in ASP and ASP.NET, but you could just as easily implement the clients in Cold Fusion, CGI, IBM WebSphere, and other Web-application platforms. Both sample clients I provided in this article have a dynamic Web page that retrieves all the rows from the Northwind database's Customers table, then displays the count of all customers to the screen.
When executing, each client application's Web page must perform the following steps:
- Try to get the customer data from the cache.
- If the data isn't in the cache, perform the following steps:
- Get the customer data from the database.
- Cache the customer data locally.
- Create a cache dependency in the database.
- Create a trigger on the database table.
- Display the count of records in the set of records.
Of the Web page's execution steps, the only ones that really participate in my design are the ones that create the cache dependency in the database and create the trigger on the database table. To create the database cache dependency, you need to call the sp_CreateCacheDependency stored procedure, which Listing 5 creates.
This stored procedure accepts as its parameters the URL for the Web page that invalidates the local data cache and the database table that the cache is dependent on. The procedure first checks that no record already exists in the database for the given URL, then inserts the data into the CacheExpiryUrl and CacheDependency tables.
To create the trigger, the code from the Web application sends a dynamic SQL string to the database server and executes it. To perform this action in SQL Server, the user must be a member of the db_ddladmin fixed database role. Although the Web applications in the example solution create the trigger dynamically, you could also create it ahead of time.
In addition to the execution steps I just mentioned, each Web application must implement a Web page that does nothing but invalidate the local data cache. This client piece of the design is extremely important because it completes the cache-invalidation sequence diagram that Figure 2 shows. It's important that the Web page does nothing else because this page's URL is called by the database server's job, and you want this page to return a response to the database server as quickly as possible.
In the code downloads for this article, which you can find at InstantDoc ID 43982, I include a script that creates all the supporting database tables, constraints, indexes, stored procedures, and even a new database user that the client applications use in their database connection strings. I also include full client implementations in both classic ASP 3.0 and ASP.NET, using the .NET Framework 1.1. To get the client solutions working, you just need to create virtual directories that point to the solution files, then run the database script on your SQL Server 2000 database.
In this article, I showed you how to create database cache dependencies for your Web applications and implement the solution on one database platform and two Web-application platforms. This solution builds on the good ideas of others but takes those ideas a few steps further with more emphasis on performance, scalability, and flexibility. The solution is highly scalable because all the work required to invalidate the database cache dependency occurs on a separate thread. By leveraging the ideas and techniques in this article, you can confidently build database caching into your Web applications without having to worry about stale data.
End of Article
Prev. page
1
2
[3]
next page -->
