Large SQL Queries From Sharepoint Causing Performance Issues

Question

Here's my problem. All of the pages on our Sharepoint 2007 sites get rendered by web parts. The web parts pull data from lists and display them on the page. In the last couple weeks, performance has suffered, to the point where a single page might take a minute and a half to load. In examining the logs, it appears that the queries to the SQL database are taking around 3 to 5 seconds each, and the actual queries are huge. The lists that contain the data have anywhere from 100 to 2000 list items. I know 2000 is a lot, but we've never had this bad of performance in the past. I'm going to post an example of one SQL query, and brace yourselves, it's pretty bad. Notice the excessive number of JOIN statements; I counted 34 in this one:

        EXEC Sp_executesql
N' SELECT t16.*, t18.[MetaInfo] AS c30, t17.[ntext7], t17.[ntext9], t17.[ntext3], t17.[ntext1], t17.[ntext4], t17.[ntext6], t17.[ntext8], t17.[ntext2], t17.[ntext5], t17.[ntext10] FROM (SELECT DISTINCT t14.*, t15.[tp_Ordinal] , t2.[tp_ID] AS c4, t3.[tp_ID] AS c6, t4.[tp_ID] AS c7, t6.[tp_ID] AS c11, t7.[tp_ID] AS c12, t8.[tp_ID] AS c13, t9.[tp_ID] AS c14, t10.[tp_ID] AS c15, t24.[nvarchar1] AS c13c10, t22.[nvarchar1] AS c11c10, t19.[nvarchar1] AS c4c5, t26.[nvarchar1] AS c15c5, t23.[nvarchar1] AS c12c8, t21.[nvarchar1] AS c7c8, t25.[nvarchar1] AS c14c5, t20.[nvarchar1] AS c6c5 FROM (    SELECT TOP 2147483648 t1.[Type] AS c0,UserDataVersioned.[tp_ModerationStatus],UserDataVersioned.[nvarchar1],UserDataVersioned.[nvarchar21],UserDataVersioned.[tp_Version],UserDataVersioned.[int2],t13.[nvarchar1] AS c24c19,t1.[TimeLastModified] AS c28,UserDataVersioned.[tp_HasCopyDestinations],UserDataVersioned.[nvarchar12],UserDataVersioned.[int7],UserDataVersioned.[nvarchar17],t12.[nvarchar4] AS c18c21,t13.[nvarchar5] AS c24c22,UserDataVersioned.[tp_WorkflowInstanceID],UserDataVersioned.[tp_Level],UserDataVersioned.[tp_DirName],t1.[ScopeId] AS c3,UserDataVersioned.[nvarchar5],UserDataVersioned.[nvarchar20],UserDataVersioned.[tp_CalculatedVersion],UserDataVersioned.[int1],UserDataVersioned.[tp_Author],UserDataVersioned.[tp_Editor],UserDataVersioned.[tp_UIVersion],UserDataVersioned.[tp_GUID],UserDataVersioned.[tp_ContentTypeId],UserDataVersioned.[nvarchar11],UserDataVersioned.[nvarchar16],UserDataVersioned.[int6],t1.[TimeCreated] AS c26,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 c1,t13.[tp_ID] AS c24c20,UserDataVersioned.[tp_WorkflowVersion],UserDataVersioned.[tp_IsCurrentVersion],UserDataVersioned.[nvarchar4],UserDataVersioned.[nvarchar9],t1.[ProgId] AS c29,UserDataVersioned.[tp_SiteId],UserDataVersioned.[int5],UserDataVersioned.[nvarchar10],UserDataVersioned.[nvarchar15],t12.[nvarchar1] AS c18c19,t12.[nvarchar5] AS c18c22,t13.[tp_Created] AS c24c23,UserDataVersioned.[tp_CopySource],UserDataVersioned.[tp_ContentType],t13.[nvarchar3] AS c24c31,UserDataVersioned.[tp_HasAttachment],UserDataVersioned.[tp_InstanceID],UserDataVersioned.[tp_ItemOrder],UserDataVersioned.[tp_Created],UserDataVersioned.[tp_ID],UserDataVersioned.[nvarchar3],UserDataVersioned.[nvarchar8],UserDataVersioned.[nvarchar23],UserDataVersioned.[tp_LeafName],UserDataVersioned.[int4],t5.[nvarchar1] AS c9c10,UserDataVersioned.[nvarchar14],UserDataVersioned.[tp_UIVersionString],t1.[DirName] AS c27,t1.[Id] AS c2,UserDataVersioned.[tp_DeleteTransactionId],UserDataVersioned.[nvarchar19],UserDataVersioned.[int9],t11.[nvarchar4] AS c16c17,t12.[tp_ID] AS c18c20,t13.[nvarchar4] AS c24c21,UserDataVersioned.[nvarchar2],UserDataVersioned.[nvarchar7],UserDataVersioned.[float1],UserDataVersioned.[nvarchar22],UserDataVersioned.[tp_Modified],UserDataVersioned.[int3],UserDataVersioned.[nvarchar13],UserDataVersioned.[int10],t1.[LeafName] AS c25,UserDataVersioned.[nvarchar18],UserDataVersioned.[int8],t12.[tp_Created] AS c18c23,t12.[nvarchar3] AS c18c31,UserDataVersioned.[nvarchar6] FROM UserDataVersioned INNER JOIN Docs AS t1 WITH(NOLOCK) ON ( 1 = 1  AND UserDataVersioned.[tp_RowOrdinal] = 0 AND t1.SiteId = UserDataVersioned.tp_SiteId AND t1.SiteId = @L2 AND t1.DirName = UserDataVersioned.tp_DirName  AND t1.LeafName = UserDataVersioned.tp_LeafName  AND t1.IsCurrentVersion = 1 AND  (UserDataVersioned.tp_Level = 255 AND t1.LTCheckoutUserId =@IU OR (UserDataVersioned.tp_Level = 1  OR UserDataVersioned.tp_Level = 2 AND (UserDataVersioned.tp_DraftOwnerId = @IU OR t1.ScopeId IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233''))) ) AND (t1.ScopeId=@L3) AND UserDataVersioned.tp_ListId = @L4) LEFT OUTER JOIN (SELECT * FROM UserData AS t5_u WITH(NOLOCK)  INNER JOIN Docs AS t5_d WITH(NOLOCK) ON (t5_d.DoclibRowId = t5_u.tp_ID  AND t5_d.SiteId = t5_u.tp_SiteId AND t5_d.SiteId = @L2 AND t5_d.DirName = t5_u.tp_DirName  AND t5_d.LeafName = t5_u.tp_LeafName  AND t5_d.Level = t5_u.tp_Level  AND  (t5_u.tp_Level = 255 AND t5_d.LTCheckoutUserId =@IU OR (t5_u.tp_Level = 1 AND (t5_u.tp_DraftOwnerId IS NULL OR  (t5_u.tp_DraftOwnerId <>@IU AND t5_d.ScopeId NOT IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233'')))  OR t5_u.tp_Level = 2 AND (t5_u.tp_DraftOwnerId = @IU OR t5_d.ScopeId IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233''))) AND (t5_d.LTCheckoutUserId IS NULL OR t5_d.LTCheckoutUserId <> @IU )) AND (t5_d.ScopeId=@L3))) AS t5 ON (UserDataVersioned.[int4]=t5.[tp_ID] AND UserDataVersioned.[tp_RowOrdinal] = 0 AND t5.[tp_RowOrdinal] = 0 AND t5.tp_ListId = @L4 AND UserDataVersioned.tp_ListId = @L4) LEFT OUTER JOIN (SELECT * FROM UserData AS t11_u WITH(NOLOCK)  INNER JOIN Docs AS t11_d WITH(NOLOCK) ON (t11_d.DoclibRowId = t11_u.tp_ID  AND t11_d.SiteId = t11_u.tp_SiteId AND t11_d.SiteId = @L2 AND t11_d.DirName = t11_u.tp_DirName  AND t11_d.LeafName = t11_u.tp_LeafName  AND t11_d.Level = t11_u.tp_Level  AND  (t11_u.tp_Level = 255 AND t11_d.LTCheckoutUserId =@IU OR (t11_u.tp_Level = 1 AND (t11_u.tp_DraftOwnerId IS NULL OR  (t11_u.tp_DraftOwnerId <>@IU AND t11_d.ScopeId NOT IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233'')))  OR t11_u.tp_Level = 2 AND (t11_u.tp_DraftOwnerId = @IU OR t11_d.ScopeId IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233''))) AND (t11_d.LTCheckoutUserId IS NULL OR t11_d.LTCheckoutUserId <> @IU )) AND (t11_d.ScopeId=@L3))) AS t11 ON (UserDataVersioned.[int10]=t11.[tp_ID] AND UserDataVersioned.[tp_RowOrdinal] = 0 AND t11.[tp_RowOrdinal] = 0 AND t11.tp_ListId = @L4 AND UserDataVersioned.tp_ListId = @L4) LEFT OUTER JOIN AllUserData AS t12 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserDataVersioned.[tp_Author]=t12.[tp_ID] AND UserDataVersioned.[tp_RowOrdinal] = 0 AND t12.[tp_RowOrdinal] = 0 AND ( (t12.tp_IsCurrent = 1) )  AND t12.[tp_CalculatedVersion] = 0  AND t12.[tp_DeleteTransactionId] = 0x  AND t12.tp_ListId = @L5 AND UserDataVersioned.tp_ListId = @L4) LEFT OUTER JOIN AllUserData AS t13 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserDataVersioned.[tp_Editor]=t13.[tp_ID] AND UserDataVersioned.[tp_RowOrdinal] = 0 AND t13.[tp_RowOrdinal] = 0 AND ( (t13.tp_IsCurrent = 1) )  AND t13.[tp_CalculatedVersion] = 0  AND t13.[tp_DeleteTransactionId] = 0x  AND t13.tp_ListId = @L5 AND UserDataVersioned.tp_ListId = @L4) WHERE (UserDataVersioned.[tp_ID] = @I6P) AND t1.SiteId=@L2 AND (t1.DirName=@DN OR t1.DirName LIKE @DNEL+N''/%'') ORDER BY UserDataVersioned.[tp_UIVersion] Desc,UserDataVersioned.[tp_ID] Asc ) AS t14 LEFT OUTER JOIN UserDataJunctionsVersioned AS t15 ON t14.[tp_SiteId] = t15.[tp_SiteId] AND t14.[tp_DeleteTransactionId] = t15.[tp_DeleteTransactionId] AND t14.[tp_IsCurrentVersion] = t15.[tp_IsCurrentVersion] AND t14.[tp_DirName] = t15.[tp_DirName] AND t14.[tp_LeafName] = t15.[tp_LeafName] AND t14.[tp_CalculatedVersion] = t15.[tp_CalculatedVersion] AND t14.[tp_Level] = t15.[tp_Level] LEFT OUTER JOIN UserDataJunctionsVersioned AS t2 ON t14.[tp_SiteId] = t2.[tp_SiteId] AND t14.[tp_DeleteTransactionId] = t2.[tp_DeleteTransactionId] AND t14.[tp_IsCurrentVersion] = t2.[tp_IsCurrentVersion] AND t14.[tp_DirName] = t2.[tp_DirName] AND t14.[tp_LeafName] = t2.[tp_LeafName] AND t14.[tp_CalculatedVersion] = t2.[tp_CalculatedVersion] AND t14.[tp_Level] = t2.[tp_Level] AND t15.[tp_Ordinal] = t2.[tp_Ordinal] AND t2.[tp_FieldId] = ''{2e286cc1-f309-4b61-9a9e-efd7bfa5fd61}''  LEFT OUTER JOIN UserDataJunctionsVersioned AS t3 ON t14.[tp_SiteId] = t3.[tp_SiteId] AND t14.[tp_DeleteTransactionId] = t3.[tp_DeleteTransactionId] AND t14.[tp_IsCurrentVersion] = t3.[tp_IsCurrentVersion] AND t14.[tp_DirName] = t3.[tp_DirName] AND t14.[tp_LeafName] = t3.[tp_LeafName] AND t14.[tp_CalculatedVersion] = t3.[tp_CalculatedVersion] AND t14.[tp_Level] = t3.[tp_Level] AND t15.[tp_Ordinal] = t3.[tp_Ordinal] AND t3.[tp_FieldId] = ''{48ce2774-348c-46fc-8863-b050f78d9fe6}''  LEFT OUTER JOIN UserDataJunctionsVersioned AS t4 ON t14.[tp_SiteId] = t4.[tp_SiteId] AND t14.[tp_DeleteTransactionId] = t4.[tp_DeleteTransactionId] AND t14.[tp_IsCurrentVersion] = t4.[tp_IsCurrentVersion] AND t14.[tp_DirName] = t4.[tp_DirName] AND t14.[tp_LeafName] = t4.[tp_LeafName] AND t14.[tp_CalculatedVersion] = t4.[tp_CalculatedVersion] AND t14.[tp_Level] = t4.[tp_Level] AND t15.[tp_Ordinal] = t4.[tp_Ordinal] AND t4.[tp_FieldId] = ''{e2f67328-beae-443a-91e6-34278ef58179}''  LEFT OUTER JOIN UserDataJunctionsVersioned AS t6 ON t14.[tp_SiteId] = t6.[tp_SiteId] AND t14.[tp_DeleteTransactionId] = t6.[tp_DeleteTransactionId] AND t14.[tp_IsCurrentVersion] = t6.[tp_IsCurrentVersion] AND t14.[tp_DirName] = t6.[tp_DirName] AND t14.[tp_LeafName] = t6.[tp_LeafName] AND t14.[tp_CalculatedVersion] = t6.[tp_CalculatedVersion] AND t14.[tp_Level] = t6.[tp_Level] AND t15.[tp_Ordinal] = t6.[tp_Ordinal] AND t6.[tp_FieldId] = ''{0e241992-fcea-4385-9dde-1bfd15602f5b}''  LEFT OUTER JOIN UserDataJunctionsVersioned AS t7 ON t14.[tp_SiteId] = t7.[tp_SiteId] AND t14.[tp_DeleteTransactionId] = t7.[tp_DeleteTransactionId] AND t14.[tp_IsCurrentVersion] = t7.[tp_IsCurrentVersion] AND t14.[tp_DirName] = t7.[tp_DirName] AND t14.[tp_LeafName] = t7.[tp_LeafName] AND t14.[tp_CalculatedVersion] = t7.[tp_CalculatedVersion] AND t14.[tp_Level] = t7.[tp_Level] AND t15.[tp_Ordinal] = t7.[tp_Ordinal] AND t7.[tp_FieldId] = ''{67e5ed65-a2e4-4522-918f-cce886fdda5c}''  LEFT OUTER JOIN UserDataJunctionsVersioned AS t8 ON t14.[tp_SiteId] = t8.[tp_SiteId] AND t14.[tp_DeleteTransactionId] = t8.[tp_DeleteTransactionId] AND t14.[tp_IsCurrentVersion] = t8.[tp_IsCurrentVersion] AND t14.[tp_DirName] = t8.[tp_DirName] AND t14.[tp_LeafName] = t8.[tp_LeafName] AND t14.[tp_CalculatedVersion] = t8.[tp_CalculatedVersion] AND t14.[tp_Level] = t8.[tp_Level] AND t15.[tp_Ordinal] = t8.[tp_Ordinal] AND t8.[tp_FieldId] = ''{cec57d0f-6e5b-4e08-bafe-c8759ba0c8b2}''  LEFT OUTER JOIN UserDataJunctionsVersioned AS t9 ON t14.[tp_SiteId] = t9.[tp_SiteId] AND t14.[tp_DeleteTransactionId] = t9.[tp_DeleteTransactionId] AND t14.[tp_IsCurrentVersion] = t9.[tp_IsCurrentVersion] AND t14.[tp_DirName] = t9.[tp_DirName] AND t14.[tp_LeafName] = t9.[tp_LeafName] AND t14.[tp_CalculatedVersion] = t9.[tp_CalculatedVersion] AND t14.[tp_Level] = t9.[tp_Level] AND t15.[tp_Ordinal] = t9.[tp_Ordinal] AND t9.[tp_FieldId] = ''{6cd2e861-f434-4b25-ad8f-b0930311759d}''  LEFT OUTER JOIN UserDataJunctionsVersioned AS t10 ON t14.[tp_SiteId] = t10.[tp_SiteId] AND t14.[tp_DeleteTransactionId] = t10.[tp_DeleteTransactionId] AND t14.[tp_IsCurrentVersion] = t10.[tp_IsCurrentVersion] AND t14.[tp_DirName] = t10.[tp_DirName] AND t14.[tp_LeafName] = t10.[tp_LeafName] AND t14.[tp_CalculatedVersion] = t10.[tp_CalculatedVersion] AND t14.[tp_Level] = t10.[tp_Level] AND t15.[tp_Ordinal] = t10.[tp_Ordinal] AND t10.[tp_FieldId] = ''{b743b55b-d144-4940-b25a-601ff3615587}''  LEFT OUTER JOIN (SELECT * FROM UserData AS t19_u WITH(NOLOCK)  INNER JOIN Docs AS t19_d WITH(NOLOCK) ON (t19_d.DoclibRowId = t19_u.tp_ID  AND t19_d.SiteId = t19_u.tp_SiteId AND t19_d.SiteId = @L2 AND t19_d.DirName = t19_u.tp_DirName  AND t19_d.LeafName = t19_u.tp_LeafName  AND t19_d.Level = t19_u.tp_Level  AND  (t19_u.tp_Level = 255 AND t19_d.LTCheckoutUserId =@IU OR (t19_u.tp_Level = 1 AND (t19_u.tp_DraftOwnerId IS NULL OR  (t19_u.tp_DraftOwnerId <>@IU AND t19_d.ScopeId NOT IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233'')))  OR t19_u.tp_Level = 2 AND (t19_u.tp_DraftOwnerId = @IU OR t19_d.ScopeId IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233''))) AND (t19_d.LTCheckoutUserId IS NULL OR t19_d.LTCheckoutUserId <> @IU )) AND (t19_d.ScopeId IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233'')))) AS t19 ON (t19.[tp_ListId] = @L7 AND t19.[tp_Id] = t2.[tp_ID] AND t19.[tp_RowOrdinal] = 0) LEFT OUTER JOIN (SELECT * FROM UserData AS t20_u WITH(NOLOCK)  INNER JOIN Docs AS t20_d WITH(NOLOCK) ON (t20_d.DoclibRowId = t20_u.tp_ID  AND t20_d.SiteId = t20_u.tp_SiteId AND t20_d.SiteId = @L2 AND t20_d.DirName = t20_u.tp_DirName  AND t20_d.LeafName = t20_u.tp_LeafName  AND t20_d.Level = t20_u.tp_Level  AND  (t20_u.tp_Level = 255 AND t20_d.LTCheckoutUserId =@IU OR (t20_u.tp_Level = 1 AND (t20_u.tp_DraftOwnerId IS NULL OR  (t20_u.tp_DraftOwnerId <>@IU AND t20_d.ScopeId NOT IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233'')))  OR t20_u.tp_Level = 2 AND (t20_u.tp_DraftOwnerId = @IU OR t20_d.ScopeId IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233''))) AND (t20_d.LTCheckoutUserId IS NULL OR t20_d.LTCheckoutUserId <> @IU )) AND (t20_d.ScopeId IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233'')))) AS t20 ON (t20.[tp_ListId] = @L7 AND t20.[tp_Id] = t3.[tp_ID] AND t20.[tp_RowOrdinal] = 0) LEFT OUTER JOIN (SELECT * FROM UserData AS t21_u WITH(NOLOCK)  INNER JOIN Docs AS t21_d WITH(NOLOCK) ON (t21_d.DoclibRowId = t21_u.tp_ID  AND t21_d.SiteId = t21_u.tp_SiteId AND t21_d.SiteId = @L2 AND t21_d.DirName = t21_u.tp_DirName  AND t21_d.LeafName = t21_u.tp_LeafName  AND t21_d.Level = t21_u.tp_Level  AND  (t21_u.tp_Level = 255 AND t21_d.LTCheckoutUserId =@IU OR (t21_u.tp_Level = 1 AND (t21_u.tp_DraftOwnerId IS NULL OR  (t21_u.tp_DraftOwnerId <>@IU AND t21_d.ScopeId NOT IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233'')))  OR t21_u.tp_Level = 2 AND (t21_u.tp_DraftOwnerId = @IU OR t21_d.ScopeId IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233''))) AND (t21_d.LTCheckoutUserId IS NULL OR t21_d.LTCheckoutUserId <> @IU )) AND (t21_d.ScopeId IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233'')))) AS t21 ON (t21.[tp_ListId] = @L8 AND t21.[tp_Id] = t4.[tp_ID] AND t21.[tp_RowOrdinal] = 0) LEFT OUTER JOIN (SELECT * FROM UserData AS  t22_u WITH(NOLOCK)  INNER JOIN Docs AS t22_d WITH(NOLOCK) ON (t22_d.DoclibRowId = t22_u.tp_ID  AND t22_d.SiteId = t22_u.tp_SiteId AND t22_d.SiteId = @L2 AND t22_d.DirName = t22_u.tp_DirName  AND t22_d.LeafName = t22_u.tp_LeafName  AND t22_d.Level = t22_u.tp_Level  AND   (t22_u.tp_Level = 255 AND t22_d.LTCheckoutUserId =@IU OR (t22_u.tp_Level = 1 AND (t22_u.tp_DraftOwnerId IS NULL OR  (t22_u.tp_DraftOwnerId <>@IU AND t22_d.ScopeId NOT IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233'')))  OR t22_u.tp_Level = 2 AND (t22_u.tp_DraftOwnerId = @IU  OR t22_d.ScopeId IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233''))) AND (t22_d.LTCheckoutUserId IS NULL OR t22_d.LTCheckoutUserId <> @IU )) AND (t22_d.ScopeId=@L3))) AS t22 ON (t22.[tp_ListId] = @L4 AND t22.[tp_Id] = t6.[tp_ID] AND t22.[tp_RowOrdinal] = 0) LEFT OUTER JOIN  (SELECT * FROM UserData AS t23_u WITH(NOLOCK)  INNER JOIN Docs AS t23_d WITH(NOLOCK) ON (t23_d.DoclibRowId = t23_u.tp_ID  AND t23_d.SiteId = t23_u.tp_SiteId AND t23_d.SiteId = @L2 AND t23_d.DirName = t23_u.tp_DirName  AND t23_d.LeafName = t23_u.tp_LeafName  AND  t23_d.Level = t23_u.tp_Level  AND  (t23_u.tp_Level = 255 AND t23_d.LTCheckoutUserId =@IU OR (t23_u.tp_Level = 1 AND (t23_u.tp_DraftOwnerId IS NULL OR  (t23_u.tp_DraftOwnerId <>@IU AND t23_d.ScopeId NOT IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233'')))  OR t23_u.tp_Level =  2 AND (t23_u.tp_DraftOwnerId = @IU OR t23_d.ScopeId IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233''))) AND (t23_d.LTCheckoutUserId IS NULL OR t23_d.LTCheckoutUserId <> @IU )) AND (t23_d.ScopeId IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233'')))) AS t23 ON (t23.[tp_ListId] =  @L8 AND t23.[tp_Id] = t7.[tp_ID] AND t23.[tp_RowOrdinal] = 0) LEFT OUTER JOIN (SELECT * FROM UserData AS t24_u WITH(NOLOCK)  INNER JOIN Docs AS t24_d WITH(NOLOCK) ON (t24_d.DoclibRowId = t24_u.tp_ID  AND t24_d.SiteId = t24_u.tp_SiteId AND t24_d.SiteId = @L2 AND  t24_d.DirName = t24_u.tp_DirName  AND t24_d.LeafName = t24_u.tp_LeafName  AND t24_d.Level = t24_u.tp_Level  AND  (t24_u.tp_Level = 255 AND t24_d.LTCheckoutUserId =@IU OR (t24_u.tp_Level = 1 AND (t24_u.tp_DraftOwnerId IS NULL OR  (t24_u.tp_DraftOwnerId <>@IU AND  t24_d.ScopeId NOT IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233'')))  OR t24_u.tp_Level = 2 AND (t24_u.tp_DraftOwnerId = @IU OR t24_d.ScopeId IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233''))) AND (t24_d.LTCheckoutUserId IS NULL OR t24_d.LTCheckoutUserId <> @IU )) AND  (t24_d.ScopeId=@L3))) AS t24 ON (t24.[tp_ListId] = @L4 AND t24.[tp_Id] = t8.[tp_ID] AND t24.[tp_RowOrdinal] = 0) LEFT OUTER JOIN (SELECT * FROM UserData AS t25_u WITH(NOLOCK)  INNER JOIN Docs AS t25_d WITH(NOLOCK) ON (t25_d.DoclibRowId = t25_u.tp_ID  AND t25_d.SiteId =  t25_u.tp_SiteId AND t25_d.SiteId = @L2 AND t25_d.DirName = t25_u.tp_DirName  AND t25_d.LeafName = t25_u.tp_LeafName  AND t25_d.Level = t25_u.tp_Level  AND  (t25_u.tp_Level = 255 AND t25_d.LTCheckoutUserId =@IU OR (t25_u.tp_Level = 1 AND (t25_u.tp_DraftOwnerId IS NULL  OR  (t25_u.tp_DraftOwnerId <>@IU AND t25_d.ScopeId NOT IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233'')))  OR t25_u.tp_Level = 2 AND (t25_u.tp_DraftOwnerId = @IU OR t25_d.ScopeId IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233''))) AND (t25_d.LTCheckoutUserId IS NULL OR  t25_d.LTCheckoutUserId <> @IU )) AND (t25_d.ScopeId IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233'')))) AS t25 ON (t25.[tp_ListId] = @L7 AND t25.[tp_Id] = t9.[tp_ID] AND t25.[tp_RowOrdinal] = 0) LEFT OUTER JOIN (SELECT * FROM UserData AS t26_u WITH(NOLOCK)  INNER JOIN Docs  AS t26_d WITH(NOLOCK) ON (t26_d.DoclibRowId = t26_u.tp_ID  AND t26_d.SiteId = t26_u.tp_SiteId AND t26_d.SiteId = @L2 AND t26_d.DirName = t26_u.tp_DirName  AND t26_d.LeafName = t26_u.tp_LeafName  AND t26_d.Level = t26_u.tp_Level  AND  (t26_u.tp_Level = 255 AND  t26_d.LTCheckoutUserId =@IU OR (t26_u.tp_Level = 1 AND (t26_u.tp_DraftOwnerId IS NULL OR  (t26_u.tp_DraftOwnerId <>@IU AND t26_d.ScopeId NOT IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233'')))  OR t26_u.tp_Level = 2 AND (t26_u.tp_DraftOwnerId = @IU OR t26_d.ScopeId IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233''))) AND (t26_d.LTCheckoutUserId IS NULL OR t26_d.LTCheckoutUserId <> @IU )) AND (t26_d.ScopeId IN (''028D254C-1C71-481D-A62D-5F1C2B1F3233'')))) AS t26 ON (t26.[tp_ListId] = @L7 AND t26.[tp_Id] = t10.[tp_ID] AND t26.[tp_RowOrdinal] = 0) ) AS t16 LEFT OUTER JOIN Docs AS t18 WITH(NOLOCK) ON t18.[SiteId] = t16.[tp_SiteId] AND t18.[DeleteTransactionId] = t16.[tp_DeleteTransactionId] AND t18.[DirName] = t16.[tp_DirName] AND t18.[LeafName] = t16.[tp_LeafName] AND t18.[Level] = t16.[tp_Level]  LEFT OUTER JOIN UserDataVersioned AS t17 WITH(NOLOCK) ON t16.[tp_SiteId] = t17.[tp_SiteId] AND t16.[tp_DeleteTransactionId] = t17.[tp_DeleteTransactionId] AND t16.[tp_IsCurrentVersion] = t17.[tp_IsCurrentVersion] AND t16.[tp_DirName] = t17.[tp_DirName] AND t16.[tp_LeafName] = t17.[tp_LeafName] AND t16.[tp_CalculatedVersion] = t17.[tp_CalculatedVersion] AND t16.[tp_Level] = t17.[tp_Level] AND t17.[tp_RowOrdinal] =  0 ORDER BY t16.tp_UIVersion Desc,t16.tp_ID Asc,t16.[tp_Ordinal]'
,
N'@L0 uniqueidentifier,@L2 uniqueidentifier,@IU int,@L3 uniqueidentifier,@L4 uniqueidentifier,@L5 uniqueidentifier,@I6P int,@DN nvarchar(260),@DNEL nvarchar(1024),@L7 uniqueidentifier,@L8 uniqueidentifier'
,
@L0='00000000-0000-0000-0000-000000000000',
@L2='BC921A53-1407-4D3F-A3F4-FFCA8E38191D',
@IU=1073741823,
@L3='028D254C-1C71-481D-A62D-5F1C2B1F3233',
@L4='6B95520B-F7FD-4378-A28D-35153C24329D',
@L5='3F05AD46-3239-4F59-B9FC-577C4916869D',
@I6P=6371,
@DN=N'Lists/Product Information',
@DNEL=N'Lists/Product Information',
@L7='C2C4D95F-5156-48D5-82BE-D171F015D499',
@L8='A5C3BB1F-7849-4AAB-B1F0-489F6BBC984B'

Large SQL Queries From Sharepoint Causing Performance Issues

Answers (1)

Related Questions