We’ve seen this couple of times lately, so I thought I’d mention it. SharePoint 2007 and 2010 share a common guidance about restricting the number of lookup columns – people links, or links to another list. SharePoint performs best when there are eight or fewer. For most document libraries, the Created By and Modified By fields count for two of them, leaving six available lookup columns.
Sometimes, I’ve been asked to recommend a memory upgrade to boost performance of an existing SharePoint farm. In one case, the culprit was a SharePoint list with about 1000 items but more than 80 attached workflows. More recently, we saw really slow page load
times on a 2007 farm for a list with only three items – but, again, 25 attached workflows.
The catch is that by design, each SharePoint Designer workflow adds its own lookup column to each attached list or library. So if you have seven workflows, even if you
have no other fields, you’re already over the threshold.
Now, in SharePoint 2010, you can elevate this value in Central Admin, under Application Management | Web Application | General Settings | Resource Throttling. You might experiment with a value of 12 or 15.
But don’t just push the number high arbitrarily. It’s a Band-Aid, and not a complete solution. The reason the throttle exists is because SharePoint already uses complex SQL joins to get documents back from the content database. Here’s a wonderfully readable excerpt trapped by SQL Profiler – see all those LEFT OUTER JOIN LOOP Workflow lines?
SELECT TOP 1 @DocParentIdForRF = Docs.Id FROM Docs WHERE Docs.SiteId
= @SITEID AND Docs.DirName = @FDN AND Docs.LeafName = @FLN; SELECT TOP(@NUMROWS) t1.[SortBehavior] AS c0,t3.[tp_ID] AS c14c8, UserData.[nvarchar15], t9.[Status1] AS c21c16, t10.[Status1] AS c22c16, t1.[ScopeId] AS c4, UserData.[nvarchar4], UserData.[tp_CheckoutUserId], t2.[tp_ID] AS c6c8, t3.[nvarchar1] AS c14c7, t3.[nvarchar6] AS c14c10, t8.[Status1] AS c20c16, UserData.[tp_ModerationStatus], UserData.[tp_Level], UserData.[nvarchar17], CASE WHEN DATALENGTH(t1.DirName) = 0 THEN t1.LeafName WHEN DATALENGTH(t1.LeafName) = 0 THEN t1.DirName ELSE t1.DirName + N''/'' + t1.LeafName END AS c3, t2.[nvarchar1] AS c6c7, t2.[tp_Created] AS c6c11, UserData.[nvarchar14], t1.[TimeCreated] AS c2, UserData.[tp_Modified], UserData.[nvarchar3],
t2.[nvarchar6] AS c6c10, t7.[Status1] AS c19c16, UserData.[nvarchar19],
UserData.[tp_ID], t1.[IsCheckoutToLocal] AS c13, t5.[Status1] AS c17c16,
t6.[Status1] AS c18c16, UserData.[nvarchar16], t1.[LeafName] AS c1,
t1.[LTCheckoutUserId] AS c12, UserData.[tp_Editor], t3.[nvarchar4] AS c14c9,
t4.[Status1] AS c15c16, t1.[Type] AS c5, t3.[tp_Created] AS c14c11,
UserData.[nvarchar13], UserData.[nvarchar18], t2.[nvarchar4] AS c6c9,
UserData.[tp_ContentTypeId] FROM UserData INNER MERGE JOIN Docs AS t1
WITH(NOLOCK) ON (UserData.[tp_RowOrdinal] = 0) AND
(t1.SiteId=UserData.tp_SiteId) AND (t1.SiteId = @SITEID) AND (t1.ParentId =
UserData.tp_ParentId) AND (t1.Id = UserData.tp_DocId) AND ( (UserData.tp_Level
= 1 OR UserData.tp_Level =255) ) AND
(t1.Level = UserData.tp_Level) AND ((UserData.tp_Level = 255 AND t1.LTCheckoutUserId
=@IU OR (UserData.tp_Level = 1 AND (UserData.tp_DraftOwnerId IS NULL) OR
UserData.tp_Level = 2)AND (t1.LTCheckoutUserId IS NULL OR t1.LTCheckoutUserId
<> @IU ))) LEFT OUTER LOOP JOIN AllUserData AS t2
WITH(NOLOCK,INDEX=AllUserData_PK) ON (UserData.[tp_CheckoutUserId]=t2.[tp_ID]) AND
(UserData.[tp_RowOrdinal] = 0) AND (t2.[tp_RowOrdinal] = 0) AND ( (t2.tp_Level
= 1) ) AND (t2.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND
(t2.[tp_CalculatedVersion] = 0 ) AND (t2.[tp_DeleteTransactionId] = 0x ) AND
(t2.tp_ListId = @L3) AND (UserData.tp_ListId = @L4) LEFT OUTER LOOP JOIN
AllUserData AS t3 WITH(NOLOCK,INDEX=AllUserData_PK) ON
(UserData.[tp_Editor]=t3.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND
(t3.[tp_RowOrdinal] = 0) AND ( (t3.tp_Level = 1) ) AND
(t3.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t3.[tp_CalculatedVersion] = 0
) AND (t3.[tp_DeleteTransactionId] = 0x ) AND (t3.tp_ListId = @L3) AND
(UserData.tp_ListId = @L4) LEFT OUTER LOOP JOIN Workflow AS t4 WITH(NOLOCK) ON
( CAST ( CAST (UserData.[nvarchar13]AS VARBINARY) AS UNIQUEIDENTIFIER)=t4.[ID])
AND (UserData.[tp_RowOrdinal] = 0) AND (t4.SiteId = @SITEID) AND (t4.WebId =
@WEBID) AND (t4.ListId = UserData.tp_ListId) AND (t4.ItemId = UserData.tp_Id)
AND (UserData.tp_ListId = @L4) LEFT OUTER LOOP JOIN Workflow AS t5 WITH(NOLOCK)
ON ( CAST ( CAST (UserData.[nvarchar14]AS VARBINARY) AS
UNIQUEIDENTIFIER)=t5.[ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t5.SiteId =
@SITEID) AND (t5.WebId = @WEBID) AND (t5.ListId = UserData.tp_ListId) AND
(t5.ItemId = UserData.tp_Id) AND (UserData.tp_ListId = @L4) LEFT OUTER LOOP
JOIN Workflow AS t6 WITH(NOLOCK) ON ( CAST ( CAST (UserData.[nvarchar15]AS
VARBINARY) AS UNIQUEIDENTIFIER)=t6.[ID]) AND (UserData.[tp_RowOrdinal] = 0) AND
(t6.SiteId = @SITEID) AND (t6.WebId = @WEBID) AND (t6.ListId =
UserData.tp_ListId) AND (t6.ItemId = UserData.tp_Id) AND (UserData.tp_ListId =
@L4) LEFT OUTER LOOP JOIN Workflow AS t7 WITH(NOLOCK) ON ( CAST ( CAST
(UserData.[nvarchar16]AS VARBINARY) AS UNIQUEIDENTIFIER)=t7.[ID]) AND
(UserData.[tp_RowOrdinal] = 0) AND (t7.SiteId = @SITEID) AND (t7.WebId =
@WEBID) AND (t7.ListId = UserData.tp_ListId) AND (t7.ItemId = UserData.tp_Id)
AND (UserData.tp_ListId = @L4) LEFT OUTER LOOP JOIN Workflow AS t8 WITH(NOLOCK)
ON ( CAST ( CAST (UserData.[nvarchar17]AS VARBINARY) AS UNIQUEIDENTIFIER)=t8.[ID])
AND (UserData.[tp_RowOrdinal] = 0) AND (t8.SiteId = @SITEID) AND (t8.WebId =
@WEBID) AND (t8.ListId = UserData.tp_ListId) AND (t8.ItemId = UserData.tp_Id)
AND (UserData.tp_ListId = @L4) LEFT OUTER LOOP JOIN Workflow AS t9 WITH(NOLOCK)
ON ( CAST ( CAST (UserData.[nvarchar18]AS VARBINARY) AS
UNIQUEIDENTIFIER)=t9.[ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t9.SiteId =
@SITEID) AND (t9.WebId = @WEBID) AND (t9.ListId = UserData.tp_ListId) AND
(t9.ItemId = UserData.tp_Id) AND (UserData.tp_ListId = @L4) LEFT OUTER LOOP
JOIN Workflow AS t10 WITH(NOLOCK) ON ( CAST ( CAST (UserData.[nvarchar19]AS
VARBINARY) AS UNIQUEIDENTIFIER)=t10.[ID]) AND (UserData.[tp_RowOrdinal] = 0)
AND (t10.SiteId = @SITEID) AND (t10.WebId = @WEBID) AND (t10.ListId =
UserData.tp_ListId) AND (t10.ItemId = UserData.tp_Id) AND (UserData.tp_ListId =
@L4) WHERE ( (UserData.tp_Level = 1 OR
UserData.tp_Level =255) AND (
UserData.tp_Level= 255 AND UserData.tp_CheckoutUserId = @IU OR ( UserData.tp_Level = 2 AND UserData.tp_DraftOwnerId IS NOT NULL
OR UserData.tp_Level = 1 AND
UserData.tp_DraftOwnerId IS NULL ) AND ( UserData.tp_CheckoutUserId IS NULL
OR UserData.tp_CheckoutUserId <> @IU))) AND
(UserData.tp_SiteId=@SITEID AND (UserData.tp_ParentId=@DocParentIdForRF)) AND
(UserData.tp_RowOrdinal=0) AND (t1.SiteId=@SITEID AND (t1.ParentId=@DocParentIdForRF)) ORDER BY t1.[SortBehavior] DESC ,t1.[LeafName] ASC ,UserData.[tp_ID] ASC OPTION (FORCE ORDER, MAXDOP 1)'
Runaway workflow generation is better handled through systems design. That’s a much bigger topic, but here are some concepts to get you started:
- Consolidate your designer workflows into fewer, but more sophisticated workflows. You don’t need here separate workflows to send a task to three separate people – you need one workflow that asks which person should receive the task.
- Hide your workflow lookup columns from most list views. (Not always practical.)
- Divide your workflows among different sites and lists, if your process can logically support it.
- Use Event Receivers for server side processing instead of SharePoint Designer workflows.
Good luck!