0

I have a list with about 100K items. There could be gaps in the beginning and in between for SharePoint generated Ids of these items (due to deletions). List items data is live and keep changing. My requirement is to go through all items to get value of a field (indexed) for each of the items. I need to do this few times a day.

I am using SSOM and SPList.GetItems() is not an option due to threshold and performance concerns. I can do batching with Id range using CAML but gaps in Ids could mean that in some batches no or very few items are fetched. Also, I should somehow get the max and min Id first for the batch processing.

Please let me know if there is any standard and efficient way of doing this.

  • Move it out to SharePoint List and to a SQL Database instead – Billy Aug 27 at 15:40
0

If you need to get a single field value from all items, you can use this method. It avoids the threshold issue by using pagination.

public static IEnumerable<SPListItem> GetAllListItems(SPList list, SPQuery externalQuery, bool viewFieldsOnly, params string[] viewFields)
{
    List<SPListItem> listItems = new List<SPListItem>();

    int itemCount = 500;
    Queries queries = new Queries();
    queries.SetViewFields(viewFields);
    SPQuery query = queries.SpQuery;
    query.Query = externalQuery.Query;

    GetAllItems(list, itemCount, query, null, listItems, viewFieldsOnly);
    return listItems;
}

private static void GetAllItems(SPList list, int itemCount, SPQuery query, string lastItemIdOnPage, List<SPListItem> listItems, bool viewFieldsOnly)
{
    while (true)
    {
        query.ViewFieldsOnly = viewFieldsOnly;
        query.RowLimit = (uint)itemCount;
        if (lastItemIdOnPage != null)
        {
            SPListItemCollectionPosition pos = new SPListItemCollectionPosition(lastItemIdOnPage);
            query.ListItemCollectionPosition = pos;
        }

        SPListItemCollection spListItems = list.GetItems(query);
        if (spListItems == null)
        {
            itemCount = 0;
        }
        else
        {
            if (spListItems.ListItemCollectionPosition == null)
            {
                listItems.AddRange(spListItems.Cast<SPListItem>());
                break;
            }

            lastItemIdOnPage = spListItems.ListItemCollectionPosition.PagingInfo;
            itemCount = spListItems.Count;
            listItems.AddRange(spListItems.Cast<SPListItem>());
        }
    }
}

Usage

var zillionItems = GetAllListItems(<Your List>, new string[1] {"FIELD_NAME"}; )
  • Thanks for this, I will try it out. Question: In first iteration, query.ListItemCollectionPosition is not set which means it runs as a normal CAML query with row limit. Won't it cause threshold issue? Otherwise, we can always avoid threshold by giving row limit. Also, standard list view in SP UI is always having pagination, so why does it show threshold error at all? – Prakash Aug 28 at 8:37
  • You can create a separate question about threshold, pagination and a row limit. It's a complicated topic and comments only allow me to type 500 characters. to answer your questions here: 1) It won't cause threshold issue. this code runs against 1M records with no issues 2) row limit does not always work around theshold. You can get a threshhold error with a row limit of one (1). 2) I am not sure what you are asking about SP UI and the threshhold error. You are wondering why SPList.GetItems() does not have built-in pagination? – Denis Molodtsov Aug 28 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.