Large datasets with Business Connectivity Services

I have an interesting problem concerning business connectivity services (BCS) in SharePoint 2010. I am new to the BCS so maybe I'm just not thinking about this problem in the right way and could use some tips.

Every time I google BCS, I see examples of importing data where the the dataset might contain MAYBE 10 records. This is all easily done. The dataset that I want to import is 400,000 records. Turning that into an external ist is choking the system. I bumped up the throttle limit, so really it's takking forever to load into a list.

Basically I want have a chart web part and load in external data into it. It seems that 开发者_开发问答you can't write a standard SQL query against it, so you have to filter it. So I thought to use a BCS Filter to filter that data instead of a query. But even then, filtering among 400k records (or even 150k records) is timing it out.

Am I just thinking about the problem in the wrong way? Thanks!

No you aren't, from my experience BCS is actually amazingly slow when it comes to handling large datasets.

First i'd try to reduce the amount of fields/type descriptors in your model. If you have like 50+ fields with all different data types this can be a show-stopper.

BCS is loading all data into the external list when you request it, not only once, and cache's it on the client, so you could try increasing the caching time. Another idea would be implementing server-side paging in your BCS service.

I'm actually pretty disappointed by BCS and i can't really see a "real world" usecase for it when you hit the limits that fast.