Wednesday, 3 November 2010

Performance: Applying Output Cache Policies to a User Controls

One of the great features in ASP.NET is the rich caching framework.  I particularly like being able to:

  1. Define caching policies in the configuration file and then assigning those policies to groups of pages (via the @OutputCache directive).
  2. Cache User Control output for reuse on multiple pages (Partial Page Caching)

What I don’t like is the fact that you can’t combine the two!  You have to hard code your user control cache directives either in the mark-up (as @OutputCache directive) or by applying an attribute (System.Web.UI.PartialCaching) to the code behind, both of which require at least the duration to be hardcoded.

This is a massive oversight, as best-practise ASP.Net dictates precompiling websites prior to production which means caching rules can’t be tweaked without frequent rebuild/deployment cycles.

Fortunately, we can work around the issue!

As with most of my workarounds, the trick is a new Extension Method to dynamically alter the UserControls Cache Policy:

   1: using System;
   2: using System.Linq;
   3: using System.Web.Configuration;
   4: using System.Web.UI;
   5:  
   6:  
   7: namespace MartinOnDotNet.Helpers.UI
   8: {
   9:     /// <summary>
  10:     /// Useful extension methods for Controls
  11:     /// </summary>
  12:     public static class ControlExtensions
  13:     {
  14:  
  15:  
  16:         private static OutputCacheSettingsSection _outputCacheSettings = ((OutputCacheSettingsSection)WebConfigurationManager.GetSection(@"system.web/caching/outputCacheSettings"));
  17:  
  18:         /// <summary>
  19:         /// Applies the cache policy.
  20:         /// </summary>
  21:         /// <param name="control">The control.</param>
  22:         /// <param name="cachePolicyName">Name of the cache policy.</param>
  23:         public static void ApplyCachePolicy(this UserControl control, string cachePolicyName)
  24:         {
  25:             if (control == null) throw new ArgumentNullException("control");
  26:             if (string.IsNullOrEmpty(cachePolicyName)) throw new ArgumentNullException("cachePolicyName");
  27:             if (_outputCacheSettings == null) return;
  28:             OutputCacheProfile profile = _outputCacheSettings.OutputCacheProfiles.OfType<OutputCacheProfile>()
  29:                 .Where(p => string.Equals(p.Name, cachePolicyName, StringComparison.OrdinalIgnoreCase))
  30:                 .FirstOrDefault();
  31:             if (profile == null || !profile.Enabled) return;
  32:             control.CachePolicy.SetExpires(DateTime.Now.AddSeconds(profile.Duration));
  33:             if (!string.Equals(profile.VaryByParam, "none", StringComparison.OrdinalIgnoreCase) && !string.IsNullOrEmpty(profile.VaryByParam))
  34:                 foreach (string key in profile.VaryByParam.Split(new char[]{';'},  StringSplitOptions.RemoveEmptyEntries))
  35:                     control.CachePolicy.VaryByParams[key] = true;
  36:             else
  37:                 control.CachePolicy.VaryByParams.IgnoreParams = true;
  38:             if (!string.IsNullOrEmpty(profile.VaryByCustom)) control.CachePolicy.SetVaryByCustom(profile.VaryByCustom);
  39:             if (!string.IsNullOrEmpty(profile.VaryByControl)) control.CachePolicy.SetVaryByCustom(profile.VaryByControl);
  40:  
  41:  
  42:         }
  43:  
  44:     }
  45: }

To use it each user control will need to have a line added to the OnInit override in the codebehind (*.ascx.cs):

   1: using System;
   2: using MartinOnDotNet.Helpers.UI;
   3:  
   4: namespace MartinOnDotNet.Helpers.BrandingDemo
   5: {
   6:     /// <summary>
   7:     /// Example of a cached usercontrol
   8:     /// </summary>
   9:     public partial class CacheExample : System.Web.UI.UserControl
  10:     {
  11:         /// <summary>
  12:         /// Raises the <see cref="E:System.Web.UI.Control.Init"/> event.
  13:         /// </summary>
  14:         /// <param name="e">An <see cref="T:System.EventArgs"/> object that contains the event data.</param>
  15:         protected override void OnInit(EventArgs e)
  16:         {
  17:             base.OnInit(e);
  18:             if (this.CachePolicy.SupportsCaching)
  19:                 this.ApplyCachePolicy("Test");
  20:         }
  21:    
  22:     }
  23: }

The ascx file will need a place holder @outputcache directive:

   1: <%@ Control Language="C#" AutoEventWireup="true" CodeBehind="CacheExample.ascx.cs"
   2:     Inherits="MartinOnDotNet.Helpers.BrandingDemo.CacheExample" %>
   3:  
   4: <%@ OutputCache Duration="1" VaryByParam="none" Shared="true" %>
   5:  
   6: <% = DateTime.Now.ToString("G") %>

The values in the directive act as defaults and will be overridden by parameters in the matching Cache Policy (Test, in the code above), apart from Shared which isn’t included in the config.

Finally, define you cache policy in the web.config file:

   1: <system.web>
   2:     <caching>
   3:         <outputCacheSettings>
   4:             <outputCacheProfiles>
   5:                 <add name="Test"
   6:                          duration="3600"
   7:                          varyByParam="none" varyByCustom="DATE:yyyyMMddHHmm"/>
   8:             </outputCacheProfiles>
   9:         </outputCacheSettings>
  10:     </caching>
  11: </system.web>

The varyByCustom attribute in the rule above refers to some custom code in the Global.asax using the GetVaryByCustomString method.  In this case the code will vary by the current date in the given format making the cached version of the control rollover every minute.  This is a bit wasteful, as the cache will hang on to each version of the control for 3600 seconds (an hour) but proves the principle works.

Happily, the outputCacheSettings element of the web.config file supports configSource so you can actually apply different profiles for different environments by using an external config file.

Happy Caching!

Monday, 1 November 2010

SQL: Cleaning Up After the Database Tuning Engine Advisor

I’ve recently had to do a quick bit of DB performance analysis work on a 3rd Party database to see if I can improve performance(without being too invasive).  The quickest way to do this, is using the Database Tuning Engine Advisor tool that comes with SQL 2008 (the best way is to have loads of experience and a tonne of time to properly analyse the database).

Part of my analysis procedure was to make a script of queries that I could run with and without the DTA recommended indexes to evaluate any real world improvement.  This meant I needed to be able to roll back any indexes created by DTA.  Unfortunately, DTA gives you and install script for the new indexes but not a removal script.  I needed to write something myself.

Fortunately, the DTA created indexes and statistics are all prefixed with _dta_ so it’s relatively easy to write a query to remove the automatically created scripts:

First to drop the generated statistics:

   1: DECLARE @dtaStats AS TABLE(Id INT IDENTITY(1,1), StatName VARCHAR(255), TableName VARCHAR(255))
   2: DECLARE @currentId AS INT
   3: DECLARE @lastId AS INT
   4:  
   5: DECLARE @statName VARCHAR(255)
   6: DECLARE @tableName VARCHAR(255)
   7:  
   8: INSERT INTO @dtaStats(StatName,TableName)
   9:     SELECT name, OBJECT_NAME(OBJECT_ID) AS TABLENAME
  10:     FROM sys.stats
  11:     WHERE name like '_dta_stat_%'
  12:  
  13: SET @currentId=NULL
  14: SET @lastId=0
  15:  
  16: SELECT @currentId=MIN(Id)
  17: FROM @dtaStats 
  18: WHERE Id>@LastId
  19:  
  20: WHILE (@currentId IS NOT NULL)
  21: BEGIN
  22:  
  23:     SELECT @statName=StatName, @tableName=TableName FROM @dtaStats WHERE Id=@currentId
  24:     
  25:     PRINT 'DROP STATISTICS ' + @tableName + '.' + @statName
  26:     
  27:     EXEC('DROP STATISTICS ' + @tableName + '.' + @statName)
  28:     
  29:     SET @lastId = @currentId 
  30:     SET @currentId=null
  31:  
  32:     SELECT @currentId=MIN(Id)
  33:     FROM @dtaStats 
  34:     WHERE Id>@LastId
  35:  
  36: END
  37:  
  38:  

Then drop the generated indexes:

   1:  
   2: DECLARE @dtaIndex AS TABLE(Id INT IDENTITY(1,1), IndexName VARCHAR(255), TableName VARCHAR(255))
   3: DECLARE @currentId AS INT
   4: DECLARE @lastId AS INT
   5:  
   6: DECLARE @IndexName VARCHAR(255)
   7: DECLARE @tableName VARCHAR(255)
   8:  
   9: INSERT INTO @dtaIndex(IndexName,TableName)
  10:     SELECT name, OBJECT_NAME(OBJECT_ID) AS TABLENAME
  11:     FROM sys.indexes
  12:     WHERE name like '_dta_index_%'
  13:     
  14: SET @lastId=0
  15: SET @currentId=NULL
  16:  
  17: SELECT @currentId=MIN(Id)
  18: FROM @dtaIndex 
  19: WHERE Id>@LastId
  20:  
  21: PRINT @currentId
  22:  
  23: WHILE (@currentId IS NOT NULL)
  24: BEGIN
  25:  
  26:     SELECT @IndexName=IndexName, @tableName=TableName FROM @dtaIndex WHERE Id=@currentId
  27:     
  28:     PRINT 'DROP INDEX ' + @IndexName + ' ON ' + @tableName
  29:     
  30:     EXEC('DROP INDEX ' + @IndexName + ' ON ' + @tableName)
  31:     
  32:     SET @lastId = @currentId 
  33:     SET @currentId=NULL
  34:  
  35:     SELECT @currentId=MIN(Id)
  36:     FROM @dtaIndex 
  37:     WHERE Id>@LastId
  38:  
  39: END
  40:  
  41:  

The database is now back to a pre-DTA’ed state!