How do I optimize backend synchronization?
Business Connectivity Services (BCS) has a synchronization framework that uses a client cache to enable you to work with external data even when disconnected.
In this post, we’ll discuss ways to optimize solutions to best use the synchronization framework client cache. We’ll be using the Contoso Sales Manager sample solution presented in the previous post, BCS Solution Packaging Tool. Please check that post for instructions on how to package and deploy the solution.
How does the client cache work?
When a solution is deployed to an Office client, a process called BCSSync.exe contacts the external system directly and downloads the data into a per-user SQL CE database managed by the synchronization framework.
Every client will be contacting the external system directly, so it’s really important to optimize your solution for offline use to minimize the load on the external system.
Working with Subscriptions
To determine what data will be brought to the client cache, solutions need to define subscription xml files. Each subscription file is responsible for defining how to request data from the external system.
Subscriptions can have 3 types of items: Queries, Explicit Identities and Associations. Queries point to finders that will be executed against the external system to populate the cache, Explicit Identities are additional instances that are added individually to the subscription, and Associations are used to bring related instances.
Here is the subscription file for Contoso Sales Manager solution defining how to request data for the “Customers” external content type.
<?xml version="1.0" encoding="utf-8"?>
<Subscription LobSystemInstanceName="AdventureWorksContosoLOBInstance"
EntityNamespace="AdventureWorksContoso" EntityName="Customer"
Name="AdventureWorksContosoCustomerSubscription" View="GetCustomerById"
IsCached="true" RefreshIntervalInMinutes="360"
xmlns="https://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">
<Queries>
<Query Name="AdventureWorksContosoCustomerQuery"
MethodInstanceName="GetCustomers"
DefaultDisplayName="Customer Read List"
RefreshIntervalInMinutes="180" IsCached="true" Enabled="true" />
</Queries>
<Associations>
<Association Name="GetOrdersForCustomer"
MethodInstanceName="GetOrdersForCustomer"
TargetSubscriptionName="AdventureWorksContosoOrderHeaderSubscription"
TargetView="GetSalesOrderHeaderById"
LobSystemInstanceName="AdventureWorksContosoLOBInstance"
RefreshIntervalInMinutes="60"/>
</Associations>
</Subscription>
The subscription above has one query, which runs the “GetCustomers” Finder on the external content type. All Customer instances returned by the finder call will be cached locally on every client that has the solution installed. The refresh interval for the query is set to 180 minutes, so every 3 hours the finder will be executed and the local data will be refreshed on each client.
NOTE: When working with the client cache, it’s important to know that it performs best when the Finder view matches the SpecificFinder view. If the Finder returns a subset of the fields that the SpecificFinder returns, the synchronization framework is forced to call the SpecificFinder on every instance returned by the finder to populate all fields of the instance. If it’s not possible for the Finder and the SpecificFinder views to match, bulk stereotypes can be used to improve performance. Bulk stereotypes will be covered on a later post.
Optimizing Subscriptions
Explicit Identities
If you know exactly which items to synchronize, you can just add their identities to the subscription individually. This is a lot easier to do programmatically since you have to put the serialized identity on the subscription XML file.
To get the serialized Identity, you need to instantiate an Identity object with the appropriate identifier values, and call its Serialize method. More information can be found at theIdentity.Serialize method page on MSDN. Once you have the Identity’s serialized string, you just need to add it in an “<Identity>” tag within the “<Identities>” tag, as will be shown later in the example.
Filtering data
The obvious way to optimize synchronization is to reduce the amount of data that is downloaded by the client. The easiest way to do this is by using filters. The filters supported by the synchronization framework that can be used to reduce the number of items downloaded are Wildcard, Comparison and Limit filters.
For example, you can use a Wildcard filter to download all employees whose names start with the letter “M” using “M*” as the filter value. Or you can select all customers that have their zip code equal to “98052” using a comparison filter. A limit filter can be used to limit the number of products to 100.
Example
Here is an example of the subscription above with 11003 and 11020 as explicit identities and a filter to return only customer with CustumerIDs greater than 11050.
<?xml version="1.0" encoding="utf-8"?>
<Subscription LobSystemInstanceName="AdventureWorksContosoLOBInstance"
EntityNamespace="AdventureWorksContoso" EntityName="Customer"
Name="AdventureWorksContosoCustomerSubscription" View="GetCustomerById"
IsCached="true" RefreshIntervalInMinutes="360"
xmlns="https://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">
<Identities>
<Identity>i+yoAAA==</Identity>
<Identity>iDCsAAA==</Identity>
</Identities>
<Queries>
<Query Name="AdventureWorksContosoCustomerQuery"
MethodInstanceName="GetCustomers"
DefaultDisplayName="Customer Read List"
RefreshIntervalInMinutes="180" IsCached="true" Enabled="true">
<FilterValues>
<FilterValue FilterDescriptorName="MinCustomerId" FilterIndex="0"
Type="System.Int32">11050</FilterValue>
</FilterValues>
</Query>
</Queries>
<Associations>
<Association Name="GetOrdersForCustomer"
MethodInstanceName="GetOrdersForCustomer"
TargetSubscriptionName="AdventureWorksContosoOrderHeaderSubscription"
TargetView="GetSalesOrderHeaderById"
LobSystemInstanceName="AdventureWorksContosoLOBInstance"
RefreshIntervalInMinutes="60"/>
</Associations>
</Subscription>
This subscription will only download the specified customers, as you can see in the image below.
The filters used by the subscription file have to be defined on the model, and the external system needs to support them, too. The model for the Contoso Sales Manager solution and the AdventureWorks web service used by the solution were updated to support a comparison filter on the CustomerID field. The updated solution can be found in the package attached to this post.
NOTE: The version number of the Customer external content type was increased on the updated solution. When editing the model manually, this is an important step to make sure our changes are not ignored during deployment.
Recommended reading
To learn more about BCS client cache, navigate to Understanding Business Connectivity Services Client Cache to Optimize Your Solutions.
For more information on how to bring external data to Office client applications go to:
- BCS Team Channel: External Lists in Outlook
- External Lists in SharePoint Workspace
- BCS Team Channel: External Data in SharePoint Workspace.
For more information on subscriptions and subscription files, see What Is a Cache Subscription? .
Is that all?
Limiting the amount of data downloaded by the client is not always an option. For those cases, version field, Bulk Stereotypes and Last Id Seen filter are a great option. They will be covered in an upcoming post.
- Rodrigo Silveira.