Reza Alirezaei’s Blog

As you know, one of the investments areas in workflows in sharePoint 2010 is the full workflow development life cycle. This cycle starts with information workers modeling the workflows in Visio which can then be imported to SharePoint Designer (for further declarative customizations) and finally to Visual Studio 2010 for developers for hard-core coding and customizations.

Well, the life cycle (Visio >SPD > Visual Studio) may seem to be straightforward, however, it’s not always a clean process! For example, during modeling if someone decides to have an approval activity in the model, below is what the poor developer will get in the workflow design canvas in Visual Studio 2010:

Workflow modeling and development, like anything else in SharePoint 2010, requires planning and education for all project stakeholders!

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

When you deploy a site-level workflow using Visual Studio 2010, Post-Deployment build configuration takes care of activating the workflow feature, and programmatically associates the workflow to the site.

When you deploy the wsp package using PowerShell or stsadm , the magic won’t happen! So, if you go to View All Site Content > Site Workflows, the page is pretty empty with a message saying that “there are no workflows currently available to start on this site”

What you need to do is to put the following code in your feature receiver:

[CSharp]
private string workflowTemplateBaseGuid = “1e547b46-3d00-43b0-928d-4fe103b92fcb”;
private string workflowAssociationName = “My Workflow”;
private string workFlowHistoryListName = “Workflow History”;
private string workFlowTaskListName = “Workflow Tasks”;

public override void FeatureActivated(SPFeatureReceiverProperties properties)
{
var site = properties.Feature.Parent as SPSite;
var web = site.RootWeb;
SPWorkflowTemplateCollection workflowTemplates = web.WorkflowTemplates;
SPWorkflowTemplate workflowTemplate =
workflowTemplates.GetTemplateByBaseID(new Guid(workflowTemplateBaseGuid));

if (workflowTemplate != null)
{
// Create the workflow association
SPList taskList = web.Lists[workFlowTaskListName];
SPList historyList = web.Lists[workFlowHistoryListName];
SPWorkflowAssociation workflowAssociation =
web.WorkflowAssociations.GetAssociationByName
(workflowAssociationName, CultureInfo.InvariantCulture);

if(workflowAssociation ==null)
{
workflowAssociation = SPWorkflowAssociation.CreateWebAssociation
(workflowTemplate, workflowAssociationName, taskList, historyList);
workflowAssociation.AllowManual = true;
workflowAssociation.Enabled = true;
web.WorkflowAssociations.Add(workflowAssociation);
}
}
}
[/CSharp]

One thing to note is that in sharePoint 2010, SPSite.SPWorkflowManager.GetWorkflowTemplatesByCategory() doesn’t properly return the workflow template collection like SharePoint 2007. So, I had to fall back to the traditional way of using SPWeb.WorkflowTemplates to get my workflow template and create the SPWorkflowAssociation object.

Obviously, in your FeatureDeactivating code, you need to add the necessary code to remove the current instances of the workflow; otherwise if you deactivate the feature and activate it, the workflow doesn’t show up again in View All Site Content> Site Workflows again. A no-code workaround for this issue is to go to Site Settings > Site Administration > Workflow Settings; and remove the instance manually.

Some excerpts from the books that I have co-authored are now available on MSDN:

Professional SharePoint 2010 Development
Real World SharePoint 2010: Indispensable Experiences from 22 MVPs

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.