External list with bulk amount of data does not work properly

Question

I have created and external content type to fetch data from a SQL table. The table currently contains 8000 records. So when I create an external list from that content type the list does not display records since it exceeds the throttling limit. If I set limit filter in the BCS(suppose to 100) it fetches the 100 items and displays properly.But my recruitment is to display all the records. How to do this any idea? (Increasing the throttling limit it can be done but the list takes 30 seconds to load and in future table data is going to increase) Please suggest a way to do this.

Answer 1

Seems like you need to implement some form of server side paging. If you're developing this as a .NET Connectivity model, then you can take this approach for paging the data: http://lightningtools.com/blog/archive/2010/06/25/sharepoint-2010-external-list-paging-ndash-server-side.aspx

We have implemented this and it works pretty reliably, although it is not without problems - see comments in the bottom of that article.

If you built this in SPD as a SQL Server model, then you might be able to page using the PageNumber filter descriptor.

Answer 2

SharePoint lists is capable of storing millions of items. To see the lists items, you use a view which is formed by querying the list and hence you get throttle exception.

There are various List Patterns available to solve the problem. Partitioned View is one of them which you can use to manage the single large list.