Bumping up against the list view threshold in SharePoint 2013

I’ve been learning a lot while working on my current SharePoint project. There’s been a lot of “trial and error” stuff, where I try something, get an error message, Google the message, then spend an hour or two puzzling out what I did wrong.

Today’s issue was related to the default 5000-item list view threshold. I was already aware that it existed, and that I’d need to take certain measures to avoid bumping up against it. The main list for my project is going to have about 20,000 items in it, when it’s in production, so I knew I had to watch out for it.

The list has two fields, company and vendor number, that, combined, form a unique key. In SQL, I would put them together into a single two-field unique index, and that would be sufficient to allow lookups to work well. In SharePoint, it’s a little more complicated. It’s theoretically possible to create a two-column compound index, but I can’t do that on my list, for some reason. (At some point, I’ll have to do some research and figure out why.) So I’ve got two single-column indexes.

One of the things I need to do in my code is pull up a single record from my list, given company and vendor number. I’m using something like the code below to do that. (This is CSOM code.) This code should only ever return a single record, since the combination of the two fields is always unique, and they’re both always specified.

/andyhuey/c5a25484c3fb1efcf1b4a3eaf388795b
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
private bool doesVendorRecExist(string companyCode, string vendorNum, 
    List vendorList, ClientContext cc)
{
    CamlQuery spq = new CamlQuery();
    spq.ViewXml = string.Format(@"
    <View><Query>
    <Where><And>
    <Eq><FieldRef Name='VendorNo' /><Value Type='Text'>{0}</Value></Eq>
    <Eq><FieldRef Name='CompanyName' /><Value Type='Text'>{1}</Value></Eq>
    </And></Where>
    </Query></View>", vendorNum, companyCode);
    ListItemCollection myItems = vendorList.GetItems(spq);
    cc.Load(myItems);
    try
    {
        cc.ExecuteQuery();
    }
    catch (Microsoft.SharePoint.Client.ServerException ex)
    {
        Console.WriteLine("Error in doesVendorRecExist({0},{1}): {2}", companyCode, vendorNum, ex.Message);
        Environment.Exit(-1);
    }
    return (myItems.Count > 0);
}

The original version of this code had the ‘CompanyName’ field first, and the ‘VendorNo’ field second. That version caused a crash with the message “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.” That didn’t make any sense to me, since I was specifying values for both indexed fields, and the result should have always been zero or one records.

Some background first: The distribution of the data in my list is a bit lopsided. There are about a half-dozen company codes, and about 6000 unique vendor numbers. There are more than 5000 vendor numbers in the main company and less than 5000 in the others. In SQL, this wouldn’t matter much. Any query with “where VendorNo=x and CompanyName=y” would work fine, regardless of the ordering of the ‘where’ clause.

In CAML, I’m guessing, the order of fields in the ‘where’ clause DOES matter. My guess is that, with the ‘CompanyName’ field first, SharePoint was first doing a select of all items with ‘CompanyName=x’, which in some cases would return more than 5000 rows. Hence the error. By switching the order, it’s searching on ‘VendorNo’ first, which is never going to return more than a half-dozen items (one for each company, if the vendor exists in all of them). Then, it does a secondary query on the CompanyName which whittles down the result set to 0 or 1 records. I’m not entirely sure if I’ve got this right, but I do know that switching the order of the fields in the CAML fixed things.

So, lesson learned: SharePoint isn’t nearly as smart as SQL Server about query optimization.

Another side-lesson I learned: I initially didn’t have my CAML query specified quite right. (I was missing the “<View><Query>” part.) This did NOT result in an error from SharePoint. Rather, it resulted in the query returning ALL records in the list. (It took a while to figure that out.)

I suspect that I’m going to learn even more lessons about SharePoint’s quirks as I get deeper into the testing phase on this project.

Useful Links:

Leave a Reply