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
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 
Dec 14, 2010
Interesting! Looking forward to see thoughts about this.
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!
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 :)
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
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!
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
Hi Johan
, to have a new method like the extension one I created that would batch the retrieval of the pages using a GetScatteredPages (batched) approach.
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
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