Large Lists and List View Threshold

You would need to know or as we know

         List View Threshold (LVT)

                o   Every web application in SP 2010, has a default configured constant LVT value.

                o   Administrator default LVT is generally set to a higher value compared to end user (default is  

                     20000 for admin / 5000 for end users).

Consideration for Large Lists

              Large List should have:

o   Limited number of columns

o   Limited number of lookup fields (<= 8)

o   Filter criteria should be on Indexed fields (around 2)

o   Folder Structure with number of items less than LVT of web application ( default 5000)

Why not List View

o   List View is not appropriate for list having large number of items

o   The LVT value limits the Views to work for certain Lists, this decision is based on some logical limits (SQL applies database lock on table when more than 5000 items are being touched on a single query) to counter balance the performance degradation of the SharePoint Farm.

o   Conditions, when the List View won’t work

§  When using the Filter on a column and the number of items; having same value in the column is repeated more than the number of threshold value.

·         Regardless of using

o   Folder

o   Index

o   Item Limit

o   And Condition

o   OR Condition

o   For List View to work without or with Filter criteria

§  The number of items should be less than the LVT of the web application

Alternatives for Viewing List Items

o   Search with result shown in the end user expected format

o   Metadata Navigation consideration while designing the List

§  Metadata Navigation applies to doc lib page only

§  Document library design is critical to have useful Metadata Navigation

o   Object Model Custom Code

o   Content Query Web Part

Consideration for Large Lists 

 From a high level, it looks like we can perform activity at three different levels; which can help avoid List View Threshold issue and performance degradation:

            Document Library Level

·         Redesign the document library, split into multiple logical library based on business needs

·         Items storage in the document library has to be modularized / use container

           Page Level

·         Create custom Views for pages to access data
·         Caching via Content Query Web Part if required

      Archival
            ·         Implement Archiving policy for the document library (auto/manual)

·         Document / version cleanup, based on document life cycle

·         Content Organizer can be utilized