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'
