we have an issue with a client where uploading a document to Sharepoint 2007 via the API/Web Service is taking in excess of a minute or more. we have narrowed it down to, what we think, is this SQL Stored Procedure call during the Upload process:
exec sp_executesql N' SELECT ScopeId, Acl, AnonymousPermMask FROM Perms WITH (NOLOCK) WHERE SiteId = @L5 AND ScopeId IN (SELECT tp_ScopeId FROM Lists WHERE tp_WebId = @L6 AND tp_ID = @L2 UNION SELECT DISTINCT t1.ScopeId FROM UserData INNER JOIN Docs AS t1 WITH(NOLOCK) ON (UserData.[tp_ID]=t1.[DoclibRowId] AND UserData.[tp_RowOrdinal] = 0 AND UserData.tp_ListId = @L2) 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 = @L3 AND UserData.tp_ListId = @L2) 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 = @L3 AND UserData.tp_ListId = @L2) WHERE (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=@L5 AND (UserData.tp_DirName=@DN OR UserData.tp_DirName LIKE @DNEL+N''/%'') AND UserData.tp_RowOrdinal=0 AND ((CASE WHEN DATALENGTH(t1.DirName) = 0 THEN t1.LeafName WHEN DATALENGTH(t1.LeafName) = 0 THEN t1.DirName ELSE t1.DirName + N''/'' + t1.LeafName END LIKE @L4TXP) AND t1.SiteId=@L5 AND (t1.DirName=@DN OR t1.DirName LIKE @DNEL+N''/%'') AND t1.Type=0) ) '
it is a single content list site, no choice at first as wasn't set up by us, with a massive amount of docs. when we upload through a test environ we do not see this query getting called, nor on our own inhouse testing server.
any help on what this is doing and how to get it to not make the call or optimize our calls to work with it a bit better?