Caching the DataGrid s DataSource at the Page Level

Caching the DataGrid's DataSource at the Page Level

In Chapter 6, we examined how to use external command buttons to provide filtering buttons. When initially presenting code to provide filtering button support, each time a filtering button is clicked, a database call is made, grabbing a portion of the total data depending on the filtering conditions.

In our discussions on this matter, we realized that each call out to the database is a bit wasteful. After all, on the first visit to the page, we display all the records from the table. Hence, it contains the records for any particular filter, and doesn't require a database access every time a filter button is clicked.

To overcome this, we use a DataSet for the data Web control's DataSource and then cache the DataSet in the ViewState. When a filter button is clicked, we can retrieve the DataSet from the ViewState, apply the filter, and rebind the filtered data to the data Web control. This approach eliminates the unneeded database accesses, but has two potential downsides. First, there is the potential for the cached data to become stale. That is, because the data being filtered is cached data from the page's first load, if records are added, updated, or deleted from the underlying database, the cached DataSet won't reflect those changes. Second, because the ViewState is maintained as a hidden HTML field, adding large objects to the ViewState can add several kilobytes. For example, a DataSet containing just the titles table from the pubs database will add roughly 20 kilobytes (20KB) to the HTML produced by the ASP.NET Web page when added to the ViewState.

In the previous code listings in this chapter, each time a sort header is clicked, we run a new SQL query, requiring a database access. As with the filtering buttons example, this database access might be superfluous if the database data does not change frequently. If the database is fairly static, the information retrieved when the page is first visited can be cached in the ViewState. When a DataGrid column's sort header is clicked, this cached data can be reordered and bound to the DataGrid.

As with caching a DataSet in the filter button example, this approach will save us a database access each time a DataGrid column's sort header is clicked. Of course, this approach carries with it the two aforementioned disadvantages: the cached data can become stale, and caching a DataSet in the ViewState will unquestionably lead to an increase in the HTML produced by the ASP.NET Web page.

To cache the data pulled in from the first page load, we need to use a DataSet in place of the SqlDataReader we have been using for our examples thus far. This is because a DataSet is designed as a disconnected data source, whereas the DataReader objects require an active connection to a data store. Furthermore, the DataSet contains a DefaultView property that is an instance of the DataView class. The DataView class is designed to provide a customizable view of a DataTable class instance to allow for sorting, filtering, and searching. (For an example of caching a DataSet, refer back to Chapter 6, Listing 6.4.)

To provide page-level caching support, we only need to make a change to one subroutine on the page: BindData(SortFieldName ). Listing 7.8 provides altered version of this subroutine. To integrate Listing 7.8 with the code from Listing 7.7, simply replace the BindData(SortFieldName ) subroutine in Listing 7.7 with the version presented in Listing 7.8.

Listing 7.8 The DataGrid Data Is Cached in a ViewState Variable to Save Database Accesses

  1:   Sub BindData(SortFieldName as String)   2:     'Check to see if we have a DataSet in the Viewstate   3:     Dim objDS as DataSet   4:   5:     If ViewState("TitlesDataSet") is Nothing then   6:       '1. Create a connection    7:       Const strConnString as String = "server=localhost;uid=sa;pwd=; database=pubs"   8:       Dim objConn as New SqlConnection(strConnString)   9:  10:       '2. Create a command object for the query  11:       Dim strSQL as String  12:       strSQL = "SELECT title, price, pubdate " & _  13:               "FROM titles " & _  14:               "ORDER BY " & SortFieldName  15:  16:       Dim objCmd as New SqlCommand(strSQL, objConn)  17:  18:       '3. Create a DataAdapter and Fill the DataSet  19:       Dim objDA as New SqlDataAdapter()  20:       objDA.SelectCommand = objCmd  21:  22:       objDS = New DataSet()  23:       objDA.Fill(objDS, "titles")  24:  25:       objConn.Close()  26:  27:       'Add the DataSet to the ViewState  28:       ViewState.Add("TitlesDataSet", objDS)  29:     Else  30:       'Get the DataSet out of the ViewState  31:       objDS = ViewState("TitlesDataSet")  32:  33:       'Apply the sorting  34:       objDS.Tables("titles").DefaultView.Sort = SortFieldName  35:     End If  36:  37:     'Finally, specify the DataSource and call DataBind()  38:     dgTitles.DataSource = objDS.Tables("titles").DefaultView  39:     dgTitles.DataBind()  40:   End Sub 

The BindData(SortFieldName ) subroutine in Listing 7.8 starts by declaring a DataSet variable, objDS, on line 3. Recall that to cache data, we need to use a DataSet as opposed to a SqlDataReader, which we used in our previous examples in this chapter. The BindData(SortFieldName ) subroutine is called the first time in the Page_Load event handler when the page is loaded for the first time. It is also called each time one of the DataGrid column's sort headers is clicked. The BindData(SortFieldName ) subroutine must be able to determine whether there is a cached DataSet in the ViewState. Line 5 makes this check if there is not a cached DataSet, lines 7 through 28 are executed; otherwise, the Else portion is executed (lines 30 through 34) .

If there is no cached DataSet, the code from lines 7 28 fills a DataSet with the appropriate SQL query. This code is roughly equivalent to the BindData(SortFieldName ) in Listing 7.7 the major difference is that in Listing 7.8 we are using a DataSet instead of a SqlDataReader. This filled DataSet is then added to the ViewState (line 28).

On the other hand, if a cached DataSet is found that is, if ViewState("TitlesDataSet") is not equal to Nothing we use the DefaultView on the DataSet to provide a sorted view of the data. We start on line 31 by referencing the cached DataSet by the local variable objDS. Next, the DataSet's DefaultView's Sort property is set to the SortFieldName input parameter (line 34).

Regardless of whether we fill a DataSet from the database or we retrieve it from the ViewState cache, we set the DataGrid's DataSource property to the DataSet's DefaultView (line 38) and call the DataGrid's DataBind() method (line 39).

This approach requires that the ASP.NET Web page perform only one database access: when the page is first loaded. The downside of caching data in this manner is that the cached data can become stale and caching the data in the ViewState results in a larger HTML output of the ASP.NET page. The penalty from this larger page size is felt twice it yields longer times for the client to download the page from the Web server, as well as longer times when performing postbacks, because the ViewState is maintained as a hidden form field that must be passed back to the server when the form is submitted.

When caching the DataSet at the page level, you might see some slight improvement in the Web server's performance. However, from the user's perspective, it will likely result in degraded performance, because he will have to wait longer to download the Web page and perform postbacks. The time the user spends waiting to download and upload the additional bytes will likely outweigh the extra time required for the Web server to make an extra database call. Therefore, I would suggest that this page-level caching technique only be used when the following two conditions hold:

1. All the Web site's users are on a high-speed connection. For example, if you are deploying a Web application on an intranet, you can rest assured that the extra kilobytes added in the ViewState will not seriously hamper the overall performance of the Web application.

2. The cost of a database access is too high. In the majority of cases, database access is quite fast. However, there are certain cases where database accesses can take up to several seconds to complete. For example, the Web server may reside in an office building in Los Angeles, while the company's master database that your Web site needs to access is located in the company's headquarters in Washington, D.C. Also, the query that needs to be run might be quite complex, or might be operating on a table with millions of records.

If both of these conditions hold true, you should consider using page-level caching techniques. More often than not, these two conditions will not be true, and therefore the standard "database access for every postback" model that we used in Listing 7.7 is preferred.

What About Using the Data Cache?

The .NET Framework provides a wonderful caching API, commonly referred to as the data cache, that allows for in-memory caching on the Web server. The caching API is fairly powerful when inserting items into the cache, you can specify that they exist for an absolute duration or a sliding duration. An absolute duration specifies how long an item should stay in the cache after it's placed there, regardless of its usage pattern. A sliding duration specifies how long an item should stay in the cache since its last use.

We have seen how to cache the DataSet in a page-level cache using the ViewState; we could opt to use the data cache instead. Through the use of the data cache, we would remove the two disadvantages of using the ViewState to cache a DataSet.

Because the data cache stores its contents on the Web server's memory, it adds any additional bytes to the ViewState this eliminates the problem of the additional page size. By using the data cache, the disadvantage of stale data can be removed as well. Items inserted into the cache can exist for an absolute or sliding time interval. In addition to this, items inserted into the data cache can have a caching dependency, such as when a particular file on the Web server's filesystem changes. That is, if an item in the cache has a file as its dependency, whenever that file is modified, the item whose dependency relies on that file will be evicted from the cache. Using this technique, a clever developer can make an item in the cache dependent upon when a particular row in the database. See the article "Invalidating an ASP.NET Cache Item from SQL Server" in the "On the Web" section at the end of this chapter for more information on this topic.

By tying a cached DataSet to a change in its underlying database table (an insert, update, or delete), any time the data is changed, the DataSet is evicted from the cache. Hence, when a call to the BindData(SortFieldName ) subroutine occurs, the DataSet is repopulated from the database, removing any chance of stale data.

Although using the data cache to store the DataSet might seem like an ideal option, it has one key drawback. Because the data cache's backing store is the Web server's memory, there is limited space. Imagine that you have a Web site with 25 pages that make database accesses, and the results of these accesses are dynamic, based on user input. Because these pages are dependent upon user input, you would need to cache a DataSet for each user. If you have, on average, say, 1,000 users visiting and using these data-bound pages, and your average DataSet requires, say, 50KB of memory, the total amount of memory needed to keep all this information in the data cache amounts to 50KB x 1,000 x 25 = 1,250,000KB, or 1.25GB.

The two conditions that make page-level caching in the ViewState worthwhile are users on a high-speed network, and a slow database connection. Data caching, on the other hand, should be used when the following two conditions hold:

1. Not all users are on a high-speed network. Some users might be accessing the data from a distant location, or could be using low-bandwidth devices, such as modems.

2. The cost of a database access is unacceptably high.

If condition 1 holds, it's clear that caching the DataSet in the ViewState is not an option, because of the bloated page size. If condition 2 holds, it's clear that simply making database accesses every time a user opts to sort the data differently is going to reduce performance. In this situation, caching the DataSet in the data cache would be ideal.

If, however, there is a low cost for a database access, you might find there is only a negligible performance gain (if any) when using data caching. For this unnoticeable performance gain, you will be taxing your Web server's free memory reserves, as well as providing less data cache memory to other ASP.NET pages in your Web application.