Caching Database Query Results

XsltDb provides you with a bunch of technics for caching recordset that is returned by mdo:xml or mdo:sql extensions.
  • using fixed period of time during which result set is valid in ASP.NET cache.
  • using SqlCacheDependency (Setting up SqlCacheDependency usage for your DotnetNuke Installation)
    • Polling model (does not require service broker)
    • Notifications (based on SqlDependency object).
      • Notification are setup with the same sql statement as data selection (for simple queries only)
      • Notifications are setup with simplified sql statements if original query is not suitable for notifications.
XsltDb differs queries by all parameters of mdo:sql that results can depend on. In other words cache key is created using
  • query text
  • parameter names
  • parameter values
  • entity names

Simple Database Cache Test

Let's create a simple XsltDb configuration


##live 1 smart
<xsl:value-of select="mdo:sql('select getdate()', '$scalar')" />
We can see that each second module is refreshed showing new time value. Now add a cache period as follows:


##live 1 smart
<xsl:value-of select="mdo:sql('select getdate()', '$scalar #5')" />
After that you can see that module is refreshed only each 5 seconds when browser still queries web server each second.

More database cache samples


Sample 1. Caching results in memory for 5 minutes or untill data is changed.


<xsl:variable name="data"
   select="mdo:sql('select * from SomeTable where SomeID = @id', 'some #300 SomeTable', '@id', 123)" />
We specified a table name after caching period and that means we keep results in memory for 300 seconds but if data in SomeTable is changed XsltDb invalidates cached data and database will be quired again. Using SqlCacheDependency we can significantly increase maximum caching time and keep pages fresh and valid at the same time.

Sample 2. Caching complex query using notifications.

If we want ot get notified when data is changed we have to use a very simple queries only. But if we have more complex query we can create another query that is simple enought and describes data range to be monitored. The syntax is:


<!-- simplified -->
tableName(where clause)

<!-- full -->
$query(select field1, field2 from dbo.SomeTable where ...)
<!-- Note, the full form requires schema owner to be specified explicitly -->
In the following sample 5 last articles is returned by query. But as far as we can't setup notifications for TOP N queries we have to create additional query for caching.


  <!-- Get last 5 articles
     ArticleID is IDENTITY so it can't be changed,
     new ArticleID is always greater than existing ones.
     So we can monitor articles added after last known.
     And, of cource, we cache last known ArticleID
   -->
  <xsl:variable name="latest-news" select="mdo:sql(
    'select top 5 * from Articles order by ArticleID desc',
    'article #300 Articles(ArticleID &gt; @LastMaxID)',
    '@LastMaxID', mdo:sql('select max(ArticeID) from Articles', '$scalar #300')
  )"/>

Sample 3. Caching simple query.

If you have a simple query that conforms notifiable query restrictions, you can use the same query for data extraction and for notificationn setup. This is most productive approach as far as you do not need to execute additional query just to setup notifications.


<!-- Here we retrieve articles areated TODAY: since 00:00 -->
<xsl:variable name="sql">
  select Image, Title, Summary
  from dbo.Articles
  where CreateDate > @date;
</xsl:variable>
<xsl:variable name="articles"
  select="mdo:sql($sql, 'article #300 $query', '@date', mdo:date('dd-MM-yyyy') )"/>
Here we use $query without parameter so XsltDb uses original query to setup notifications.

When To Use Caching

  • Caching is not a method to speed-up a slow query. If you have slow query you have to optimize it or denormalize your database and store data in form suitable for effective querying.
  • Caching is required when you have highly loaded system when thousands of users request the same data. If you show last articles feed on main page you probably show the list 100-10000 times before it is changed with new article. In this case you definitely should use caching to store latest articles in very fast in-process ASP.NET cache.


Last edited Feb 23, 2011 at 4:26 PM by findy, version 17

Comments

No comments yet.