SharePoint 2010 Lookup Columns and Performance Considerations
When designing lists with lookup features in SharePoint 2010, there are several architectural considerations that must be examined before implementation. With the lookup columns you get a list view with a separate table. The lookup columns in a view will increase the complex nature of list view filter queries and of metadata navigation. With managing the metadata columns though both people and group columns can be considered as lookup columns. When you add more lookup columns to a view you don’t have any decrease in overall performance. It will be stable to a certain point but then you may start to see it decreasing.
A configuration default of eight lookup columns per list view exists. If you add more then you will experience a decrease in the results for the queries in that view. This process is also one that will reduce the resources of the SQL server. Therefore you are strongly encouraged to stay well within those 8 lookup columns for all views. When there are more lookup columns then it won’t be able to be synced with the SharePoint Workspace (or site or whatever).
In each of the lookup columns there is a list view and it joins a second table. There is a lookup column for each view that increase the overall complexity of the queries. When you have a standard lookup in these columns, the single value and multiple value managed metadata count. When you add more lookup columns to a view that isn’t gradual or linear in performance then it has to be stable for the eight columns. Otherwise your performance levels will rapidly decrease.
There is a huge change when you have ten lookup columns. With a list that is very large the amount of queries will have some views that contain more than eight lookups. This can cause the query to use up a large amount of SQL Server resources. That is why you should never change the default to more than eight.
The performance will drop, when that happens you will have a harder time getting results from the query. SharePoint Workspaces aren’t able to sync the list with more than eight lookup columns offered. It doesn’t matter if the columns are used in view or not.