External Content Type/External Lists’ performance with large datasets


  • Are External Content Types a viable solution when dealing with large datasets?

    To be a little more precise with my question: within InfoPath forms, I want to do a search on a table found in a LOB system (list of cities) by taking a string that is entered by the user in the form as the input parameter, and return a handful of cities back in the form. Since our list of cities is several hundred thousand records, submitting a query to return a list of 5-10 cities should not request/return all records that are then filtered down to 5-10 cities.

    I know that this can be achieved by building a web service connection in InfoPath (but that’s if we have one): as in, only the 5-10 cities are transmitted over to InfoPath. However, in some cases (different queries on the LOB systems) we might not have a web service to get the information that needs to appear in the form, but we might have a SQL stored procedure that returns the necessary information (and I believe InfoPath cannot connect to a SQL stored procedure, only Views and Tables: is that correct?).

    Another reason for why I don't want to use InfoPath web service connections is because I would prefer to have a connection layer for everything SharePoint-related, so that it would be easier to manage when there are changes to any tables/stored proc/webservice in the LOB systems. Having everything go through External Content Types would be a nice way to achieve this, however I don't want to pull the whole contents of the different tables every time a query is made on that External List/ECT. Without this connection layer, the connections in the InfoPath forms would be a mishmash of SOAP web service, REST webservice, SQL database, and SharePoint lists.




    Monday, October 10, 2011 5:12 PM


All replies