Reza Alirezaei’s Blog

Once upon a time, there was a SharePoint developer who decided to build a site in SharePoint 2007 . Well, maybe there were more than one developer, say a developer and an architect, but one developer is enough to start my story.

The developer decided to create a SharePoint list with 30 lookup columns to other lists! The client has added approximately 700 items to that list since then.

Life was good and everyone was happy until one day the list started acting funny! Sure enough, IT department was alerted that the site is no longer working. “Updating list items is darn too slow!”, said the client with frustration, “Why Can’t SharePoint handle such a simple scenario?!! $#%&@#… I hate SharePoint”.

IT department noticed that this little issue is locking out the entire SQL Server instance and causing performance issues for other applications in the farm. As saying goes, one bad apple is enough to make a whole barrel of apples bad!

— End of the story—

We looked at the list’s schema and obviously the first thing that stands out is 30 lookup columns!

Is having 30 lookup columns really that bad?!

Since I have not found any official statement against it, I would assume that SharePoint does not have a predefined limit on the number of lookup columns a list can contain. Nevertheless, the actual number of lookup columns is limited by your hardware configuration and by the design of your farm, as it is the case with foreign key constrains in SQL Server.

Remember, lookup columns are conceptually similar to foreign key constrains (MSDN).  So, it’s good practice to consider the cost of enforcing them when you design your lists.

In our case, however, the number of lookup columns was not the issue at all!! I have a list with 35 lookup columns in another site and it’s working just fine. Also; when we took the same list to SharePoint 2010 , then there was no performance issue. In another word, SharePoint 2010 perfectly handled the same list that was misbehaving in SharePoint 2007. So, there must have been something in the list schema that MOSS 2007 didn’t like it!

I looked at the individual lookup columns and noticed that there are 10 self-referencing lookup columns. Those are the list columns (SPFiled) pointing back to the Title field of the same list to simulate dependencies. Something similar to Employees table , but with one difference:  imagine that each employee could have up to 10 managers!! so you would have Manager1ID, Manager2ID…. Manager10ID, all pointing to EmployeeID of the same table.

I asked our SharePoint admin to send me the SQL query that was executed under the hood, and sure enough there were lots of LEFT OUTER JOINs generated as the result of self-referencing columns. Obviously, the cost of query was too high!!

The first thing we did was to index the lookup columns but it didn’t make any difference. As matter of fact ; I think it made it even slower as the general rule of the thumb with indices and update/delete/insert operations. Then I went a head and proposed two other solutions:

1. Completely redesign the list and its dependencies. <– Due to time and budget constraints this was not an option!
2. Normalize the relationship. Create a second list which duplicates some of the data of the primary list and have the primary list look up that list instead of itself. Kinda like this:

I’ll be the first one to admit that the second solution is not necessarily the best one. It was proposed to bring the farm back to the operational mode as well as ensuring that business continuity is maintained.

Conclusion:
It’s amazing how just one simple design flaw may result in devastating consequences and change people’s perception of SharePoint.