Views: 7955
Number of votes: 7
Average rating:

Speeding up FindPagesWithCriteria and General PageDataCollection based queries

I work on a few sites that use FindPagesWithCriteria extensively in various places and was wondering whether the EPiServer queries are as optimised as they could be.

I decided to open Reflector and start digging into the EPiServer API.  After a while I noticed a potential bottle neck.  For every page id that is returned from FastFindPagesWithCriteria a call is made to the GetPage method.  If the page does not exist in cache a stored procedure call (netPageDataLoad) will be made against the database. 

This means that if for instance the FastFindPagesWithCriteria call returns 2000 page ids and none of the pages exist in cache there will be 2000 separate hits against the database to populate the PageData objects.

After further reflection I noticed there are other stored procedures used by the API that accept a binary parameter containing page Ids.  I decided to implement my own version of netPageDataLoad that will accept a collection of page ids to load.  The stored procedure TSQL is below:


Download usp_netPageDataLoadBatched.txt

EPiServer CMS 6 version

   1:  SET ANSI_NULLS ON
   2:  GO
   3:  SET QUOTED_IDENTIFIER ON
   4:  GO
   5:  CREATE PROCEDURE [dbo].[usp_netPageDataLoadBatched]
   6:  (
   7:      @Binary VARBINARY(8000),
   8:      @LanguageBranchId int
   9:  )
  10:  AS
  11:  BEGIN
  12:   
  13:   
  14:      SET NOCOUNT ON
  15:      DECLARE @Pages TABLE (LocalPageID INT)
  16:      DECLARE    @Length SMALLINT
  17:      DECLARE @Index SMALLINT
  18:      SET @Index = 1
  19:      SET @Length = DATALENGTH(@Binary)
  20:      DECLARE @FirstPageID int
  21:      SET @FirstPageID = -1
  22:      
  23:      WHILE (@Index <= @Length)
  24:      BEGIN
  25:          IF @FirstPageID = -1
  26:          BEGIN
  27:              SET @FirstPageID = CAST(SUBSTRING(@Binary, @Index, 4) as int)
  28:          END
  29:          INSERT INTO @Pages VALUES(SUBSTRING(@Binary, @Index, 4))
  30:          SET @Index = @Index + 4
  31:      END
  32:   
  33:      /*This procedure should always return a page (if exist), preferable in requested language else in master language*/
  34:      IF (@LanguageBranchID = -1 OR NOT EXISTS (SELECT Name FROM tblPageLanguage WHERE fkPageID in (SELECT LocalPageID FROM @Pages) AND fkLanguageBranchID = @LanguageBranchID))
  35:          SELECT @LanguageBranchID = fkMasterLanguageBranchID  FROM tblPage
  36:              WHERE tblPage.pkID = @FirstPageID
  37:      /* Get data for page */
  38:      SELECT
  39:          tblPage.pkID AS PageLinkID,
  40:          NULL AS PageLinkWorkID,
  41:          fkParentID  AS PageParentLinkID,
  42:          fkPageTypeID AS PageTypeID,
  43:          NULL AS PageTypeName,
  44:          CONVERT(INT,VisibleInMenu) AS PageVisibleInMenu,
  45:          ChildOrderRule AS PageChildOrderRule,
  46:          PeerOrder AS PagePeerOrder,
  47:          CONVERT(NVARCHAR(38),tblPage.PageGUID) AS PageGUID,
  48:          fkArchivePageID AS PageArchiveLinkID,
  49:          ExternalFolderID AS PageFolderID,
  50:          CONVERT(INT,Deleted) AS PageDeleted,
  51:          (SELECT ChildOrderRule FROM tblPage AS ParentPage WHERE ParentPage.pkID=tblPage.fkParentID) AS PagePeerOrderRule,
  52:          fkMasterLanguageBranchID AS PageMasterLanguageBranchID,
  53:          CreatorName
  54:      FROM tblPage
  55:      WHERE tblPage.pkID in (SELECT LocalPageID FROM @Pages)
  56:      ORDER BY tblPage.pkID
  57:      IF (@@ROWCOUNT = 0)
  58:          RETURN
  59:      /* Get data for page languages */
  60:      SELECT
  61:          L.fkPageID AS PageID,
  62:          CASE L.AutomaticLink
  63:              WHEN 1 THEN
  64:                  (CASE
  65:                      WHEN L.fkPageLinkID IS NULL THEN 0    /* EPnLinkNormal */
  66:                      WHEN L.FetchData=1 THEN 4                /* EPnLinkFetchdata */
  67:                      ELSE 1                                /* EPnLinkShortcut */
  68:                  END)
  69:              ELSE
  70:                  (CASE
  71:                      WHEN L.LinkURL=N'#' THEN 3                /* EPnLinkInactive */
  72:                      ELSE 2                                /* EPnLinkExternal */
  73:                  END)
  74:          END AS PageShortcutType,
  75:          L.ExternalURL AS PageExternalURL,
  76:          L.fkPageLinkID AS PageShortcutLinkID,
  77:          L.Name AS PageName,
  78:          L.URLSegment AS PageURLSegment,
  79:          L.LinkURL AS PageLinkURL,
  80:          L.Created AS PageCreated,
  81:          L.Changed AS PageChanged,
  82:          L.Saved AS PageSaved,
  83:          L.StartPublish AS PageStartPublish,
  84:          L.StopPublish AS PageStopPublish,
  85:          CONVERT(INT,L.PendingPublish) AS PagePendingPublish,
  86:          L.CreatorName AS PageCreatedBy,
  87:          L.ChangedByName AS PageChangedBy,
  88:          -- RTRIM(tblPageLanguage.fkLanguageID) AS PageLanguageID,
  89:          L.fkFrameID AS PageTargetFrame,
  90:          0 AS PageChangedOnPublish,
  91:          0 AS PageDelayedPublish,
  92:          L.fkLanguageBranchID AS PageLanguageBranchID,
  93:          CASE 
  94:              WHEN L.PublishedVersion=W.pkID THEN 4    /* EPnWorkStatusPublished */
  95:              WHEN W.HasBeenPublished=1 THEN 5        /* EPnWorkStatusPrevious */
  96:              WHEN W.Rejected=1 THEN 1                /* EPnWorkStatusRejected */
  97:              WHEN W.DelayedPublish=1 THEN 6            /* EPnWorkStatusDelayedPublish */
  98:              WHEN W.ReadyToPublish=1 THEN 3            /* EPnWorkStatusCheckedIn */
  99:              ELSE 2                                    /* EPnWorkStatusCheckedOut */
 100:          END AS PageWorkStatus
 101:      FROM tblPageLanguage AS L
 102:      LEFT JOIN tblWorkPage AS W ON W.pkID = L.PublishedVersion
 103:      WHERE L.fkPageID in (SELECT LocalPageID FROM @Pages)
 104:          AND L.fkLanguageBranchID=@LanguageBranchID
 105:      ORDER BY L.fkPageID
 106:      
 107:      /* Get the property definitions*/
 108:      SELECT
 109:          fkPageTypeID as PageTypeID,--fkPageID as PageID,
 110:          tblPageDefinition.Name AS PropertyName,
 111:          tblPageDefinition.LanguageSpecific,
 112:          COALESCE(PDT.Property,tblPageDefinition.Property) AS PropertyType,
 113:          CONVERT(INT, Required) AS Required,
 114:          tblPageDefinition.pkID AS fkPageDefinitionID,
 115:          tblPageDefinition.Advanced AS OwnerTab,
 116:          TypeName,
 117:          AssemblyName,
 118:          LongStringSettings,
 119:          FieldOrder,
 120:          NULL AS Guid,
 121:          DisplayEditUI
 122:      FROM tblPageDefinition INNER JOIN tblPageType on tblPageDefinition.fkPageTypeID = tblPageType.pkID
 123:          LEFT JOIN tblPageDefinitionType AS PDT ON PDT.pkID=tblPageDefinition.fkPageDefinitionTypeID
 124:      WHERE tblPageDefinition.fkPageTypeID in (SELECT DISTINCT fkPageTypeID FROM tblPage WHERE pkID in (SELECT LocalPageID FROM @Pages))
 125:      ORDER BY FieldOrder,tblPageDefinition.pkID
 126:      
 127:      /* Get the property data for the requested language */
 128:      SELECT
 129:          fkPageID as PageID,
 130:          tblPageDefinition.Name AS PropertyName,
 131:          CONVERT(INT, Boolean) AS Boolean,
 132:          Number AS IntNumber,
 133:          FloatNumber,
 134:          PageType,
 135:          PageLink AS PageLinkID,
 136:          Date AS DateValue,
 137:          String,
 138:          LongString,
 139:          LongStringSettings,
 140:          tblProperty.fkLanguageBranchID AS LanguageBranchID
 141:      FROM tblProperty
 142:      INNER JOIN tblPageDefinition ON tblPageDefinition.pkID = tblProperty.fkPageDefinitionID
 143:      WHERE tblProperty.fkPageID in (SELECT LocalPageID FROM @Pages) AND NOT tblPageDefinition.fkPageTypeID IS NULL
 144:          AND tblProperty.fkLanguageBranchID = @LanguageBranchID
 145:      ORDER BY tblProperty.fkLanguageBranchID,FieldOrder,tblProperty.fkPageDefinitionID
 146:      /*Get category information*/
 147:      SELECT fkPageID AS PageID,fkCategoryID,CategoryType
 148:      FROM tblCategoryPage
 149:      WHERE fkPageID in (SELECT LocalPageID FROM @Pages) AND CategoryType=0
 150:      ORDER BY fkPageID, fkCategoryID
 151:      /* Get access information */
 152:      SELECT
 153:          fkPageID AS PageID,
 154:          Name,
 155:          IsRole,
 156:          AccessMask
 157:      FROM
 158:          tblAccess
 159:      WHERE 
 160:          fkPageID in (SELECT LocalPageID FROM @Pages)
 161:      ORDER BY
 162:          fkPageID,
 163:          IsRole DESC,
 164:          Name
 165:   
 166:      
 167:      /* Get all languages for the page */
 168:      SELECT fkPageID as PageID, fkLanguageBranchID as PageLanguageBranchID FROM tblPageLanguage
 169:          WHERE tblPageLanguage.fkPageID in (SELECT LocalPageID FROM @Pages)
 170:          ORDER BY fkPageID
 171:  END

 

Once the above stored procedure was returning the results I wanted I built some extension methods for FindPagesWithCriteria, GetChildren and GetDescendents.

If a language branch has been specified these methods will use new code that will populate a PageDataCollection in batches, otherwise the standard DataFactory methods will be used.  The code I have knocked up for this is below:


Download DataFactoryPageDataRetrievalExtensions.txt
   1:  using System;
   2:  using System.Collections.Generic;
   3:  using System.Data;
   4:  using System.IO;
   5:  using System.Linq;
   6:  using System.Reflection;
   7:  using System.Web;
   8:  using EPiServer;
   9:  using EPiServer.Configuration;
  10:  using EPiServer.Core;
  11:  using EPiServer.DataAbstraction;
  12:  using EPiServer.DataAccess;
  13:  using EPiServer.Filters;
  14:  using EPiServer.Security;
  15:   
  16:  namespace ElencySolutions.EPiServer
  17:  {
  18:      /// <summary>
  19:      /// Extensions for the DataFactory class which provides batched PageData queries
  20:      /// </summary>
  21:      public static class DataFactoryPageDataRetrievalExtensions
  22:      {
  23:   
  24:          #region Members and constants
  25:   
  26:          private static MethodInfo _loadPageMetaDataMethod;
  27:          private static MethodInfo _loadPageLangMetaDataMethod;
  28:          private static MethodInfo _loadUserPropertyMethod;
  29:          private static MethodInfo _readPropertyDataMethod;
  30:          private static MethodInfo _loadCategoryMethod;
  31:          private static MethodInfo _addPageToCacheMethod;
  32:          private static MethodInfo _fastFindPagesWithCriteriaMethod;
  33:          private static MethodInfo _getChildrenReferencesMethod;
  34:   
  35:          private const int BATCH_SIZE = 100;
  36:   
  37:          #endregion Members and constants
  38:   
  39:          #region Public extension methods
  40:   
  41:          /// <summary>
  42:          /// Finds all pages with criteria batched.
  43:          /// </summary>
  44:          /// <param name="dataFactory">The data factory.</param>
  45:          /// <param name="pageLink">The page link.</param>
  46:          /// <param name="criterias">The criterias.</param>
  47:          /// <param name="languageBranch">The language branch.</param>
  48:          /// <param name="selector">The selector.</param>
  49:          /// <returns></returns>
  50:          public static PageDataCollection FindPagesWithCriteriaBatched(this DataFactory dataFactory, PageReference pageLink, PropertyCriteriaCollection criterias, string languageBranch)
  51:          {
  52:              PageDataCollection pages;
  53:   
  54:              if (!UseBatchedGetPageCalls(languageBranch))
  55:                  pages = DataFactory.Instance.FindPagesWithCriteria(pageLink, criterias, languageBranch);
  56:              else
  57:              {
  58:                  IEnumerable<int> pageIds = FastFindPagesWithCriteria(pageLink, criterias, languageBranch);
  59:                  pages = GetPages(pageIds, languageBranch);
  60:                  new FilterAccess().Filter(pages);
  61:              }
  62:   
  63:              return pages;
  64:          }
  65:   
  66:          /// <summary>
  67:          /// Gets the children batched
  68:          /// </summary>
  69:          /// <param name="dataFactory">The data factory.</param>
  70:          /// <param name="pageLink">The page link.</param>
  71:          /// <param name="selector">The selector.</param>
  72:          /// <returns></returns>
  73:          public static PageDataCollection GetChildrenBatched(this DataFactory dataFactory, PageReference pageLink, ILanguageSelector selector)
  74:          {
  75:              LanguageSelectorContext args = new LanguageSelectorContext(pageLink);
  76:              selector.LoadLanguage(args);
  77:   
  78:              if (!UseBatchedGetPageCalls(args.SelectedLanguage))
  79:                  return DataFactory.Instance.GetChildren(pageLink, selector);
  80:   
  81:              IEnumerable<PageReference> pageReferences =  GetChildrenReferences(DataFactory.Instance.ProviderMap.GetDefaultPageProvider(), pageLink, args.SelectedLanguage);
  82:              return GetPages(pageReferences.Select(current => current.ID), args.SelectedLanguage);
  83:          }
  84:   
  85:          /// <summary>
  86:          /// Gets the descendents batched.
  87:          /// </summary>
  88:          /// <param name="dataFactory">The data factory.</param>
  89:          /// <param name="pageReferences">The page references.</param>
  90:          /// <param name="languageBranch">The language branch.</param>
  91:          /// <returns></returns>
  92:          public static PageDataCollection GetDescendentsBatched(this DataFactory dataFactory, IList<PageReference> pageReferences, string languageBranch)
  93:          {
  94:              if (string.IsNullOrEmpty(languageBranch))
  95:                  throw new InvalidDataException("You have not specified a language branch");
  96:   
  97:              return GetPages(pageReferences.Select(current => current.ID), languageBranch);
  98:          }
  99:   
 100:          #endregion Public extension methods
 101:   
 102:          #region Private methods
 103:   
 104:          /// <summary>
 105:          /// Gets whether alternative versions of GetChildren, GetDescendents or FindPagesWithCriteria can be used
 106:          /// </summary>
 107:          /// <param name="languageBranch">Language branch</param>
 108:          /// <returns>True if alternative versions can be used, otherwise false</returns>
 109:          private static bool UseBatchedGetPageCalls(string languageBranch)
 110:          {
 111:              return !Settings.Instance.UIShowGlobalizationUserInterface || !string.IsNullOrEmpty(languageBranch);
 112:          }
 113:   
 114:          /// <summary>
 115:          /// Fasts the find pages with criteria.
 116:          /// </summary>
 117:          /// <param name="pageLink">The page link.</param>
 118:          /// <param name="criterias">The criterias.</param>
 119:          /// <param name="languageBranch">The language branch.</param>
 120:          /// <returns></returns>
 121:          private static IEnumerable<int> FastFindPagesWithCriteria(PageReference pageLink, PropertyCriteriaCollection criterias, string languageBranch)
 122:          {
 123:              PropertySearchDB propertySearchDb = new PropertySearchDB();
 124:   
 125:              if (_fastFindPagesWithCriteriaMethod == null)
 126:                  _fastFindPagesWithCriteriaMethod = propertySearchDb.GetType().GetMethod("FastFindPagesWithCriteria", BindingFlags.Instance | BindingFlags.NonPublic);
 127:   
 128:              object[] args = new object[] { pageLink.ID, criterias, languageBranch };
 129:              return _fastFindPagesWithCriteriaMethod.Invoke(propertySearchDb, args) as HashSet<int>;
 130:          }
 131:   
 132:          /// <summary>
 133:          /// Gets the children references.
 134:          /// </summary>
 135:          /// <param name="pageProviderBase">The page provider base.</param>
 136:          /// <param name="pageLink">The page link.</param>
 137:          /// <param name="languageBranch">The language branch.</param>
 138:          /// <returns></returns>
 139:          private static IEnumerable<PageReference> GetChildrenReferences(PageProviderBase pageProviderBase, PageReference pageLink, string languageBranch)
 140:          {
 141:              if (_getChildrenReferencesMethod == null)
 142:              {
 143:                  Type[] types = new[] { typeof(PageReference), typeof(string) };
 144:                  _getChildrenReferencesMethod = pageProviderBase.GetType().GetMethod("GetChildrenReferences", BindingFlags.Instance | BindingFlags.NonPublic, null, types, null);
 145:              }
 146:   
 147:              return _getChildrenReferencesMethod.Invoke(pageProviderBase, new object[] { pageLink, languageBranch }) as PageReferenceCollection;
 148:          }
 149:   
 150:          /// <summary>
 151:          /// Loads page meta data
 152:          /// </summary>
 153:          /// <param name="pageLoadDb">PageLoadDB</param>
 154:          /// <param name="propertyDataCollection">PropertyDataCollection</param>
 155:          /// <param name="reader">IDataReader</param>
 156:          private static void LoadPageMetaData(PageLoadDB pageLoadDb, PropertyDataCollection propertyDataCollection, IDataReader reader)
 157:          {
 158:              if (_loadPageMetaDataMethod == null)
 159:                  _loadPageMetaDataMethod = pageLoadDb.GetType().GetMethod("LoadPageMetaData", BindingFlags.Instance | BindingFlags.NonPublic);
 160:   
 161:              _loadPageMetaDataMethod.Invoke(pageLoadDb, new object[] { propertyDataCollection, reader });
 162:          }
 163:   
 164:          /// <summary>
 165:          /// Loads page language meta data
 166:          /// </summary>
 167:          /// <param name="pageLoadDb">PageLoadDB</param>
 168:          /// <param name="propertyDataCollection">PropertyDataCollection</param>
 169:          /// <param name="reader">IDataReader</param>
 170:          private static void LoadPageLangMetaData(PageLoadDB pageLoadDb, PropertyDataCollection propertyDataCollection, IDataReader reader)
 171:          {
 172:              if (_loadPageLangMetaDataMethod == null)
 173:                  _loadPageLangMetaDataMethod = pageLoadDb.GetType().GetMethod("LoadPageLangMetaData", BindingFlags.Instance | BindingFlags.NonPublic);
 174:   
 175:              _loadPageLangMetaDataMethod.Invoke(pageLoadDb, new object[] { propertyDataCollection, reader });
 176:          }
 177:   
 178:          /// <summary>
 179:          /// Loads user properties
 180:          /// </summary>
 181:          /// <param name="pageLoadDb">PageLoadDB</param>
 182:          /// <param name="propertyDataCollection">PropertyDataCollection</param>
 183:          /// <param name="reader">IDataReader</param>
 184:          /// <param name="readData">True if data should be reader, otherwise false</param>
 185:          private static void LoadUserProperty(PageLoadDB pageLoadDb, PropertyDataCollection propertyDataCollection, IDataReader reader, bool readData)
 186:          {
 187:              if (_loadUserPropertyMethod == null)
 188:                  _loadUserPropertyMethod = pageLoadDb.GetType().GetMethod("LoadUserProperty", BindingFlags.Instance | BindingFlags.NonPublic);
 189:   
 190:              _loadUserPropertyMethod.Invoke(pageLoadDb, new object[] { propertyDataCollection, reader, readData });
 191:          }
 192:   
 193:          /// <summary>
 194:          /// Reads property data
 195:          /// </summary>
 196:          /// <param name="pageLoadDb">PageLoadDB</param>
 197:          /// <param name="propertyData">PropertyData</param>
 198:          /// <param name="reader">IDataReader</param>
 199:          private static void ReadPropertyData(PageLoadDB pageLoadDb, PropertyData propertyData, IDataReader reader)
 200:          {
 201:              if (_readPropertyDataMethod == null)
 202:                  _readPropertyDataMethod = pageLoadDb.GetType().GetMethod("ReadPropertyData", BindingFlags.Instance | BindingFlags.NonPublic);
 203:   
 204:              _readPropertyDataMethod.Invoke(pageLoadDb, new object[] { propertyData, reader });
 205:          }
 206:   
 207:          /// <summary>
 208:          /// Loads a category
 209:          /// </summary>
 210:          /// <param name="pageLoadDb">PageLoadDB</param>
 211:          /// <param name="pageData">PageData</param>
 212:          /// <param name="reader">IDataReader</param>
 213:          private static void LoadCategory(PageLoadDB pageLoadDb, PageData pageData, IDataReader reader)
 214:          {
 215:              if (_loadCategoryMethod == null)
 216:                  _loadCategoryMethod = pageLoadDb.GetType().GetMethod("LoadCategory", BindingFlags.Instance | BindingFlags.NonPublic);
 217:   
 218:              _loadCategoryMethod.Invoke(pageLoadDb, new object[] { pageData, reader });
 219:          }
 220:   
 221:          /// <summary>
 222:          /// Adds a page to cache
 223:          /// </summary>
 224:          /// <param name="pageProviderBase">PageProviderBase</param>
 225:          /// <param name="pageData">PageData</param>
 226:          private static void AddPageToCache(PageProviderBase pageProviderBase, PageData pageData)
 227:          {
 228:              if (_addPageToCacheMethod == null)
 229:                  _addPageToCacheMethod = pageProviderBase.GetType().GetMethod("AddPageToCache", BindingFlags.Instance | BindingFlags.NonPublic);
 230:   
 231:              _addPageToCacheMethod.Invoke(pageProviderBase, new object[] { pageData });
 232:          }
 233:   
 234:          /// <summary>
 235:          /// Loads an access control entry
 236:          /// </summary>
 237:          /// <param name="reader">IDataReader</param>
 238:          /// <param name="page">PageData</param>
 239:          private static void AclLoader(IDataReader reader, PageData page)
 240:          {
 241:              page.ACL.Add(new AccessControlEntry((string)reader["Name"], (AccessLevel)Convert.ToInt32(reader["AccessMask"]), (SecurityEntityType)Convert.ToInt32(reader["IsRole"])));
 242:          }
 243:   
 244:          /// <summary>
 245:          /// Populates a page data collection
 246:          /// </summary>
 247:          /// <param name="cmd">AbstractCommand</param>
 248:          /// <returns>PageDataCollection</returns>
 249:          private static PageDataCollection PopulatePageDataCollection(AbstractCommand cmd)
 250:          {
 251:              // Based on PageLoadDB.LoadPageInternal but modified to work with multiple pages
 252:              PageProviderBase pageProviderBase = DataFactory.Instance.ProviderMap.GetDefaultPageProvider();
 253:              Dictionary<int, PageData> dictionary = new Dictionary<int, PageData>();
 254:              PageDataCollection datas = new PageDataCollection();
 255:              IDataReader reader = cmd.ExecuteReader();
 256:              PageLoadDB pageLoadDb = new PageLoadDB();
 257:   
 258:              while (reader.Read())
 259:              {
 260:                  PageData page = new PageData();
 261:                  LoadPageMetaData(pageLoadDb, page.Property, reader);
 262:                  page.ACL.Creator = reader["CreatorName"].ToString();
 263:                  ((PageAccessControlList)page.ACL).PageLink = new PageReference(Convert.ToInt32(reader["PageLinkID"]));
 264:                  dictionary.Add(Convert.ToInt32(reader["PageLinkID"]), page);
 265:              }
 266:   
 267:              reader.NextResult();
 268:   
 269:              while (reader.Read())
 270:              {
 271:                  int pageId = Convert.ToInt32(reader["PageID"]);
 272:                  PageData page = dictionary[pageId];
 273:                  LoadPageLangMetaData(pageLoadDb, page.Property, reader);
 274:              }
 275:   
 276:              reader.NextResult();
 277:   
 278:              while (reader.Read())
 279:              {
 280:                  int pageTypeId = Convert.ToInt32(reader["PageTypeID"]);
 281:                  List<PageData> pages = dictionary.Keys.Select(key => dictionary[key]).Where(currentPage => currentPage.PageTypeID == pageTypeId).ToList();
 282:   
 283:                  for (int i = 0; i < pages.Count; i++)
 284:                      LoadUserProperty(pageLoadDb, pages[i].Property, reader, false);
 285:              }
 286:   
 287:              reader.NextResult();
 288:              while (reader.Read())
 289:              {
 290:                  int pageId = Convert.ToInt32(reader["PageID"]);
 291:                  PageData page = dictionary[pageId];
 292:   
 293:                  PropertyData prop = page.Property[(string)reader["PropertyName"]];
 294:                  if (prop != null)
 295:                      ReadPropertyData(pageLoadDb, prop, reader);
 296:              }
 297:   
 298:              reader.NextResult();
 299:              while (reader.Read())
 300:              {
 301:                  int pageId = Convert.ToInt32(reader["PageID"]);
 302:                  PageData page = dictionary[pageId];
 303:   
 304:                  LoadCategory(pageLoadDb, page, reader);
 305:              }
 306:   
 307:              reader.NextResult();
 308:              while (reader.Read())
 309:              {
 310:                  int pageId = Convert.ToInt32(reader["PageID"]);
 311:                  PageData page = dictionary[pageId];
 312:                  AclLoader(reader, page);
 313:              }
 314:   
 315:              reader.NextResult();
 316:              Dictionary<int, List<string>> languageDictionary = new Dictionary<int, List<string>>();
 317:              while (reader.Read())
 318:              {
 319:                  int pageId = Convert.ToInt32(reader["PageID"]);
 320:   
 321:                  if (!languageDictionary.ContainsKey(pageId))
 322:                      languageDictionary.Add(pageId, new List<string>());
 323:   
 324:                  languageDictionary[pageId].Add(LanguageBranch.Load(Convert.ToInt32(reader["PageLanguageBranchID"])).LanguageID);
 325:              }
 326:   
 327:              foreach (int pageId in dictionary.Keys)
 328:              {
 329:                  PageData page = dictionary[pageId];
 330:                  page.InitializeData(languageDictionary[pageId]);
 331:                  page.MakeReadOnly();
 332:                  datas.Add(page);
 333:                  AddPageToCache(pageProviderBase, page);
 334:              }
 335:   
 336:              return datas;
 337:          }
 338:   
 339:          /// <summary>
 340:          /// Gets the language branch id.
 341:          /// </summary>
 342:          /// <param name="languageBranch">The language branch.</param>
 343:          /// <returns></returns>
 344:          private static int GetLanguageBranchId(string languageBranch)
 345:          {
 346:              int languageBranchId = -1;
 347:   
 348:              string actualBranch = languageBranch;
 349:   
 350:              if (string.IsNullOrEmpty(actualBranch))
 351:                  actualBranch = DataFactory.Instance.GetPage(PageReference.StartPage).MasterLanguageBranch;
 352:   
 353:              if (!string.IsNullOrEmpty(actualBranch))
 354:              {
 355:                  LanguageBranch branch = LanguageBranch.ListEnabled().Where(current => current.LanguageID == actualBranch).FirstOrDefault();
 356:   
 357:                  if (branch != null)
 358:                      languageBranchId = branch.ID;
 359:              }
 360:   
 361:              return languageBranchId;
 362:          }
 363:   
 364:          /// <summary>
 365:          /// Gets the page ids not in cache.
 366:          /// </summary>
 367:          /// <param name="pageIds">The page ids.</param>
 368:          /// <param name="languageBranch">The language branch.</param>
 369:          /// <returns></returns>
 370:          private static List<int> GetPageIdsNotInCache(IEnumerable<int> pageIds, string languageBranch)
 371:          {
 372:              return (from id in pageIds
 373:                      let pageReference = new PageReference(id)
 374:                      let cacheKey = DataFactoryCache.PageLanguageCacheKey(pageReference, languageBranch)
 375:                      where HttpContext.Current.Cache[cacheKey] == null
 376:                      select id).ToList();
 377:          }
 378:   
 379:          /// <summary>
 380:          /// Gets pages matching the supplied pageIds and language branch
 381:          /// </summary>
 382:          /// <param name="pageIds">Page Ids</param>
 383:          /// <param name="languageBranch">Language branch</param>
 384:          /// <returns></returns>
 385:          private static PageDataCollection GetPages(IEnumerable<int> pageIds, string languageBranch)
 386:          {
 387:              PageDataCollection pages = new PageDataCollection();
 388:              PageProviderBase pageProvider = DataFactory.Instance.ProviderMap.GetDefaultPageProvider();
 389:              List<int> pageIdsNotInCache = GetPageIdsNotInCache(pageIds, languageBranch);
 390:   
 391:              int languageBranchId = GetLanguageBranchId(languageBranch);
 392:   
 393:              if (pageIdsNotInCache.Count > 0)
 394:              {
 395:                  PageReferenceCollection pageReferences = new PageReferenceCollection();
 396:                  pageReferences.AddRange(pageIdsNotInCache.Select(pageId => new PageReference(pageId)));
 397:   
 398:                  double numberOfBatches = Math.Ceiling(pageIdsNotInCache.Count / (double)BATCH_SIZE);
 399:   
 400:                  for (int i = 1; i <= numberOfBatches; i++)
 401:                  {
 402:                      int startIndex = (i * BATCH_SIZE) - BATCH_SIZE;
 403:                      int endIndex = startIndex + BATCH_SIZE;
 404:   
 405:                      if (endIndex > (pageIdsNotInCache.Count - 1))
 406:                          endIndex = (pageIdsNotInCache.Count - 1);
 407:   
 408:                      List<int> batch = pageIdsNotInCache.Skip(startIndex).Take(endIndex - startIndex).ToList();
 409:   
 410:                      PageListDB pageListDb = new PageListDB();
 411:                      pageListDb.Execute(delegate
 412:                      {
 413:                          //new PageDataCollection(batch.Count);
 414:                          Type[] types = new[] { typeof(string) };
 415:                          MethodInfo createCommandMethod = pageListDb.GetType().GetMethod("CreateCommand", BindingFlags.Instance | BindingFlags.NonPublic, null, types, null);
 416:                          object[] args = new object[] { "usp_netPageDataLoadBatched" };
 417:                          AbstractCommand cmd = createCommandMethod.Invoke(pageListDb, args) as AbstractCommand;
 418:                          byte[] buffer = new byte[4 * batch.Count];
 419:                          new MemoryStream(buffer, true);
 420:                          int num = 0;
 421:                          foreach (int num2 in batch)
 422:                          {
 423:                              buffer[num++] = (byte)((num2 >> 0x18) & 0xff);
 424:                              buffer[num++] = (byte)((num2 >> 0x10) & 0xff);
 425:                              buffer[num++] = (byte)((num2 >> 8) & 0xff);
 426:                              buffer[num++] = (byte)(num2 & 0xff);
 427:                          }
 428:                          types = new[] { typeof(string), typeof(DbType), typeof(ParameterDirection), typeof(object) };
 429:                          MethodInfo createParameterMethod = pageListDb.GetType().GetMethod("CreateParameter", BindingFlags.Instance | BindingFlags.NonPublic, null, types, null);
 430:                          args = new object[] { "Binary", DbType.Binary, ParameterDirection.Input, buffer };
 431:                          IDbDataParameter parameter = createParameterMethod.Invoke(pageListDb, args) as IDbDataParameter;
 432:                          cmd.Parameters.Add(parameter);
 433:   
 434:                          args = new object[] { "LanguageBranchID", DbType.Int32, ParameterDirection.Input, languageBranchId };
 435:                          parameter = createParameterMethod.Invoke(pageListDb, args) as IDbDataParameter;
 436:                          cmd.Parameters.Add(parameter);
 437:   
 438:                          return PopulatePageDataCollection(cmd);
 439:                      });
 440:   
 441:                  }
 442:   
 443:              }
 444:   
 445:              foreach (PageReference pageReference in pageIds.Select(pageId => new PageReference(pageId)))
 446:              {
 447:                  string cacheKey = DataFactoryCache.PageLanguageCacheKey(pageReference, languageBranch);
 448:                  PageData page = HttpContext.Current.Cache[cacheKey] as PageData ??
 449:                                  DataFactory.Instance.GetPage(pageReference, new LanguageSelector(languageBranch));
 450:   
 451:                  if (page != null)
 452:                      pages.Add(page);
 453:              }
 454:   
 455:              return pages;
 456:          }
 457:   
 458:          #endregion Private methods
 459:   
 460:      }
 461:  }

 

Performance Tests

I have performed some tests to highlight the performance gains that can be made when batching PageData population.  All tests were performed locally with a local database instance. 

The web application cache was cleared between each test and the code was compiled in release build to make use of any runtime optimisations.

Below are the test results:

Test 1 - FindPagesWithCriteria vs FindPagesWithCriteriaBatched

Cache cleared between each test, the number of pages being returned by the method call was 1469.

Test run

Old version

New version

1

2654

1314

2

2179

1239

3

2170

1221

4

2010

1273

5

1194

1295

6

2749

1338

7

2012

1277

8

1995

1280

9

2051

1219

10

2286

1235

Average

2210

1270

 

Average Difference in milliseconds : 940

Average Percentage Increase : 42.53%

 

Test 2 – GetChildren vs GetChildrenBatched

Cache cleared between each test, the number of pages being returned by the method call was 56.

Test run

Old version

New version

1

93

41

2

112

40

3

94

40

4

111

41

5

83

34

6

111

38

7

112

41

8

98

29

9

99

41

10

99

45

Average

102

39

 

Average Difference in milliseconds : 63

Average Percentage Increase : 61.76%

 

Test 3 – GetDescendentsBatched

Cache cleared between each test, the number of pages being returned by the method call was 21,959.  The old version calls GetDescendants which returns IList<PageReference>.  A foreach loop is then used to call GetPage with each PageReference.

Test run

Old version

New version

1

24462

10310

2

24152

12312

3

24259

10341

4

24914

10307

5

24615

9726

6

24472

10076

7

23808

10210

8

23762

10150

9

23840

10075

10

23986

12391

Average

24227

10590

 

Average Difference in milliseconds : 13637

Average Percentage Increase : 56.28%

Conclusion

I wanted to see if calls to FindPagesWithCriteria and GetChildren could be speeded up and the code I have provided suggests that they definitely could be.

The code I have provided is not production ready and is of a prototype standard.  I am sure it will not cater correctly for language fall-backs etc.

I suppose the next steps are for the EPiServer Product Developers to look into this and possibly enhance the LocalPageProvider class in vNext to use batch calls in situations where it makes sense to? 

What do you think EPiServer Product Developers ?

 

Disclaimer

The stored procedure and code provided above is not production ready and has only been tested against a single language branch.  I have performed no testing in a multi-lingual site although I believe the extension methods will use the normal DataFactory.Instance methods if no language branch has been supplied.

If you decide the use the code as is and you suffer a catostrophic failure of some kind then I accept no responsibility Winking smile

Dec 14, 2010

Joel Abrahamsson
(By Joel Abrahamsson, 12/14/2010 3:35:15 PM)

Interesting! Looking forward to see thoughts about this.

Magnus Rahl
(By Magnus Rahl, 12/14/2010 7:24:50 PM)

Kudos for exploring this! If there are no side-effects to such an approach I think it is a good thing to get those percents of performance increase. However, consider that most of the time many pages would probably be returned from cache, with diminishing performance gain as a result. Also, if they are NOT returned from cache, this means your query is pulling a lot of pages that are not often requested. If a query returns thousands of pages this effectively means you are poisoning the cache with many pages that will probably go unused. In this scenario I think it is more important to optimize the query to return fewer pages (I'm assuming here that not all the pages are used in any processing, but that they are post-filtered). Just a thought, and of course it doesn't invalidate your results! Good job!

lee.crowe
(By lee.crowe, 12/14/2010 9:03:50 PM)

The tests were just examples to highlight potential gains.

Normally as you suggested you would optimze the query and pages may well be in cache :)

Anders Hattestad
(By Anders Hattestad, 12/15/2010 2:12:38 PM)

Have done something simular once. But we returened LightPageData objects instead. These objcects did only containen a small amount of the full PageData objects, but we used it to only display items in lists so it was enough for us.

What you have done is very usefull. I wonder why there is not implementet in the core. There are times you need to fetch many pages as once, and I dont understand why its not inside the core.

A

jb
(By jb, 12/16/2010 10:55:27 AM)

I think the result are interesting, especially the time difference you have seen in GetChildren. The reason I find GetChildren most interesting is that we actually have bascially the same approach there as you have, namely first check which pages that are already in cache and for those not in cache we get them in one batch.
When I have time I will compile your code and do some profiling comparsion with our code. My guess though is that the overhead seen in our code is due to language handling. That is we use the languageselector for each fetched children to get the correct language for each child due to the configrued language fallback rules etc.

I think your suggestion regarding FindPagesWithCriteria is sane. That is we should use the same approach there as we do in GetChildren which is first check which pages that are present in cache and for those not in cache get them in one batch. I have reported a bug for that and I think and hope we will get that into the CMS6R2 release.

Thanks for your valuable feedback!

lee.crowe
(By lee.crowe, 12/16/2010 11:45:08 AM)

Hi Johan

This is great! I think you are probably right that there is additional overhead in the language selectors. My thinking was if the site is not multi lingual or a specific language is specified then language selection may not need to come into play.

My thinking was also that if database calls were batched then there may be potential peformance gained from this.

Kind Regards

Lee

lee.crowe
(By lee.crowe, 12/17/2010 11:34:19 AM)

Hi Johan

Foolishly the original performance tests I have displayed above are against EPiServer CMS 5 R2. I probably should have mentioned that in the blog post :(

I have ported the stored procedure for EPiServer 6 and have performed some similar tests and can see in EPiServer CMS 6 GetChildren seems much more performant than in 5 R2.

FindPagesWithCriteria can definately still be speeded up by using a similar GetScatterPages (batched) approach.

Also, I think it will be useful rather than just having a GetDescendents method that returns an IList, to have a new method like the extension one I created that would batch the retrieval of the pages using a GetScatteredPages (batched) approach.

If you would like to check the updated stored procedure and my testing code please download the following files.

- http://www.elencysolutions.co.uk/EPiServer/usp_netPageDataLoadBatched_EPiServer6.sql.txt
- http://www.elencysolutions.co.uk/EPiServer/Global.asax.cs.txt

On another note, I added a feature request a couple of weeks ago for a new bool PageExists(PageLink pageLink) method on the DataFactory object. This method would return true/false depending on whether a page exists in the database. I have always found it very odd that this functionality doesn't exist and you have to call GetPage and catch a PageNotFoundException to check for the existence of a page.

Many thanks for your time!

Lee

Please login to comment.