Handling OData Inline Count

The OData API allows users to ask an OData service to include in the response to a query the number of entities that the query result will contain.  This may be different than the number of entities in the current query response if the result of the query is broken up in pages.  It can be expensive for the OData service to calculate the number of rows in a result.

The simple method for determining the count is for the service to fetch and construct all of the entities in the result and count the number of resulting entities.  This means that all of the pages of the result must be constructed before the first page of the result can be returned to the user.  This works well if the result is relatively small.  However if the result is large, the response time to get the first page can be large.

Another option is to have the OData service ask the data source how many rows will be in a result.  For relational SQL data sources this is typically done using a count (*) query in addition to the query to return that actual data requested.  The advantage of this is that the count is obtained up front.  This allows the service to return the first page of results as soon as it is ready and continue to fetch and build subsequent response pages in the background.  This can significantly improve the response time for the first and often subsequent result pages if the result is large.  The drawback to this approach is that it requires an additional query to be executed on the data source.  If the results are relatively small it can take more time to execute this additional count query than it does to just fetch all the entities and count them.  Also if the data being query is frequently changing, the count returned will be approximate because the data may have changed between when the count query was executed and the actual query to fetch the data was executed.

Salesforce Lightning Connect and Inline Count.

Salesforce Lightning Connect always includes an inline count request in the OData query it generates when accessing external data sources.  This means that the size of the external data being fetched and the approach for how inline count is processed can affect the performance of Lightning connect queries. 

DataDirect Cloud Inline Count Mode option

Given that Salesforce Lightning Connect always requests an inline count and there is no one best approach for handling an inline count request in an OData query, DataDirect Cloud now provides a data source option to allow the user to control how inline count is handled.  As of the Mar 14, 2015 update for DataDirect Cloud the OData tab for all data sources that support OData has an option named Inline Count Mode.  This option can have one of the following values

1 – Execute a separate count query to get the number of entities to be returned up front, thus allowing the first page of results to be returned as soon as it is built  

2 -   Fetch and construct all of the entities in the result before returning the first result.

Guidance for Setting Inline Count Mode

There are no hard and fast rules for setting the Inline Count Mode, but the following are some general guidelines.

  • If the data source is a relational data source (Oracle, Microsoft SQL Server, DB2, PostgreSQL, MySQL, Progress Open Edge, Informix and Sybase) then set Inline Count Mode to 2 if the results returned fit within a page or two.  The default page size for DataDirect Cloud is 2000 entities per page. 

 

  • If the data source is a relational data source and the results are larger than a page or two, set Inline count mode to 1 (this is the default for relational data sources).

 

  • If the data source is not a relational data source leave the Inline Count Mode set to 2 (this is the default for the non-relational data sources).  The non-relational data sources typically do not have an efficient query or api for getting the count of results so having the service fetch all the entities and count them is typically more efficient.

 

NOTE these are just guidelines.  You should try both settings in your environment to determine what works best.

Sample Results

In a test done with a result that return a small number of entities (between 1 and 20) against a relational database the data below shows that setting inline count to 2 in this case more than doubles the performance of the queries.  Bigger is better in these results.

  Inline Count Mode

 Requests per second

 1 – Separate count query

 2957.33

 2 – Fetch all entities and count them

 6289.67

 

Again, the performance difference in your environment will vary.