Saving DB Access in Online Market

• Download source - 30.77 KB

Introduction

Think about [currency exchange rate] in an online market. This data must be in DB and developers will use that in the calculation for currency conversion. Given that the rate changes periodically once a day, at 6 AM sharp. Apparently the data will never change during 24 hours. Then we don't have to access DB every time we need that data until 6 AM next day if we already have that. As shown in the above diagram, substituting web server's local XML data for the real DB data will reduce DB access and eventually make DB happier.

Here, I described my approach to cache the periodically updated data in an online car market. Because the information and advertisement of a car don't change frequently, that could be a good example.

There is a similar technique, partial page caching in .NET. I will review that later in this article.

Sample

1. Open and execute 'SetUp_JustExecute.sql' file first. That will create a database, an account and a table.
2. Open the solution file in Visual Studio. It is a web application project. Build and execute. You will see below in web browser.
   
   Make sure the cache or xml file has been created in the project folder.

   

Using the Code

1. Import WebPageXmlCaching namespace by adding WebPageXmlCaching.cs to your .NET project. You will use only WebPageXmlCachingAPI class and Periodicality class in the namespace.
2. Decide which part of your code is to be cached. Codes around high cost DB query would be the main target.
3. Wrap the code region with the A, B two blocks. You will need to change some variables.(Click to enlarge)

   

   

   A. Notice "CarSpec" and Request.QueryString["carno"]; the XML cache file will be created like "[the page name]/CarSpec_[carno].xml". If you don't want different cache version along with "carno", just set that "" i.e. 'WebPageXmlCachingAPI.GetCacheInXml(Request, "CarSpec", "", ref strXMLCarSpec);'

   C#

   <%
       string strXMLCarSpec = null;
       WebPageXmlCachingAPI.GetCacheInXml(Request, "CarSpec", 
   		Request.QueryString["carno"], ref strXMLCarSpec);
       if (strXMLCarSpec != null ) Response.Write(strXMLCarSpec + 
   		" [Data from the cached XML, not from DB]");
       else
       {
           WebPageXmlCachingAPI.StartRecordingResponse(Response); 
   %>

   B. Notice Periodicality(...); You should specify when the cache will be updated.

   C#

   <%                   
           string strHTMLCache = WebPageXmlCachingAPI.StopRecordingResponse(Response);
           // at PM 8:00 every monday. There are more methods in 
           // Periodicality class to tweak the cycle.
           Periodicality prd = new Periodicality
   		(DayOfWeek.Monday, TimeSpan.FromHours(20));
           // Save in a xml file
           WebPageXmlCachingAPI.SetCacheInXml(Request, 
   		"CarSpec", prd, Request.QueryString["carno"], strHTMLCache);
       }
   %>

   Note two pairs of methods: Get/Set and Start/Stop. You might be aware of how to use them:

   C#

   WebPageXmlCachingAPI.GetCacheInXml(Request, "CarSpec", "", ref strXMLCarSpec);
       WebPageXmlCachingAPI.StartRecordingResponse(Response);
       WebPageXmlCachingAPI.StopRecordingResponse(Response);
   WebPageXmlCachingAPI.SetCacheInXml(Request, "CarSpec", prd, "", strHTMLCache);
   

4. Compile and execute. At the very first execution, the car data will come from DB. But from the next execution, the data (HTML fragment) will come from the local XML file. You will feel how fast the page opens because it doesn't access DB. Make sure the cache file is created. Until expiration time, the web server will continue to substitute local XML cache for remote DB data.

Discussion

I guess this situation always happens in every online market: information of goods usually don't change for a while. So the periodically updated data could be an easily recognizable target for caching to reduce DB overhead.

There could have been many similar technologies including partial page caching in .NET:

//After deploying a 'user control' on page, specify below 
<%@ OutputCache Duration="3600" VaryByParam="carno" %>
//the cache will be expired after 60 minutes and 
//the cache will create and manage different versions 
//of cache based on URL parameter 'carno' 

But suppose I have 5000 goods to display. Unfortunately I don't totally trust the mechanism behind this user control caching in .NET though I guess it must be very efficient.
Besides, the 'duration' doesn't meet my needs. Provided that the data will be updated at 6 AM everyday. If the page has been called 5:59 AM, what will happen next? The expired, meaningless data will last up to 6:59 AM (for 3600 seconds). There is absolute-time based cache expiration but that expiration happens just once. Automatic repetition is needed in this case.

So I invented my own caching:

1. to verify the cache contents with my own eyes by opening the XML file directly
2. to update cache periodically and more sharply
3. to apply to other web server environment that is not .NET

Though I've tried to simplify A and B blocks, the above ones were my best. I'm sorry that MACRO is sparingly used in C#. MACRO can generate long codes in a simple function. Maybe other languages can apply this caching technique more simply using MACRO.

Further Study

1. Framework for the 'currency exchange rate' kind data caching
   I cached HTML fragment inside CDATA tag in XML file. But what about using XML file as a small read-only DB that is periodically synchronized to the heavy DB? This could be possible because DB schema can be converted to XML without a loss.
   Imagine a DB access framework that encapsulates both remote DB and local XML data inside of it. Though web programmers will send DB query through the interface of this framework just as they have been doing, this framework will return the data after the data source choice between remote DB and local XML that has cached mother DB.
   I heard Microsoft SQL Server offers a functionality that could been used here: SSRS(SQL Server Reporting Services). Unfortunately I don't know much about that. At the moment, I guess people who want to store most business logic in web server not in DB would prefer to locate the code for caching regulation also in web server not in SSRS.
2. Extending this technique to other web pages, not just .NET
   I hope to implement the above idea in server-side COM object that could have more chance to be used in various server languages if the web server is a Microsoft product.

History

• Article created