none
How to retrieve items from document libraries using client context without performance lag?

    Question

  • I need to retrieve items/list items from SharePoint 2010 document library and display it in a list ListView using C#. I have used following CAML query to retrieve list items and its properties.

    camlQuery.ViewXml = "<View><Query><Where><Geq><FieldRef Name='ID'/><Value Type='Number'>" + id.ToString() + "</Value></Geq></Where></Query><RowLimit>100</RowLimit></View>"; 
                            ListItemCollection collListItem = oList.GetItems(camlQuery);
    
                            if (id != 0)
                            {
                                items = context.LoadQuery(collListItem.Include
                     (
                       item => item.File.ListItemAllFields,
                       item => item.FileSystemObjectType,
                       item => item.Id,                     
                       item => item.File,
                       item => item.File.ServerRelativeUrl,
                       item => item.DisplayName
                       ).Where(item => item.Id > id && item.FileSystemObjectType == FileSystemObjectType.File));

    You would have noticed that I have set Row limit to 100 so this works fine, as soon as I increase the limit to about 1000 it slows considerably, taking more than 7 seconds to get query result which is just not acceptable. Is there any other way the I can optimize my query to make it work faster to get about 1000 items in some milliseconds. This is frustrating because reloading items in ListView also adds up to time when loading items using paging.

    Need some Guidance.

    Thanks


    MS

    Tuesday, February 05, 2013 12:43 AM

Answers

  • Hi Mani,

    Yes, this query should perform better because you are requesting fewer fields per item. Also, you are querying a specific folder, and not the entire list as in the first sample, and presumably that folder contains fewer items.

    A couple of suggestions to further improve this:

    1. Depending on your specific requirements, you might be able to request even fewer fields per item. When you are displaying items in a ListView control, this acts pretty much a client-side view of the list, which will typically display only a limited number of fields, like the DisplayName, Author, Created, etc. After that when the user selects a specific item from the ListView, you might retrieve and display more details for that item using a query for a single item by ID.

    2. You may implement paging in the ListView control by using a combination of the Take() and Skip() LINQ methods, and thus get fewer items per query.

    Regards,


    Teodora

    • Marked as answer by Manidollars Thursday, February 07, 2013 5:41 AM
    Wednesday, February 06, 2013 7:04 AM

All replies

  • Try to specify only the fields you wish to return using <ViewFields>. This may help.

    THosE wHo doN'T apPreCiATe LiFe, DOn't DeSerVe iT

    Tuesday, February 05, 2013 9:30 AM
  • Hi manidollars,

    One thing you should do is limit the number of fields you are retrieving for each list item to the fields you really need. In the code above you are getting all the list item fields plus the file information.

    In addition, it seems to me that you are duplicating the filtering logic - first by specifying the ViewXml, and then by using the Where method. Doesn't the ViewXml approach work by itself like in the sample here?

    Regards,


    Teodora

    Tuesday, February 05, 2013 10:46 AM
  • Hi A.Ragab

    I need to get all items associated with an item including default and custom fields. I guess it will be too complicated to create such query. Although it seems as the solution to my other query for searching SharePoint item by their file name.

    Thanks

    Mani


    MS

    Tuesday, February 05, 2013 11:16 PM
  • Hi Teodora,

    Indeed I am retrieving too many fields, but it is a requirement, and also I was aware that I have applied ID filtering twice but that didn't seem to speed up things even if I remove one of those.

    I have got some acceptable result by using following query 

    var oFiles = context.Web.GetFolderByServerRelativeUrl(path).Files;
    context.Load(oFiles,
    files => files.Include(file => file.ListItemAllFields).Where(file => file.ListItemAllFields.FileSystemObjectType == FileSystemObjectType.File).Take(2000));
    context.ExecuteQuery();

    I was able to get around 1200 hits in approximately 2 seconds. I am keen to test it with 4000 items today. 

    The reason I guess, as you suggested lowering the no. of objects requested. In above query I am not initiating ListItem object but instead using FileCollection object.

    Thanks


    MS

    Tuesday, February 05, 2013 11:26 PM
  • Hi Mani,

    Yes, this query should perform better because you are requesting fewer fields per item. Also, you are querying a specific folder, and not the entire list as in the first sample, and presumably that folder contains fewer items.

    A couple of suggestions to further improve this:

    1. Depending on your specific requirements, you might be able to request even fewer fields per item. When you are displaying items in a ListView control, this acts pretty much a client-side view of the list, which will typically display only a limited number of fields, like the DisplayName, Author, Created, etc. After that when the user selects a specific item from the ListView, you might retrieve and display more details for that item using a query for a single item by ID.

    2. You may implement paging in the ListView control by using a combination of the Take() and Skip() LINQ methods, and thus get fewer items per query.

    Regards,


    Teodora

    • Marked as answer by Manidollars Thursday, February 07, 2013 5:41 AM
    Wednesday, February 06, 2013 7:04 AM
  • Hi Teodora,

    At the moment I am trying to show and populate all the fields from default content type of the document library. My bad, I did not posted it but I have always executed the query on single FolderCollection by specifying ServerRelativeUrl. Taking a note on your suggestion and as A.Ragab suggested to specify the field names in <ViewFields> tag in CAML. I came up with following query.

    string viewField = "<ViewFields>";
    foreach (string fieldInternalName in defaultContentTypeFields)
    viewField += @"<FieldRefName= \""" + fieldInternalName.Split('\t')[0] + "\" />";
    
    viewField += @"<FieldRefName=""FileRef"" />";
    viewField += @"<FieldRefName=""File_x0020_Size"" />";
    viewField += @"<FieldRefName=\""" + spDefaultIDFieldInternalName + "\" />";
    viewField += "</ViewFields>";
    
    
    CamlQuery camlQuery = new CamlQuery();
    camlQuery.ViewXml = "<View><Query><Where><Contains><FieldRef Name='FileLeafRef'/><Value Type='Text'>SearchText</Value></Contains></Where><OrderBy><FieldRef Name='Created' Ascending='FALSE'/></OrderBy></Query>+" + viewField + "<RowLimit>2000</RowLimit></View>";
    camlQuery.FolderServerRelativeUrl = Path;
    ListItemCollection collListItem = oList.GetItems(camlQuery);
    context.Load(collListItem);
    context.ExecuteQuery();
    return collListItem;

     The fields like Modified, FileRef and Title does get initialized however all the other fields such as Size, Author, and other SharePoint default fields does not fetches data (Initialize exception). The Other fields are EmailTo, Email Subject, and Created which are default in SharePoint. 

    How I am getting value:

    collListItem[i].FieldValues["Created"].ToString();

    I am using Internal field names in CAML and while extracting. Query does fire and get the no. of hits as it should. 

    Now, Since I am performing text search on the file name, I cannot use Linq because it doe not allow Contains() expression in the query. Is there any other way out or could you tell me where am I going wrong?

    Thanks


    MS

    Wednesday, February 06, 2013 11:00 PM
  •  My bad, the above query has spacing issue and there is an extra "+" character  after </Query> tag. Following is the working query

    foreach (string fieldInternalName in defaultContentTypeFields)
         viewField += "<FieldRef Name='" + fieldInternalName.Split('\t')[0] + "' Type='" + fieldInternalName.Split('\t')[1] + "' Nullable='TRUE' />";
    
    viewField += "<FieldRef Name='FileRef' Nullable='TRUE' />";
    viewField += "<FieldRef Name='File_x0020_Size' Nullable='TRUE' />";
    viewField += "<FieldRef Name='" + spDefaultIDFieldInternalName + "' Nullable='TRUE' />";
    viewField += "</ViewFields>";
    
    camlQuery.ViewXml = "<View><Query><Where><Contains><FieldRef Name='" + spDefaultFileLeafRefFieldInternalName + "'/><Value Type='Text'>" + fileNameKeyWords.ToString() + "</Value></Contains></Where><OrderBy><FieldRef Name='FileLeafRef' Ascending='FALSE'/></OrderBy></Query>" + viewField.ToString() + "<RowLimit>2000</RowLimit></View>";

    Thanks a lot Teodora for your help and suggestions.

    Mani


    MS

    Thursday, February 07, 2013 5:41 AM