BDC Performance Over the WAN

Are you centralizing your SharePoint deployment? Do you want to integrate backend data sources located in distant lands with SharePoint using the BDC? Here's something to think about. BDC does no caching of data sources. So what does that mean? If you are displaying a large number of records from a database in Singapore using the BDC on your farm in New York your experience will not be as good as if that data was co-located with the farm. Latency has a major impact on most applications and the BDC is no exception.

Our testing showed that BDC performance has an inverse relationship with latency. Here's a sample of what that looks like. Notice that bandwidth didn't have much of an effect on performance at the low end, but when latency got worse bandwidth helped slightly. This is because your effective throughput is roughly bandwidth / latency.  

Here's something else to think about. Run a trace on a backend data source with at least 250 records while rendering your BDC list web part. Configure your web part with the default values (limit items per view to 20 and don't limit the number of records). Now render your page and view the query. Note the query that was processed. Now do this. Click the arrow to view the next 20 records. Notice anything? That's right, it ran the exact same query with nothing to limit the recordset. Not really a problem if your latency isn't that bad (less than 100ms) and your recordset isn't that large. (less than 1000) However, think about the scenario where your backend data source is already heavily burdened and you need to deploy some BDC web part to a page that has a lot of concurrency such as a portal home page or the mysite template. What will happen if that web part is viewed by 100 users at the same time and it contains a lot of records.

Be smart about deploying the BDC.

1. Make sure your queries return only the data that is needed. Never use a * in a BDC query. Make sure to only return the columns you need to display. Ensure your queries have adequate controls to prevent expensive round trips.

2. Don't deploy BDC web parts on high traffic pages until you have thoroughly tested those pages at scale. If your page(s) see a million hits a day, make sure you push a representative sample or more through your testing suite.

3. Minimize latency between your web parts and data sources. If your testing shows that your BDC web parts take too long to render, consider moving your data source closer. This is much easier than you might think. You can simply log ship, replicate, or even mirror and snapshot the data somewhere closer to your farm.

4. Finally, The BDC is great, but its not perfect for all scenarios. Consider alternatives for integration. Excel may be a great alternative in some situations since it caches. (more on that in the future)