Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I have a list with 20000 items when I'm trying to open it in Datasheet View it shows me 100 records and then alert on the screen.

Cannot connect to the server at this time. You can continue working with this list, but some data may not be available.

That specific list is wrong, I have also one with more than 80000 and works fine.

In Sharepoint logs I can see that error:

Slow Query StackTrace-Managed: at Microsoft.SharePoint.Utilities.SqlSession.OnPostExecuteCommand(SqlCommand command, SqlQueryData monitoringData) at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior, SqlQueryData monitoringData, Boolean retryForDeadLock) at Microsoft.SharePoint.SPSqlClient.ExecuteQueryInternal(Boolean retryfordeadlock) at Microsoft.SharePoint.SPSqlClient.ExecuteQuery(Boolean retryfordeadlock) at Microsoft.SharePoint.Library.SPRequestInternalClass.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPr... 66538fd8-7a89-4740-954e-2bde46dcae0f

In deleveloper dashboard, that query is executing more that 80 seconds:

SqlCommand: 'DECLARE @DocParentIdForRF uniqueidentifier SELECT TOP 1 @DocParentIdForRF = Docs.Id FROM Docs WHERE Docs.SiteId = @SITEID AND Docs.DirName = @FDN AND Docs.LeafName = @FLN;      set nocount on;set ansi_warnings off;declare @aggrTable1 table(AggrNewID# uniqueidentifier, Count# int, GroupCol#0 nvarchar(255) COLLATE Polish_CI_AS); insert into @aggrTable1 select  NEWID() as AggrNewID#,COUNT(0) as Count#, t1.[nvarchar1] COLLATE Polish_CI_AS FROM UserData LEFT OUTER LOOP JOIN AllUserData AS t1 WITH(NOLOCK,INDEX=AllUserData_PK) ON (UserData.[int2]=t1.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t1.[tp_RowOrdinal] = 0) AND ( (t1.tp_Level = 1) ) AND (t1.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t1.[tp_CalculatedVersion] = 0 ) AND (t1.[tp_DeleteTransactionId] = 0x ) AND (t1.tp_ListId = @L1) AND (UserData.tp_ListId = @L2) INNER MERGE JOIN Docs AS t2 WITH(NOLOCK) ON (UserData.[tp_RowOrdinal] = 0) AND (t2.SiteId=UserData.tp_SiteId) AND (t2.SiteId = @SITEID) AND (t2.ParentId = UserData.tp_ParentId) AND (t2.Id = UserData.tp_DocId) AND ( (UserData.tp_Level = 1) ) AND (t2.Level = UserData.tp_Level) AND (t2.IsCurrentVersion = 1) AND (t2.Level = 1 OR t2.Level =  2) WHERE ( (UserData.tp_Level = 1) ) AND (UserData.tp_SiteId=@SITEID AND (UserData.tp_ParentId=@DocParentIdForRF)) AND (UserData.tp_RowOrdinal=0) AND (t2.SiteId=@SITEID AND (t2.ParentId=@DocParentIdForRF)) GROUP BY t1.[nvarchar1] COLLATE Polish_CI_AS;set ansi_warnings on;set nocount off;SELECT t9.[MetaInfo], t7.* FROM (SELECT DISTINCT t3.[tp_ID] AS c11, t4.*, t5.[tp_Ordinal], t6.[nvarchar1] AS c11c12 FROM (SELECT TOP(@NUMROWS) t1.[tp_Created] AS c0c7, t2.[LeafName] AS c9, UserData.[tp_ContentTypeId], CASE WHEN DATALENGTH(t2.DirName) = 0 THEN t2.LeafName WHEN DATALENGTH(t2.LeafName) = 0 THEN t2.DirName ELSE t2.DirName + N'/' + t2.LeafName END  AS c4, UserData.[tp_Version], UserData.[tp_IsCurrentVersion], UserData.[nvarchar4], UserData.[tp_Created], UserData.[tp_HasCopyDestinations], UserData.[tp_ModerationStatus], UserData.[tp_Level], UserData.[tp_SiteId], UserData.[tp_CalculatedVersion], UserData.[nvarchar1], UserData.[nvarchar6], t1.[tp_ID] AS c0c6, t2.[ScopeId] AS c8, t2.[TimeCreated] AS c10, aggrTable#1.Count# AS __Count#1, t2.[SortBehavior] AS c3, t1.[nvarchar1] COLLATE Polish_CI_AS AS c0c1, UserData.[tp_HasAttachment], t2.[Type] AS c2, UserData.[tp_Modified], UserData.[tp_DeleteTransactionId], UserData.[tp_ParentId], UserData.[tp_DocId], UserData.[int2], UserData.[nvarchar3], UserData.[tp_ID], UserData.[tp_CopySource], UserData.[nvarchar5], aggrTable#1.AggrNewID# AS __AggrNewID#1, t2.[Id] AS c5, UserData.[nvarchar2], UserData.[int1] FROM UserData LEFT OUTER LOOP JOIN AllUserData AS t1 WITH(NOLOCK,INDEX=AllUserData_PK) ON (UserData.[int2]=t1.[tp_ID]) AND (UserData.[tp_RowOrdinal] = 0) AND (t1.[tp_RowOrdinal] = 0) AND ( (t1.tp_Level = 1) ) AND (t1.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t1.[tp_CalculatedVersion] = 0 ) AND (t1.[tp_DeleteTransactionId] = 0x ) AND (t1.tp_ListId = @L1) AND (UserData.tp_ListId = @L2) INNER MERGE JOIN Docs AS t2 WITH(NOLOCK) ON (UserData.[tp_RowOrdinal] = 0) AND (t2.SiteId=UserData.tp_SiteId) AND (t2.SiteId = @SITEID) AND (t2.ParentId = UserData.tp_ParentId) AND (t2.Id = UserData.tp_DocId) AND ( (UserData.tp_Level = 1) ) AND (t2.Level = UserData.tp_Level) AND (t2.IsCurrentVersion = 1) AND (t2.Level = 1 OR t2.Level =  2) INNER JOIN @aggrTable1 AS aggrTable#1 ON ((t1.[nvarchar1] IS NULL AND aggrTable#1.GroupCol#0 IS NULL) OR t1.[nvarchar1] COLLATE Polish_CI_AS=aggrTable#1.GroupCol#0) WHERE ( (UserData.tp_Level = 1) ) AND (UserData.tp_SiteId=@SITEID AND (UserData.tp_ParentId=@DocParentIdForRF)) AND (UserData.tp_RowOrdinal=0) AND (t2.SiteId=@SITEID AND (t2.ParentId=@DocParentIdForRF)) ORDER BY t1.[nvarchar1] COLLATE Polish_CI_AS  ASC ,UserData.[tp_ID]  ASC ) AS t4 LEFT OUTER JOIN UserDataJunctions AS t5 WITH(NOLOCK) ON (t4.[tp_SiteId] = t5.[tp_SiteId] AND t4.[tp_DeleteTransactionId] = t5.[tp_DeleteTransactionId] AND t4.[tp_IsCurrentVersion] = t5.[tp_IsCurrentVersion] AND t4.[tp_ParentId] = t5.[tp_ParentId] AND t4.[tp_DocId] = t5.[tp_DocId] AND t4.[tp_CalculatedVersion] = t5.[tp_CalculatedVersion] AND t4.[tp_Level] = t5.[tp_Level]) LEFT OUTER JOIN UserDataJunctions AS t3 WITH(NOLOCK) ON (t4.[tp_SiteId] = t3.[tp_SiteId] AND t4.[tp_DeleteTransactionId] = t3.[tp_DeleteTransactionId] AND t4.[tp_IsCurrentVersion] = t3.[tp_IsCurrentVersion] AND t4.[tp_ParentId] = t3.[tp_ParentId] AND t4.[tp_DocId] = t3.[tp_DocId] AND t4.[tp_CalculatedVersion] = t3.[tp_CalculatedVersion] AND t4.[tp_Level] = t3.[tp_Level]) AND (t5.[tp_Ordinal] = t3.[tp_Ordinal]) AND (t3.[tp_FieldId] = @L7) LEFT OUTER LOOP JOIN AllUserData AS t6 WITH(NOLOCK,INDEX=AllUserData_PK) ON (t6.[tp_ListId] = @L8) AND (t6.[tp_Id] = t3.[tp_ID]) AND (t6.[tp_RowOrdinal] = 0) AND (t6.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND (t6.[tp_CalculatedVersion] = 0 ) AND (t6.[tp_DeleteTransactionId] = 0x ) AND ( (t6.tp_Level = 1) )) AS t7 LEFT OUTER JOIN Docs AS t9 WITH(NOLOCK) ON (t9.[SiteId] = t7.[tp_SiteId] AND t9.[DeleteTransactionId] = t7.[tp_DeleteTransactionId] AND t9.[ParentId] = t7.[tp_ParentId] AND t9.[Id] = t7.[tp_DocId] AND t9.[Level] = t7.[tp_Level] ) ORDER BY t7.c0c1 COLLATE Polish_CI_AS  ASC ,t7.tp_ID  ASC ,t7.[tp_Ordinal]  ASC  OPTION (FORCE ORDER, MAXDOP 1)'
   CommandType: Text CommandTimeout: 0
   Parameter: '@LFFP' Type: UniqueIdentifier Size: 0 Direction: Input Value: '00000000-0000-0000-0000-000000000000'
   Parameter: '@L1' Type: UniqueIdentifier Size: 0 Direction: Input Value: 'a36960d9-aeb5-49af-86e3-04bf61c3c8c0'
   Parameter: '@L2' Type: UniqueIdentifier Size: 0 Direction: Input Value: '451893ed-6f60-4fbe-8248-5412c65d3004'
   Parameter: '@SITEID' Type: UniqueIdentifier Size: 0 Direction: Input Value: '4c52f123-8850-4946-86c1-4235210df404'
   Parameter: '@FDN' Type: NVarChar Size: 4000 Direction: Input Value: 'Lists'
   Parameter: '@FLN' Type: NVarChar Size: 4000 Direction: Input Value: 'Pakiety lekw'
   Parameter: '@NUMROWS' Type: BigInt Size: 0 Direction: Input Value: '101'
   Parameter: '@L7' Type: UniqueIdentifier Size: 0 Direction: Input Value: 'a1950602-1cdd-45bf-8579-cc1882f58a35'
   Parameter: '@L8' Type: UniqueIdentifier Size: 0 Direction: Input Value: '183396a3-4878-4da2-a839-983f6a119977'
   Parameter: '@RequestGuid' Type: UniqueIdentifier Size: 0 Direction: Input Value: '2bff3cea-a473-4461-9fcd-9756e2ad1d32'
share|improve this question
add comment

1 Answer

You shouldn't expect SharePoint to work well opening any view with more that 2000/5000 items (depending on SQL server version).

What may slow it further down is:

  • Lookup columns (including User and Managed Metadata)
  • Many columns of certain type causing SharePoint to use multiple rows in SQL for each item

Many is defined as:

64 Single line of text
32 Multiple Lines of Text
64 Choice
12 Number
12 Currency
8 Date and Time
6 Lookup
16 Yes / No
16 Person or group
32 Hyperlink or picture
8 Calculated
16 Int

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.