List View Thresholds And Blocked Operations In SharePoint 2010

There have been several past posts that deal with the list threshold, such as here. A list may exceed the list view threshold and then some operations will be blocked. The big problem with this is that the default list view can’t be used to access the list, bad news bear! They have to be properly configured before they can work with a large list. The list view threshold blocks the database operations that affects more items than that threshold allows. It won’t just affect the number of items that have been returned.

There are two classifications that come into the picture when you have large lists: “List Exceeds The List Value Threshold” and “Container Exceeds The List View Threshold”.

There are operations that can be blocked when the size of the entire list exceeds the list view threshold. This occurs even if the items are placed into folders. These operations include managing and checking versions, operations of all items, and recursive queries. The views that return all items without folders can also be prevented. There are operations that affect a complete list too including adding a column or deleting indexes and they can be blocked.

There can be operations prevented due to the folder for the list containing more items than the list view threshold allows. You won’t be able to rename it or to delete it so you do need to be careful. The list view threshold can prevent you from performing some common actions when you setup your list. This is why you should configure the columns and indexes for a list before the size is greater than the list view threshold.

Should a list exceed what the list view threshold allows, then you need to plan to configure it correctly. You need to configure view and navigational options well in advance. However, lists can grow beyond the list view threshold and that will require some action from you. For example when you create a column or index that column in a list you need to be prepared for it to take time. The operations are prevented by the live view threshold. They can be performed during the daily time window. They can also be performed by the farm and computer administrators.

The operations need to be planned well in advance. The list may be too big so you will need to use a daily time window. An administrator with the right privileges may be needed in order to perform the necessary operations. It is possible for a list to become so large that some of the operations can time out when they are used in associated with a Web browser.

List Exceeds The List Value Threshold

  • Add/Remove/Update a list column All of the columns including lookup and calculated columns. There are updates such as name change. They aren’t blocked due to the fact that they won’t affect all of the items in the list.
  • Add/Remove/Update a list content type Every item in the list is affected so it is blocked if the list has more items than the list view threshold.
  • Create/Remove indexes it is blocked for any list that has more items than the list view threshold has it affects each item in the list.
  • Mange files The non indexed query fails for any list that has more items than the list view threshold.
  • Non indexed queries This includes filters and various sorts. The operation will fail if the list size is larger than the list view threshold. There isn’t an index so a full scan of the list occurs. The items will all be returned but the folders will be ignored.
  • Cross list query This includes the various queries by the Content Query Web Part. It follows the list view threshold setting for auditors and administration. The default for it is 20,000. For operations above that threshold it will fail.
  • Lookup columns This refers to those that enforce relationship behavior. You can’t offer lookup columns like this though if the list references content for more items than the list view threshold.
  • Delete a list This is blocked if the list has more items than the list view threshold due to the fact that it affects every item in the list.
  • Delete a site This affects all of the items in the list so it’s blocked for any list if there are more items in it than the list view threshold.
  • Save the list as a template This affects all of the items in the list so it is blocked for any list with more items than the list view threshold.
  • Show totals in list views This performs a query against all of the items in the list. It is blocked for a list that has more items than the list view threshold.
  • Enable/Disable attachments for a list This affects all of the items in the list so it will be blocked when the list offers more items than the list view threshold.
    Container Exceeds The List View Threshold

  • Delete/Rename/Copy a folder This fails if the folder contains more items than the list view threshold as too many of the rows will be affected.
  • Queries to filter non indexed columns This fails if the folder or list has more items than the list view threshold. It performs a full scan against the entire folder because there isn’t an index.
  • Fine grain security permissions This fails when the list or the folder that is being set has fine grained permissions that contain more items than the list view threshold as too many rows are affected. You can use the fine grain permissions on documents in a large list. However, you can’t set the permissions of the list or of the folders if they contain more than the list view threshold.
  • Open with Explorer This won’t show any items if a container has more items than the list view threshold, other than in reference to sub folders. If the root list contains more items than the list view threshold the “Open with Explorer” won’t show anything. In order to use Open with Explorer the list needs items to be organized into folders. The amount needs to be less than the list view threshold in the root for a given container.

Best Practices For Accessing And Retrieving Data in SharePoint 2010 – Part 3 – Search WebParts

When considering data access approaches in SharePoint 2010, it is important to also consider the role that search will play. Search queries are able to scale better than when you directly access SQL Server resources. This is due to the fact that such a search is optimized for high-read scenarios. It is simpler for it to scale out multiple search index and query servers when compared to scaling SQL Server needs. Search queries are less bothered by list size than Content Query Web Parts or list view queries. The Search Web Parts allow offloading queries from SQL Servers for scaling searches while the query and index servers are scalable and perform better. The results are determined by the full text search of the documents and a summary of text offers more information than those from other Web Parts. The problem is that results are only as current as the last crawl, no column values are displayed, and being unable to perform list actions.

Search offers you the best overall performance at various high scale points. The search shouldn’t be used if list actions have to be offered on item. It also won’t work if the data has to be offered in real time. This is because the results will only be as current as the last crawl. There are several WebParts that structure the aggregate search system:

  • Core Results Web Part Complete results with the paging and the full featured Web Part. It can also handle specific queries based on the user or the system.
  • Federated Results Web Part Small sets of results will be offered with a link that is optional. This is to allow for viewing of the full results.
  • Search Box Web Part The Web Part is used to accept the input from the user for a query.

Best Practices For Accessing And Retrieving Data in SharePoint 2010 – Part 2 – Content Query Web Part

There are several beneficial reasons to use the Content Query WebPart with SharePoint 2010. The Content Query WebPart retrieves the content from lists. This can be used for pages, documents, and lists. The Content Query WebParts are cached by default and which offers better overall performance. It also uses fewer SQL Server resources. The default cache setting is for thirty minutes so you will have data that is close to being current. However, this also means that you will use more SQL Server resources than the search queries do. It is excellent for navigating, has simple configuration for the column displays, multiple content query WebParts can be used on the same page, it has fast load time when you compare it to list views and search, and SQL use is reduced due to the default for cache. While there are a limited number of properties are displayed, links go to the items directly, and list actions can’t be performed it is still exceptionally useful.

There are several things that need to be taken into consideration regarding the Content Query WebPart. Content Query WebParts can be used to return items that user’s access often and can be used for a large list. You should use filters though for items so that the query won’t exceed the list view threshold. With the Content Query WebPart only columns with indexes for filtering should be used and shouldn’t be used when you need to query multiple lists if you will exceed the view threshold for auditors or administration.

Since Content Query WebParts offer the least amount of HTML they are faster for the pages to show up. You can also use multiple Content Query WebParts to configure the pages. Cached content query WebParts provides quick data access as list size gets larger. The non cached Content Query WebParts have latency on part with a similar list view.

Content query WebParts can be used as part of navigation to help provide content on pages. It can also be used for high read scenarios where most of the users don’t need to perform list actions.

The Content Query WebPart can be used as a means of accessing the content without entering a list view. A user is able to have a small amount of content that they use often or that they have a desire to track. The document center site templates make this possible based on information logged by a given user. The defaults can also be set to track highest rated documents and more recent documents accessed. The use of these measures allows for a user to quickly access such documents again and again.

The use of Content Query WebPart when you have a large lists allows you to get accurate results. You want to make sure you follow the guidelines though so you don’t get blocked due to the list view threshold being exceeded. The items have to be filtered so that they are less than what will be used by the indexed column. It isn’t recommended that you use cross list queries for a large list.