WikiWebs – Computer Software Services

We'll fix your SharePoint

Archive for the ‘Uncategorized’ Category

The notorious “List View Threshold” issue

Thursday, August 10th, 2017

SharePoint has been from inception, based on an ISAM file management system.

ISAM is a 1950’s technology that was all but killed off when the Oracle Relational Database was released for the IBM PC in the 1980’s.

It is a misconception that SharePoint uses SQL Server, which is a relational database platform and nothing like ISAM. Unfortunately, that is only half the truth. SharePoint ISAM is implemented on top of SQL Server and apart from the Table space SharePoint does not use many (if any) of the considerable additional features of SQL Server.

The problem with ISAM is that it doesn’t scale well. That’s Ok if you don’t ever expect your application to have more than about – let say – 5000 items in any one file. But beyond that you need to start thinking about optimising your access – particularly if you want to do dynamic filtering and sorting (etc) in a shared server application with hundreds or thousands of concurrent users.

The SharePoint List View Threshold (LVT) issue arises because the ISAM based List View cannot scale except by ditching the ISAM single List and going to a set of Relational Tables. Its unlikely Microsoft will do ever this because SharePoint is by design only meant to contain small data sets (Lists/Libraries) albeit many of them. SharePoint scales horizontally, not vertically.

In view of this, the three best solution scenarios we have at the moment for the LVT issue are :

1. it is possible to work around the LVT issue by using the original All Items list view (or restored to original settings), however even in this view filtering only works on the ID column when LVT is exceeded.

2. The next easiest solution is archiving strategies and would be accomplished by simply copying the current List before it hits LVT (the copy becomes the read only archive). Then you can think about which record categorisation method is the best candidate to use for deleting items from the current List. An intuitive cutoff date usually works best if end state categorisation options (eg status=closed) are not available.

3. If, in spite of archiving, you require direct access to more records than the LVT, then we suggest that a more traditional Database Application is required and that SharePoint is not a good fit for these requirements. As noted above, SharePoint is designed for small independent Lists and does not scale well past the LVT, in spite of Microsoft’s misleading claims to the contrary.

A commonly touted solution is to replace your List Views with one or more search pages with various forms of refiners, filters or managed metadata keywords, but these are not necessarily going to work for you. This is because above 5000 items most List infrastructure features also fail (such as refiners, calculated columns, content types) at undocumented points and unpredictable moments. You are also going to encounter problems importing and exporting large volumes of items in and out of Lists exceeding the LVT. And its completely unpredictable. Microsoft’s guaranteed to work solution is not to exceed the LVT. Which is to say ‘exceed LVT at your own risk’. The term “List View Threshold” is completely false and misleading on Microsoft’s part because the Threshold actually applies to the entire List, not just the View. It is a “List Threshold”.

If you insist on a SharePoint application that exceds LVT, then a relational database solution exists (really a rewrite) and is a choice of either:
• SharePoint Hosted App/Add-in (eg. client/browser side Javascript/JQuery, Access Web Forms)
• Provider Hosted App/Add-in (server side code – eg. Visual Studio .Net)

The latter is simply traditional SharePoint server-side development and is known to be very costly compared to client side browser based solutions. Even the Javascript client-side solution is just as costly, risky and difficult, but may have a shorter development to deployment cycle.

The take home message is this: If SharePoint is to be used for anything other than small data sets (less than 5000 items) then consider the traditional client-server relational database web application paradigm instead. This can be hosted on a SharePoint App/Add-in but all you are getting out of SharePoint will be the consistent GUI and may be some extra connectivity. That’s a lot of licensing cost for not a lot of use.

Because the high development cost is comparable, it makes more sense to build your large data set application using ASP.Net and be done with it.

That way at least you have more chance of finding someone to support it. Server-side development in SharePoint is extremely difficult and finding people who can do it competently is not easy. That is why Wikiwebs is committed to  recommending SharePoint for out of the box and client side development that you do not expect to exceed the List View Threshold. Ever.