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

SharePoint Web Service - Elevated Privilege / Anonymous Access / Performance

Considering SharePoint Web Service, watch out for the following:

* SharePoint internet site has anonymous user base. Calling web service from an anonymous internet sp site is not straight forward. Moreover, Read only web service call from anonymous SP pages is feasible.   The following blogs takes a stab on it:     

    http://sympmarc.com/2010/03/18/allowing-anonymous-access-with-sharepoint-web-services-and-spservices/

* Web Service work great when small amount of data across the network is considered. If web service requires intensive operations this in turn puts frequent database updates and cause a significant burden in a production environment. For example, using Web services to bulk load or synchronize a list with a database is risky, depending on the amount of data and the period of synchronization. A more acceptable use of code that accesses a Web service is for a Web Part to access a built-in service to query a set of items in a list. More

on this straight from the core: http://msdn.microsoft.com/en-us/library/bb862916.aspx

* Considering best practices; Microsoft does not recommend heavy use of SharePoint Web Services for updating SharePoint data, however, passive use of SharePoint Web Services to read service data is considered acceptable.

* At times we need to elevate privilege to perform certain action on the SP server via managed code. When working with the SharePoint Web Services you can't elevate the privilege.  More on this: 

http://www.codeproject.com/KB/sharepoint/spwslimits_1.aspx

* Some other notable blog points out:

-          Web Services use XML based SOAP (Simple Object Access Protocol) messages as the communication protocol

-          Reliance on XML can translate into high overhead in the form of oversized, sluggish messages

-          This can mean an investment in XML appliances or other software to optimize validation, parsing, and analysis of XML messages.

-          As Web services operate autonomously, with no centralized control, users may find them unpredictable, necessitating IT to provide service guarantees or service-level agreements.

-          Every request has a marshalling un-marshalling overhead.
-          Every request has to be authenticated using user name and password. It does not support the concept of authenticated session, which can be re-used with a session key. Exception is Excel Service Web Service
-          Issues with Downloading Files  Copy web service i.e. Marshalling/un-marshalling overhead: The SharePoint Server encodes the binary file data (e.g. Word, Excel file) into text (using Base64 encoding) in order to send it over Web Service. The client application should decode it back to the original encoding for viewing the file. This can affect performance.

-          Memory overhead: The GetItem() method uses a single buffer to retrieve the whole file content.

Finding List of all Installed KB on your windows server or pc

To get all the KB installed on your machine / box / pc / server, your best bet is to simply run the following command from the command prompt:

reg query HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall >> C:\list_of_kbs.log

Using Add Remove Program from control panel or any other method doesn't give you all the KB's on your box.

SharePoint Large List Performance and Views Performance on Large List

SharePoint is a Collaboration and Content Management tool, although more often gets over boarded with a feel of a true Document Repository tool. SharePoint List/View customization can facilitate storing large number of documents efficiently and behave in line with repository tools, to take this further we can integrate archiving tools with SharePoint and reduce the SQL database load/cost.

Sooner or Later the number of items in most of the out of box lists will outgrow the logical performance threshold of 2000 items per container; this will directly impact the performance of the list and views. SharePoint List design and customization can optimize the list / views performance and efficiently manage the large number of items in the list,

List / Document Library

       · Custom List Design / Provisioning

Instead of having one large list or list with folders, where possible create as many lists as possible based on: Content Type, Logical Category, and so on

o No more than 2000 lists per site (soft limit)

o List is not a RDBMS

o Lookup columns should be used sporadically

· Active List and Archive List

One model, which can be used to have performance intact for list is to have a separate list for archived items. In other words, Items should be archived to another list when the list size approaches 2000-4000.

o Active/Custom List items (maximum 2k-4k items)

Items fall in the active list if

· used almost on daily basis

· in dynamic/transient state

· in draft mode

· recently created

· recently published

· versioning is enabled

o Archive List items (maximum 5 million)

Items based on a pre-configured logic, gets moved from Active List to Archive List

· Archive List should have folder implementation in place to organize the items. Where each folder has maximum of 2000 items and the list will contain total of 5 million items maximum.

· One level deep folder is being recommended, further folder nesting has same issue as file system

· Folder creation scan be based on a logical nomenclature (like year and week number) or a unique id (like guid or date time), so on.

· No versioning required as item is in its final state and archived.

 Views

o Views for Archive list

o It’s advisable to use “Search” feature to lookup an item or desired set of items. This would require SharePoint Search configuration and indexing.

o The other option, which can be leveraged is to view items in the folder by using the corresponding folder link

o Views for Active List i.e. Custom List

o Views created on Lists should be displaying items in batches of 10, 20, not more than 100 items.

o Views filtering should be designed carefully

o Usage of indexed columns

· Each column index consumes disk space and resources in the database. Add indexes only to columns that will be used actively in the views.

· Only one indexed column can be used in a view filter and it’s important that the first column that you use to filter the view has an index

o Filtering by an indexed column is more efficient then defining an item limit in a view.

o Complex filtering should be avoided instead design document library as separate entities

o Indexed column is not utilized in Views that use an OR filter condition.

o View should use a filter that will not touch more than 2000-4000 items during its processing.

o Views designed using SharePoint Designer

o The page size (i.e. downloaded data by the browser to render the page), where views is being designed should be of reasonable size (300-500 KB)

o Data View web part uses web services to fetch data from the list

o Content Query web part is the fastest means of pulling data from list.

 Page

Before making a customized page live in production performance should be analyzed using tools like

o Http watch

o Page speed

 Browser

IE 8.0 is recommended for better performance as compared to IE 6.0 / IE 7.0

IE 8.0 allows more TCP connections at a time compared to IE 6/7

Archiving

o Tools or BLOB storage can be used to reduce the data base load / cost and further enhance the list performance and also bring the archiving capability to its best.

o Records Management can be used to distribute the database Load