Friday, May 16, 2014

Managing large list in SharePoint 2013

Its time for me to recollect and update my knowledge on "Managing the huge list and libraries" and limitation on SharePoint 2013.
Absolutely there are so many improvements done in SharePoint 2013 while comparing its predecessor.
Quick notable points are

Artifacts
Limitation
Items in List and Library
50 million =  (50 * 10000000)
File size in attachment
50 Gig
Lookup columns in list
Can hold up to 8
Indexing the columns
Cannot be indexed on choices, multiline of text, Calculated columns, People picker with multi valued. External column, hyper link or photo columns and Lookup with multivalued
Query able per request using SharePoint UI
Default – 5000 items. Programmatically increase the (overriding the threshold) for admin user.
By default List View threshold for Auditors and administrator 20K
Working offline with Excel – Leverage the Chart, Pivot table against SP List
One way connection with MS Excel
Working offline with Access  Service
Advantage: Bi directional. When sync the data you are directly working with Service. Process the batches of 2000 items at a time.
Disadvantage: Access Service has its own threshold.  

Best practices to be followed when dealing with large list or library
1.       Create an index column and then use this column to create the filtered view.
2.       If the indexed column based view exceeding the List View threshold then continue your operation on off peak hours.
3.       You can also create the “Access App “to work with large list.
4.       Use the Excel to communicate with SharePoint List in off line mode. Add your records in excel and refresh the link.
5.       Use Meta data based filtration on List / Library.
6.       Create your personal view to get filtered that relevant to you.
7.       Enable the RSS Feed on your list and library.
8.       Manage your Task, Calendar, discussion and document library sync with Outlook.

Updates: (Managing the large list when you deal with Programmatically)

When you are working with huge list that’s any time can exceed the default thrash holding size.
You have to be careful when querying that list in order to retrieve or any against the data.
By default SPQuery will returns all fields in the list and sometime if your list had more than 8 lookup columns, then you can expect only 8 lookup columns and remains will silently suppressed in the result set.
You can optimize the CAML query that limits the rows (RowLimit) and required columns using ViewFields property.
Creating the index column in the SharePoint list considerably reduces the complexity and faster response on the loading time and as well as when you filter the list view.

·         You can create up to 20 columns as indexed.
·         Two types of Index available i.e. single column and compound (primary and foreign key).
·         Scan all columns in the list, if you find unique then you can index that column.

Content Iterator


Content Iterator API is a new mechanism to overcome the SPQueryThrottlingException on the list already exceeded the default throttling limit. This API uses the Callback pattern for segmenting the query for processing a single item at a time.

It has core method name “ProcessListItems” that takes 4 arguments i.e. List, Query, ProcessListItem and ProcessError.