Caching Database Query Results
XsltDb provides you with a bunch of technics for caching recordset that is returned by
- 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
##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
. Caching results in memory for 5 minutes or untill data is changed.
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.
. 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 -->
<!-- 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 > @LastMaxID)',
'@LastMaxID', mdo:sql('select max(ArticeID) from Articles', '$scalar #300')
. 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 -->
select Image, Title, Summary
where CreateDate > @date;
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.