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:
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.
We use only 1 list with 5 different views, no problem. Syncing as in option 1
- 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