We are using a custom BCS connector to crawl the Data from SQL for SharePoint 2013 search. There are 2.1 million records in the SQL table. The data is being read in batches and provided to the crawler (we have implemented our own logic to supply the records to the search crawler in batches).

The issue here is the crawl rate.  The rate of crawl at which the OOB External Content type retrieves the data is way higher than the custom process (2.1 M items in 8.30 hrs.). But when we try to crawl the BCS External Content type using custom BCS connector its takes 10 hrs. (If the load is high or multiple users log into the server sometimes it takes 19 hrs. to crawl 1M items)

At this point of time we are unable to identify the cause of the issue or find the section where the bottle neck is.

Environment:

  • SharePoint 2013 Enterprise with all the Services enabled on the same server.
  • SQL Server 2008 R2 Standard also on the same SharePoint standalone server.

Solution:

When we compared the OOB and Custom BDC model files (.bdcm), we found that there is one additional property called UseClientCachingForSearch was used in the OOB BDC model. Then we started investigating about this property and found that this is the property produce the magical number for OOB BCS.

The UseClientCachingForSearch property in BDC model improves the speed of full crawls by caching the item during enumeration. This property is also recommended when implementing incremental crawls that are based on change logs, because it improves incremental crawl speed.

Basically when we make a model using Out of the box, the UseClientCachingForSearch property is added automatically. When we make our own custom BCS model we have to add this property manually.

Important Note:

If BCS crawl items are larger than 30 kilobytes on average, we should not set this property, as it will lead to a significant number of cache misses and negate performance gains. In our case our records are not more than this limit.

Output

After implementing this we got the magical number same like out of the box BCS crawl in our custom BCS crawl activity. This is just in the development environment. When it goes to production it should give very good performance for 200% sure.

Why Custom BCS Connector?

Custom BCS connector is used here for the following reasons:

  • Out of the box BCS Connector has response limit. The maximum number of rows that can be read through Database Connector is 1,000,000. The limit can be changed via the ‘Set-SPBusinessDataCatalogThrottleConfig’ cmdlet using PowerShell. But it is not recommended. To handle this threshold issue on the search crawler, we are writing the custom BCS connector with the batch processing. (Refer the SharePoint 2013 Software Boundaries)
  • Currently our external system (Teamcenter) has 2.1 million data and same may grow up to 6 million in the next 4 to 5 years. To process this dynamic data growth we have implemented batch processing in our Custom BCS connector.
  • OOB SharePoint incremental crawl will not delete the deleted items in the external system from the search indexes until we do a full crawl. To implement this logic for incremental crawl we choose custom BCS connector.

Vote of Thanks

Regarding this solution approach, I would like to convey my thanks to Subrat Naik who helped us to create the core BCS custom connector. To improve the performance at the code level Sivasankar Sabbani Pillai and Prasad Athalye did a great code review. To improve the crawl and search performance Scot Hillier and Matthew McDermott helped us a lot. Scot & Matthew the MVPs helped us a lot to get this solution work with greater performance in tough testing scenarios.

Last but not least Sreeharsha Alagani the Search Consultant who is working with me in this project. He has a very good knowledge in SharePoint Search Applications.

In this project the researches, findings and learning we have done are going to be Great Knowledge Base for everyone us/you.

Feel free to get in touch with me if you have any queries on Custom BCS connector / SharePoint 2013 Search. I am happy to help you.

Thank you every one!!! Happy SharePointing !!!