none
Performance problems with Lists in SharePoint 2010 (EnsureListItemsData)

    Question

  • Hello,

    I've faced with serious performance problems when I'm trying to get hierarchical structures from the SharePoint 2010 lists (page loads about 60 seconds).
    Following the DashBoard logs, the problem lies in the large number of EnsureListItemsData function calls.
    After experimenting with CAML / Linq / GetItemBy ... queries I've came to the conclusion that each request to SharePoint lists is accompanied by a EnsureListItemsData function call (MSDN and Google didn't give me the answer, what this function actually did).
    The only solution I've found - it's to get all the elements of the hierarchy level in one large query, instead of set of small queries in a loop.
    Is there is any way to avoid of calling EnsureListItemsData function? Or if not, whether there is some optimal solution/best practice for the hierarchy fetching?
    Thank you.

    Sincerely,
    Homyuk Sergey
    Wednesday, December 08, 2010 8:16 AM

Answers

  • Have you tried using Joins instead of the foreach loop? Depending on what your DTO transformations do this may not matter, but in theory you can use Join operators in LINQ to SharePoint and the query will not be executed until you enumerate the collection.Your current code enumerates the collection by doing a foreach, thus sending the CAML query to the server at that moment. 
    http://jcapka.blogspot.com
    • Marked as answer by Wayne Fan Monday, December 13, 2010 9:23 AM
    Wednesday, December 08, 2010 3:31 PM

All replies

  • Can you post a code snippet as to what you are trying to do?  

    I doubt that you will want to avoid calling the EnsureListItemsData function, if the API calls it there is good reason. I think you are on the right path with using one query as opposed to a loop. Loop of queries sounds like a bad idea, but I want to see your code first.


    http://jcapka.blogspot.com
    Wednesday, December 08, 2010 10:14 AM
  • Joe, thanks for helping.

    I'm afraid that code snippet wouldnt help because i'm using repository pattern, entities, lazy loading and linq expressions to encapsulate all queries to the sharepoint lists.

    But if simplify, it will looks like follows:

    I have the following hierarcy: Round -> Tasks -> Attachments (Lists connected with Lookups).

    And I need to transform it into the hierarcy of Data Transfer Objects: RoundDTO -> TaskDTOs -> AttachmentDTOs

    using(var roundsDataContext = new RoundsDataContext())
    {
    	var rounds = from round in roundsDataContext.Rounds where round.ProjectID == View.ProjectID select round;
    	foreach (var round in rounds)
    	{
    		... // some code, that transforms round into DTO
    		using(var taskDataContext = new TaskDataContext())
    		{
    			var tasks = from task in taskDataContext.Tasks where task.RoundID == round.ID select task;			
    			foreach (var task in tasks)
    			{
    				... // some code, that transforms task into DTO
    				using(var attachmentDataContext = new AttachmentDataContext())
    				{
    					var attachments = from attachment in attachmentDataContext.Tasks where attachment.TaskID == task.ID select attachment;							
    					foreach (var attachment in attachments)
    					{
    						... // some code, that transforms attachment into DTO
    					}
    				}
    			}
    		}
    	}
    }
    

    Single query for whole hierarchy, significantly increase performance(page loads just in a few seconds), but it makes my Data Access Layer completely useless.


    Sincerely,
    Homyuk Sergey

    Wednesday, December 08, 2010 11:20 AM
  • Have you tried using Joins instead of the foreach loop? Depending on what your DTO transformations do this may not matter, but in theory you can use Join operators in LINQ to SharePoint and the query will not be executed until you enumerate the collection.Your current code enumerates the collection by doing a foreach, thus sending the CAML query to the server at that moment. 
    http://jcapka.blogspot.com
    • Marked as answer by Wayne Fan Monday, December 13, 2010 9:23 AM
    Wednesday, December 08, 2010 3:31 PM