The operations executed via BDC can be extremely slow. Just so you know I will talk about a project I had to implement and how an alternative solution can be put in place to maximize the overall performance.
The task was to is to hit a specific database and bring a whole set of records in order to build a report. For many architectural reasons particular to the project, the BDC approach had to be used for the task.
The SharePoint web site makes the request to the BDC, the BDC then drags the data out of the external database and then this data is manipulated and displayed back in the end user report page. The transaction is much like the picture below:
Let's run a profiler against that page and see what it tells about it. (I will blur all text which might contain sensitive information about the client.)
SharePoint's page life cycle and what's going on behind the scenes we can draw a few conclusions and the most important conclusion here is that 2 single calls are responsible for almost 99% of the total execution time. Now that an impressive bottleneck.
Let's dive a bit further on it into the internal calls and we can identify the very single calls responsible for these times.
The world would be perfect if we only could have done things our way, but unfortunately due to requirements restrictions this can not be changed.
As a good exercise I made myself a mirror copy of this environment to test a theory. What if we change the approach to loading data from the BDC?
The idea is to connect to the BDC and only extract the necessary connection string to the external data source and from there I would load the data via ADO.Net. That would be great because :
- we are not performing any breaking changes in the current structure
- the BDC still plays the game
- All the permissions and security levels are still managed by the BDC definition
- I have an opportunity to retrieve data from an external source much faster than via BDC
- 1 and 2 we would get the BDC connection string.
- 3 and 4 we will query the external database and get the data displayed on screen.
Note that the code is also using Entity Framework.
25 public static void SetSharedServiceProvider(SPSite site)
29 if (site != null)
37 catch (Exception)
39 // Ignore the exception if a provider
40 // is already set.
45 public static EntityConnection GetOnePortalConnectionString(SPSite site)
47 const string instanceName = "TEST_Instance";
49 LobSystemInstance instance = ApplicationRegistry.GetLobSystemInstances()[instanceName];
50 var properties = instance.GetProperties();
53 return GetEntityConnection(GetProperty(properties, "CONN Data Source"),
54 GetProperty(properties, "CON Initial Catalog"));
Once implemented the results are impressive. The page that use to load in minutes now takes a couple of seconds to run.
Now moving from the current scenario to the improved version is easier said than done and the lesson learned here is that BDC can be as great as mush as it can be plain dangerous to kill an application.
See you later,
By Edge Pereira