Database caching of non dynamic content

/ By / Category: Code

We run a dynamic website that retrieves information from a relational database. There’s lots of content involved across loads of domains and in several languages, this makes the site fairly heavy. Performance is as important as ever and it’s only going to become more so as users patience decreases inversely as bandwidth increases. Mmm.

One solution is to cache content that doesn’t need to be refreshed that regularly. The time between refreshes can be anything, a few minutes, days, weeks or never. It’s a fairly simple process that lends itself to any scripting language that reads and writes to a database. The best part is that if you’re already reading content from database tables and displaying it, you only have to adjust your script to check another table for the cached content date and act accordingly.

Here’s a really simple example of how it might work, this ones in pseudo VB:


'Query your cache table for content
objRSNavBuild.Open "SELECT * FROM tblNavBuild WHERE BuildType = 2 AND CountryID=2 AND LanguageID=1"
'Set refresh variable
navBuildInterval = 720 '24 hours
'Set timestamp from query
lastBuilt = objRSNavBuild.Fields("timeStamp")

‘If lastBuilt is further back in time than navBuildInterval
if (DateDiff (“n”,lastBuilt,Now()) > navBuildInterval or isNull(lastBuilt)) then

‘get your content
objRSNav.Open “SELECT * FROM tblNav”
objRSFoot.Open “SELECT * FROM tblFoot”
objRSMenu.Open “SELECT * FROM tblMenu”
‘Write your content out
navContent = objRSNav.Fields(“content”)
footContent = objRSFoot.Fields(“content”)
menuContent = objRSMenu.Fields(“content”)

‘Display your content for immediate use
Response.write navContent & vbCrlf
‘Write your content for to a variable for storage
build = navContent & vbCrlf
‘repeat above
Response.writefootContent & vbCrlf
build = build & footContent & vbCrlf
Response.write menuContent & vbCrlf
build = build & menuContent & vbCrlf

‘write nav into database and timesatamp
objRSNavBuild(“navigationBuild”) = build
objRSNavBuild(“timeStamp”) = Now()
objRSNavBuild.Update

‘Otherwise write out your cached content
else
Response.write objRSNavBuild.Fields(“navigationBuild”)
end if