Best Practices For Accessing And Retrieving Data in SharePoint 2010 Part 4 List Views And Metadata

List views offer several benefits over previously discussed approaches in this series. Lists views provide a heightened experience through dynamic sorting and filtering while being simple to customize the views and to display various columns. The problem with views is they tend to be very expensive in terms of performance with the slowest result time.
Both list views and metadata navigation offer support for content retrieval when it comes to large document libraries, folders, and indexes. When you query with a list view in real time and queries with the SQL server database you can get recent results and decent performance. The throughput will decrease with large list size. List views also allow for the most recent content loaded to show up on page.

Metadata navigation and list views can be used in order to increase the ability to perform list view actions for an item. List views are the primary method for working with a list in low read scenarios. When there are many read operations in place you need to consider the other query methods and use them to help you access the list data.

There are many things to look at for the topic of view configuration. Choose carefully the columns that will be used in views. When you add more columns you have more data available. However, this makes the page load time longer so that is the trade off you have to make. Finding the balance that works well for you here is very important. Reduce the number of lookup columns in order to manage the metadata for people and groups. They can be connected with other database tables. Avoid using totals for columns. If you don’t plan to use indexed columns for filtering then you need to have the items show in folders. This ensures that the folders will never have more items in them than the list view threshold. The views need to be filtered on indexed columns to help reduce the number of items returned. This is especially true if there aren’t any sub folders to push more items into than the limit for the list view threshold. Allow the metadata navigational feature to offer the most recent results for queries. They would otherwise be subjected only to the list view threshold. This is usually going to be enabled for you on all the sites. When filtering views with metadata navigation you should consider using per location views. They create unfiltered views for metadata navigation pivots.

Views are typically used to help access list data. Views have to be selected carefully though to optimize the way in which a user is able to find content and to meet the requirements of policies for performance. With a list that is large in size, it is important to carefully decide how to configure it. The standard views should be used. Avoid Datasheet, Calendar, and Gantt views as they can cause you to exceed the list view threshold.

The views should always have the least number of columns as you can possibly arrange. Pay close attention to the number of lookup columns though. They have to be able to join other tables or your performance suffers.

With SharePoint Server 2010, as discussed in previous posts, the new list view threshold significantly changes the approach for how to use view with a large list. Users may get errors if they attempt to return more results than the list view threshold allows. The use of totals on the large list will be blocked when that occurs. The number of items have to be scanned and that is what is important versus the number of rows that are returned.

When a list is organized a folder shouldn’t contain more items than the list view threshold. When you show all items outside of folders you should avoid the mechanics being in place where they can filter the results.

The recently changed items view is used for a user to identify what has changed. It can be set at the default when a user frequently accesses various types of content from the items in the list. This view is simple enough to configure as it relies on system metadata that is set for each of the items. When you have a large list then you either need to set it for the item limit of filter the results to show less than the list view threshold. This view is created when you index the modified field. Then you sort it in descending order. The my items view can be used with repositories where users access the certain documents frequently. This is simple to configure as it uses the system metadata that is set for each time. You can filter it with the modified by or created by columns. This is a view in the filters that can be set for a multiple user columns. It won’t show all of the documents in this view but the most recent.


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.


The List View Threshold In SharePoint 2010

There are several things to take into consideration with the list view threshold in SharePoint 2010. There are several operations here including non-indexed queries that allow for columns to be added. They can be used to make a list and to give time for resources that are proportional to the number of items in the list. When you have a small list you don’t have to add items quickly. The bigger the list gets though the more the related operations is going to take up your available resources.

The list view threshold is a safety in place to let you know when you should change the query. It also lets you know that the data can be accessed for performance when the farm usage is low. The list view threshold is the maximum number of items that a database can hold at once. The default is set to 5,000 items and it will impact how our holistic system operates so don’t increase it.

You don’t want to prevent a query from being filtered on columns that aren’t indexed. When that happens there is a filter in place and the correct data set will be affected. You need to allow the default value for the limit to be based on farm and list performance. This is how the SQL Server is able to manage locks, so it is a good idea to leave it just the way it is.

You want to minimize database contention and the best way to do so is with the SQL Server row level locking. This is a strategy that allows for accurate updates to occur without affecting users when they have access to other rows. If a read or a write database operation including queries is in place it can mean that 5,000 rows are all locked at the same time. It will be handled more efficiently when the SQL Servers in place to serve as the lock. This means the entire table will be complete. It is going to stop other users from being able to access that table.

The mixture of queries allows for the return of all the items in the list and then more items can be returned later on. When the limits are changed from the default of 5,000 to 10,000 there is a huge impact of the overall performance. It is a good idea though to focus on high performance from the queries.

With list view threshold there are some exception due to the fact that there are operations to perform. They may not do well though if you reconfigure them. They have to be raised to the limit you will need so that they can sufficiently operate as you perform them. The worst that can happen is that you will need to change the enable throttle setting for a given list to false. Then you can ignore the list view threshold.

However, this can only be accomplished at the list level. You can’t do it for a site. You should only be doing this when you want to allow a list access while changes are being made to fix poor performance operations that are being blocked. As soon as you are done with all of that the enable throttle setting needs to be changed back right away.

Both farm and local computer administrators on the web end of things where the server is and the query starts won’t be blocked by the list view threshold. They are users that are browsing large lists and it could be found that some of them aren’t properly configured. They have to be careful when testing them so that the data is normal when other users see it. This is why some operations are prevented by the list view threshold.

There are time services that can be run with an account that isn’t protected by the list view threshold. This allows for various scenarios to be in place including a creation of an index for a large list later on. The use of general will apply so that there aren’t problems during such a scenario playing out.

With the list view threshold for auditors and administrators, the list view threshold for service account is in place. There is a limit for caching the results of a large query and therefore it saves resources. The custom code allows for a request to use the higher limit for running an account that is going to comply with the web application security policy.

Object model override determines if the service accounts can be part of the list view threshold for auditors and administrators. With a farm administrator there is the ability for the object model to be overridden. This is a program specific ability that has some exceptions.

The programmers with authorization can request for a query or a list and then benefit from it. They can change the value so that custom codes can be used to override what is already in place. It is a good idea to leave this setting at the default.

There is a daily time window that can be set for the operations to be performed. This is allowed to occur without being subject to the list view threshold. The time can be changed by 15 minute increments for a period of up to 24 hours. The database operation or query will start with the daily time window where it continues until it is completed so that it doesn’t finish in the time specified.

By default the daily time window won’t be configured because of the fact that off peak hours can vary. We suggest that you only have a daily time window in place to be specified if you have reasonable off hours. The time frame to use would be when very few people will be using a given Web application. The users are able to perform administrative operations for large lists. This includes creating indexes. All of this is best to do during those periods of time when farm usage is lower than normal.