External Content Type - limitations

Question

This is my scenario: I have an external SQL Server db with about 100.000 items in one table. These items may be grouped into 5 categories. Now the we'd like to add a sharepoint interface on top of this list for a familar editing experience inside SharePoint. There will be about 20 simultaionius users on this list, we're using sp foundation in a minimal configuration.

What of the following options would you suggest:

1. Create one sp list for each category. Now each list contains about 20.000 items, and this would be no problem for sharepoint. We could even let the user use the datasheet view without any problems. Every 15 min we do a sync with the external database using a 3rd party syncing component.

2. We use only 1 list with 5 different views, no problem. Syncing as in option 1

3. We create a external content type and use the bcs feature. This feature works fine, and handles 100.000 items without any problems. We could easily provide filters and also do sorting and paging. And use the datasheet view to edit data. BSC (or is it bcs or bdc - impossible to remember) is a perfect match for this scenario.

Ok, that was a bit exaggerated but hopefully I can get some ideas of wether using external content types is a good fit or not. I find it difficult to figure out the downsides of it.

Thanks for any help

Regards

Larsi

Answer 1

Large List Threshold in 5,000 items for users, and 20,000 for site collection admins. When list hits this threshold you can't query whole list, view filters can be applied only to indexed columns. On paged views there is no difference, on Data Sheet View will fail.

External lists have no Data Sheet View (!), REST interface, RSS. You can't query more than 2,000 entities from external system at single query. For External lists you nedd to carefully design data access methods to work with over 2,000 external entities.

In general 100,000 items is too many. You should consider what operations executed on this dataset, yhan design data access in sharepoint.

Answer 2

Option 3 does seem like a no brainer until you start learning about all the limitations of external lists, such as:

• No datasheet view

• True server side paging is difficult, especially in a .NET connectivity scenario. The paging that you configure on a list will not be server side. Here is an approach to a 'pseudo' server side paging (it always does top N) that might be enough for you (it was for us): http://lightningtools.com/blog/archive/2010/06/25/sharepoint-2010-external-list-paging-ndash-server-side.aspx

Also, gandjustas has a good point about item threshold.