I was working on a document library project recently and we hit a bit of a performance problem. A 4 quad core processor, 16GB, SQL Server 2005 box was grinding to a halt.  Investigation showed that this happened when either a folder or a file in the document library was accessed.  The document library consisted of approximately 353,000 documents, split across about 2,000 folders.

After a lot of investigation by a SQL Server expert, it was found that the following CAML query was causing the issue :-

Query><Where><Eq><FieldRef Name="FSObjType"/><Value Type="Integer">1</Value></Eq></Where></Query>

This generated a SQL Query like :-

SELECT TOP 2147483648 t1.[TimeCreated] AS c0,UserData.[ntext2],t4.[tp_ID] AS c35c4,UserData.[nvarchar10],t2.[nvarchar4] AS c33c5,t1.[Id] AS c15,UserData.[tp_ItemOrder],t1.[ParentLeafName] AS c28,t1.[Type] AS c8,t3.[nvarchar5] AS c34c6,UserData.[tp_ModerationStatus],UserData.[nvarchar1],UserData.[nvarchar6],UserData.[tp_Created],t1.[LTCheckoutUserId] AS c23,UserData.[tp_WorkflowInstanceID],UserData.[ntext1],t4.[tp_Created] AS c35c7,t6.[nvarchar1] AS c3,t2.[nvarchar1] AS c33c3,t3.[tp_ID] AS c34c4,UserData.[tp_ID],t1.[ProgId] AS c13,UserData.[nvarchar5],UserData.[bit1],t1.[DirName] AS c21,t1.[CheckinComment] AS c26,UserData.[tp_GUID],t1.[LeafName] AS c1,UserData.[tp_Editor],t1.[IsCheckoutToLocal] AS c12,UserData.[tp_Author],UserData.[tp_UIVersionString],t2.[nvarchar5] AS c33c6,t3.[nvarchar1] AS c34c3,UserData.[nvarchar7],UserData.[nvarchar2],UserData.[tp_ContentType],t4.[nvarchar4] AS c35c5,t1.[TimeLastModified] AS c9,t3.[tp_Created] AS c34c7,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 c11,t1.[ScopeId] AS c16,UserData.[tp_ContentTypeId],t5.[Status1] AS c36c19,t1.[Size] AS c24,UserData.[tp_WorkflowVersion],UserData.[nvarchar4],UserData.[tp_CheckoutUserId],UserData.[tp_Version],UserData.[nvarchar9],UserData.[tp_IsCurrentVersion],t2.[tp_ID] AS c33c4,UserData.[tp_HasCopyDestinations],UserData.[tp_Level],t4.[nvarchar1] AS c35c3,t1.[MetaInfo] AS c14,t1.[Size] AS c22,t1.[ParentVersionString] AS c27,UserData.[tp_Modified],t3.[nvarchar4] AS c34c5,UserData.[nvarchar3],UserData.[nvarchar8],UserData.[tp_UIVersion],t2.[tp_Created] AS c33c7,UserData.[tp_CopySource],t4.[nvarchar5] AS c35c6,UserData.[ntext3],UserData.[tp_InstanceID] FROM UserData INNER MERGE  JOIN Docs AS t1 WITH(NOLOCK) ON ( 1 = 1  AND UserData.[tp_RowOrdinal] = 0 AND t1.SiteId = UserData.tp_SiteId AND t1.SiteId = @L2 AND t1.DirName = UserData.tp_DirName  AND t1.LeafName = UserData.tp_LeafName  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_DraftOwnerId <>@IU AND t1.ScopeId NOT IN (@L3, @L4, @L5, @L6)))  OR UserData.tp_Level = 2 AND (UserData.tp_DraftOwnerId = @IU OR t1.ScopeId IN (@L3, @L4, @L5, @L6))) AND (t1.LTCheckoutUserId IS NULL OR t1.LTCheckoutUserId <> @IU )) AND (1 = 1)) LEFT OUTER JOIN AllUserData AS t2 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserData.[tp_Editor]=t2.[tp_ID] AND UserData.[tp_RowOrdinal] = 0 AND t2.[tp_RowOrdinal] = 0 AND ( (t2.tp_IsCurrent = 1) )  AND t2.[tp_CalculatedVersion] = 0  AND t2.[tp_DeleteTransactionId] = 0x  AND t2.tp_ListId = @L7 AND UserData.tp_ListId = @L8) LEFT OUTER JOIN AllUserData AS t3 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserData.[tp_CheckoutUserId]=t3.[tp_ID] AND UserData.[tp_RowOrdinal] = 0 AND t3.[tp_RowOrdinal] = 0 AND ( (t3.tp_IsCurrent = 1) )  AND t3.[tp_CalculatedVersion] = 0  AND t3.[tp_DeleteTransactionId] = 0x  AND t3.tp_ListId = @L7 AND UserData.tp_ListId = @L8) LEFT OUTER JOIN AllUserData AS t4 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserData.[tp_Author]=t4.[tp_ID] AND UserData.[tp_RowOrdinal] = 0 AND t4.[tp_RowOrdinal] = 0 AND ( (t4.tp_IsCurrent = 1) )  AND t4.[tp_CalculatedVersion] = 0  AND t4.[tp_DeleteTransactionId] = 0x  AND t4.tp_ListId = @L7 AND UserData.tp_ListId = @L8) LEFT OUTER JOIN Workflow AS t5 WITH(NOLOCK) ON ( CAST ( CAST (UserData.[nvarchar10]AS VARBINARY) AS UNIQUEIDENTIFIER)=t5.[ID] AND UserData.[tp_RowOrdinal] = 0 AND t5.SiteId = @L2 AND t5.ListId = UserData.tp_ListId AND t5.ItemId = UserData.tp_Id AND UserData.tp_ListId = @L8) LEFT OUTER JOIN AllUserData AS t6 WITH(NOLOCK, INDEX=AllUserData_PK) ON (t1.[LTCheckoutUserId]=t6.[tp_ID] AND t6.[tp_RowOrdinal] = 0 AND ( (t6.tp_IsCurrent = 1) )  AND t6.[tp_CalculatedVersion] = 0  AND t6.[tp_DeleteTransactionId] = 0x  AND t6.tp_ListId = @L7) WHERE  (UserData.tp_Level= 255 AND UserData.tp_CheckoutUserId = @IU OR  ( UserData.tp_Level  <> 255 AND (UserData.tp_CheckoutUserId IS  NULL  OR UserData.tp_CheckoutUserId <> @IU))) AND UserData.tp_SiteId=@L2 AND (UserData.tp_DirName=@DN OR UserData.tp_DirName LIKE @DNEL+N'/%') AND UserData.tp_RowOrdinal=0 AND ((t1.[Type] = 1) AND t1.SiteId=@L2 AND (t1.DirName=@DN OR t1.DirName LIKE @DNEL+N'/%')) ORDER BY t1.[Type] Desc,UserData.[tp_ID] Asc OPTION (FORCE ORDER)               

This in turn was caused by the following C# statements.

Guid gd = new Guid(Page.Request["loc"].ToString());

SPFolder fol = docLib.Folders[gd].Folder;

It appears that there is a SPListCollection method which is underneath the SPFolder fol = docLib.Folders[gd].Folder; statement that generates the CAML Query that generates the horrendous SQL query.

(Take a bow Mr Stuart Starrs – for finding this method !)

The next problem was to find out how to stop this CAML Query being generated.

The answer was to change the C# code slightly to use the GetItemByUniqueId method of the SPDocumentLibrary object:-

Guid gd = new Guid(Page.Request["loc"].ToString());

SPListItem FolderItem = docLib.GetItemByUniqueId(gd);

SPFolder fol = FolderItem.Folder;

Problem solved.  Application now goes like a dream.