Reza Alirezaei’s Blog

As you probably know, Web Part Gallery and the way web parts are added to the page are completely changed in SharePoint 2010. The following picture, shows the new Web Part Gallery and its new positioning on the top of the page:

While this is great, there might be cases in which you want the Web Part gallery to appear in the right side of the page (like good old SP2007 time). Since SharePoint heavily depends on URLs to accomplish various tasks, URLs come handy in this particular case again! First, check out the page. Then append ?PageView=Shared&ToolPaneView=2
to the page URL and hit the Enter. Now, your page should look like this:

Add your Web Parts from the gallery, check the page back in and finally publish it.

Obviously, with switching back to SP2007 Web Part Galley style, you will lose some of the new features. For example, SP2010 Web Part Galley allows you to add Web Parts to the rich content areas (such as Wiki pages) as shown in the picture below, but the old gallery only adds to the Web Part zones. That’s the first difference!

The second difference is that the new Web Part Gallery uses WebPartAdder class which you can use to dynamically populate the gallery from different sources including the Web Parts deployed to the server. See Wictor’s post here for more information.

It’s imperative to note that both approaches allow you to bucketize your Web Parts into the groups that you want. If you go to Site Settings > Galleries > Web Parts. You will find all the Web Parts deployed to the server. As you can see the gallery (which is essentially nothing but a list) has a column called Group. You can go ahead and edit group column for each Web part and add your own grouping as shown in the following picture:

Then, the new group will show up in both gallery types as shown below:

I am pleased to announce that the book I was a co-author for, with Brain Wilson, Bill Baer, Martin Kearn and Joel Olson, is now available for pre-order.

Please check it out if you are intrested:

http://www.amazon.com/SharePoint-2010-Enterprise-Architects-Guidebook/dp/0470643196

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.

The book that I co-authored with other SharePoint MVPs, Real World SharePoint 2010, is now available online for pre-order, though it won’t ship before end of October.
For more information see http://www.amazon.com/Real-World-SharePoint-2010-Indispensable/dp/0470597135

Gotta love Canada Day that’s the anniversary date for my MVP award!

It’s a late kind of announcement, but just a quick note that I was awarded MVP status again.  This makes it my fourth year as a SharePoint MVP and I’m looking forward to another exciting year of helping the SharePoint developer community through my presentations, upcoming new book (will be announced later), articles and blog posts.