Back Issues  •  About our Experts

February 1999

Caching SQL data in the IIS Application object

As good developers, we do our best to make our code as data-driven as possible. Back in the hay days of client-server development, this was relatively easy. While we distracted the user with a splash screen, we'd open up our connections to the database, load all the static data, and build our global structures. If there were any danger that this data was only semi-static, we could easily fire off a background thread to periodically check for any new data—and rebuild the structures if needed.

Now that we're in an n-tier world ruled by cruel, stateless Web servers, our old, tried and true techniques no longer work. This presents us with an unpleasant tradeoff. On the one hand, you can open an expensive database connection and fetch the exact same static data for every user that loads a page. Performance and scalability would suffer but your application would be very easy to maintain. Conversely, you can create an extremely fast site by using static HTML pages that hard-code your lookup values. Sure, your site will scale to millions of users—but you'll have a difficult time keeping your pages current.

In this article, we're going to show you a middle ground where you can cache your static data in ASP's Application instead and refresh it only when necessary. We're going to start by showing you the end result—the HTML code that's sent to users when they request a static Web page containing a combo box of lookup values.

Next, we're going to walk you through dynamically generating the same combo box by pulling the values out of a database. We'll then take this a step further and show you how to cache this data in IIS's Application object and gain a four-fold increase in performance by saving the database hit every time the page loads. Finally, we're going to create a simple page to enable an administrator—or even a SQL trigger—to update the cached object when the underlying data changes.

We've chosen to use the Northwind sample data that ships with both SQL Sever 7.0 and all versions of Microsoft Access. That way, you can cut and paste the sample code in this article into your own environment and begin working right away. Once you have our sample pages running, you can easily substitute your own databases and tables and start creating high-performance Web applications based on your own data.

Creating a static HTML combo box

Figure A: Our sample page shows a combo box containing the Northwind confections.

The code snippet in Listing A shows the HTML we used to render the combo box on this page. As you can see, it contains all of the confection items from the Products table in the Northwind database.

Listing A: HTML used to build a combo box

<SELECT name=cboProducts>

When you create a combo box on a Web page, you delineate it by using the HTML <SELECT>...</SELECT> tags and you identify the combo box elements by using the <OPTION>...</OPTION> tags. You break an <OPTION> tag into two components: the displayed text and the value you want associated with it.

When the browser draws the page, it uses the text between <OPTION> tags as the elements of the combo box. When the user clicks the Submit button, the browser sends the VALUE portion. The VALUE portion is optional; if you don't set it, the browser will send the actual text of the selected item.

While it might initially seem preferable to receive the actual text selected rather than some code, in most database applications you'll want to get the primary key associated with a textual entry. That's exactly what we've done here; we've used ProductID (the primary key of the Products table in the Northwind database) as the VALUE in our combo box. This way, the page that processes the user's input can directly insert the passed value into a table in which we're storing user responses without having to look up the ProductName to get the primary key.

You can see that creating pages using this approach is quite easy. All you need to do is to create the HTML SELECT snippet once and paste it into the pages where you want to enable the users to pick a product—in our example, a confection. Most applications have a couple dozen such pages ranging from the product catalog and the shopping cart to the merchandizing and administrative pages—code reuse at its best. Well, maybe not.

This approach works fine until someone in the merchandizing department decides to add a new product to the items available for sale on your site. Now you have to manually update (and retest) dozens of pages over several servers in a Web farm. Sure, you could automate this with SED- or AWK-like utilities (programmable text editors), but there's a better way to keep your Web pages and database synchronized.

Filling the combo box dynamically

To dynamically populate the combo box, we created an ASP Web page called Dynamic.asp that uses ADO to open a connection to the database, issue a SQL SELECT statement (not to be confused with the HTML <SELECT> tag), and loops through the result set to build the HTML code for the combo box. Once again, we're going to concern ourselves with only the ASP code to create the combo box itself.

To simplify the listing here, we've skipped declaring the variables and removed the error trapping you should include in a production application. You can find the complete code with more extensive comments at www.infogenics.com/ZD/ViewDynamic.htm.

Listing B shows the first part of the ASP code where we open the ADO object and request the data from the server. Here, we're creating ADO Connection and Recordset objects. If you're new to ASP, the method of instantiating objects might look unfamiliar to you. The ASP runtime environment contains several built-in objects that enable the developer to interact with the Web page and the server. Here, we're using the Server object's CreateObject method. This method takes a progid as an argument and returns an instance of a server component to the ASP page, opening a connection to the database.

Listing B: ASP code to retrieve rows using ADO

'-- Instantiate ADO objects

Now that we've created the objects, we can go ahead and open a connection to the database. We do this by calling the Open method of our connection object, cnNorthwind, and passing it a system DSN, a login name, and a password. Once we've connected to the database, we can open a cursor so that we can fetch the data.

To do this, we use rsProducts, our record set object, and call its open method. This method requires a minimum of two arguments—the SQL query and the connection to use—but you should also tell ADO what type of cursor you want. Because we're simply looping through the result set once and then closing it, we'll get the best performance by using a read-only, forward scrolling cursor—commonly called a firehose cursor.

Creating the HTML for the combo box and sending it to the browser

First, notice that we're using another one of ASP's intrinsic objects, the Response object. This object enables you to manipulate the HTML stream sent back to the browser. We're using the Write method and passing the text string we want to send to the browser.

Listing C: Creating the combo box with ASP

'-- Send the opening SELECT tag to the browser

We start and end this code section just as we did our first example containing static HTML: we use the HTML <SELECT> tags to delineate the combo box. Between these two tags, we need to send to the browser a series of strings containing the <OPTION> tags that hold the elements in the combo box. We do this with a standard Do...Loop where we examine each row of the rsProducts record set and build a string by concatenating the opening <OPTION> tag, the ProductID and ProductName fields, and finally the closing </OPTION> tag. Once we have this string, we send it to the browser with a Reponse.Write and then we move on to the next row of the record set.

Once IIS has executed this script, it sends plain HTML back to the browser. In fact, the dynamically created HTML is identical to the static HTML we created in the first example. That's the good news. The bad news is that this method is significantly slower than straight HTML—about 20 times slower. In the next section, we'll show you how to have a dynamic combo box and gain some of that performance back.

Caching a dynamic combo box

Up until now, all of the variables and objects had what's called page-level scope—meaning that they existed only for the duration of the page. ASP has two other scoping levels: application and session.

A session-level variable is private to an individual user and remains active throughout that user's visit. By using session-level variables, you can create statefull applications—thus allowing you to store a user's information on the server and access it from all of the pages in your application. We could cache our combo box here, but it would waste a lot of memory and we'd have to hit the database to create a new combo box for each user.

An application-level variable is a single instance shared among all users and it remains in scope as long as the application is running. In terms of ASP, an application is defined as all of the pages in a directory and its subdirectories. An application starts the first time a user requests a page in the directory and ends when the Web site is shut down. In other words, any data we store in an application variable is available to all users and never has to be re-created. This is the perfect place to cache our combo box, but how?

Using the Application object

In the last example, shown in Listing C, we created individual strings for each part of the combo box and wrote them separately back to the browser. In this example, we need to build a single string that contains the entire combo box including the <SELECT> tags. To make sure the resulting HTML is as readable as possible, we've added hard returns after opening the <SELECT> tag and each of the closing <OPTION> tags.

Listing D: Procedure to create an application-level combo box

Sub GetProductsConfections

Once we've built the HTML for the combo box, we need to store it in the Application object. Because the Application object is shared by all users, we must be sure to lock it before updating any of its properties to prevent multiple users from updating it at the same time. We do this by calling the Lock method, updating the variable, gsProductsConfections in our example, and then calling the Unlock method.

We have now created a new, application-level variable and we're ready to use it in our page, as shown in Listing E. There you have it; in just four lines of code (seven if you count comments) we've placed the code that retrieves our cached combo box. We do this by retrieving our application variable gsProductsConfection and checking to make sure it isn't empty. If it's empty, we call the procedure from Listing D to create and store the combo box in the application variable. Once we're sure the cached combo box exists, we call Response.Write and send the HTML text containing the combo box to the browser.

Listing E: Retrieving the combo box from the Application object

'-- Make sure applicaiton variable is populated

The best part of this technique is that it's linearly scaleable. Were you to dynamically build the combo box for each user, at some point the traffic would overwhelm your database server because every single user must query the database. By caching the data in IIS, you can support an unlimited number of users by continuing to add new servers to your Web farm, because those additional users have no impact on the database. Infinite scalability!

Not quite. With every bit of good news there's bad news to follow. The cached combo box is indeed four times faster than building one dynamically for each user, but it's still not as fast as straight HTML because parsing and interpreting the ASP code requires quite a bit of overhead. In addition, the final problem we need to overcome is the same one that affects all caching strategies: coherence.

Refreshing the cached combo box

To solve this problem, we need a means of refreshing the cache on demand. To do this, we can create an ASP page called ClearCache.asp. The relevant section of this page is shown in Listing F. When the Products table changes, we simply point our browser at this page and we can update the cache whenever we want. This chunk of code does nothing more than clear the gsProductsConfections variable. That's it.

With the variable cleared, the next person to hit our Cached.asp page will find that the gsProductsConfections variable is empty when our code tests it at the beginning of the page. The code on that page will then automatically call the GetProductsConfections procedure to refresh the cache. The stale data problem is solved. Nevertheless, we could still do better.

Listing F: ASP code to clear the combo box from the cache

<%

Using this approach requires that someone hit the ClearCache.asp page every time the Products table changes. As you're bound to have several combo boxes based on different data throughout your application, making sure that each one is current at all times will quickly become an administrative nightmare. Worse, no matter how dutiful you are about it, eventually you'll get a phone call from an irate marketing person complaining that all of the new products aren't accessible on the Web page.

To avoid this hassle, you can create a trigger on the Products table that will automatically call the ClearCache.asp page. To fire off an HTTP request from SQL Server, you need to use an extended stored procedure. We'll use the xp_cmdshell extended stored procedure even though you might not use this in a production environment due to security concerns (especially if you're using SQL 6.5). The next part of our solution is a utility called HttpCmd.exe that's part of the IIS Resource Kit. While a detailed description of this utility is beyond the scope of this article, it's a command line browser.

Listing G shows how we roll xp_cmdshell and HttpCmd.exe together in our trigger. The most important sections of this snippet of code are the two SET lines. The first SET line defines the command we'll execute out at the shell. We're executing the HttpCmd program and passing it the address of the Web server—here, the loopback address—and a request file. The request file is nothing more than a text file containing a list of HTTP commands. Our request file contains the following single command:

GET /zd/ClearCache.asp HTTP/1.0

Listing G: Creating a trigger to automatically update the ASP cache

CREATE TRIGGER trgClearASPCache ON [Products]

Performance considerations

To give a perspective on the magnitude of the differences, Figure B shows the Performance Monitor log captured while we ran these three tests. The first thing to note is that we extended the vertical axis to 200 rather than the default 100 to make it more readable. This script increases the load until the CPU reaches 100 percent utilization—thus marking the maximum throughput the server can sustain.

Once we peg the CPU, we read the Total Requests Requests/sec counter of the Web Service object to plot the effective throughput. The black line represents the CPU utilization, which pegs at 100 percent immediately under the load of 20 constantly hitting the page. The solid while line represents the total requests serviced by the Web server—so the higher this line, the greater the number pages served (and the better your Web server's performance).

Figure B: We used Performance Monitor to compare the performance of static, dynamic, cached, and empty ASP pages.

As expected, static HTML gave the best performance. Somewhat unexpectedly, requiring the ASP engine to parse an empty Web page reduced throughput by over 75 percent. Next, retrieving the cached combo box from the Application object resulted in another 33 percent drop. Finally, creating that combo box with a dynamic database read shaved off another 75 percent—leaving us fewer than 4 percent of our original performance.

Conclusion

Copyright © 2001 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.

   Privacy Policy     Questions? Comments?