Support for $count in the Domain Api (2020.1)

We are pleased to announce, that starting with v.2020.1, the Domain Api will support the $count method. It is implemented in a very efficient manner and can be used for building beautiful dashboards in the client applications.

Documentation

The developer documentation is updated with examples:

https://docs.erp.net/dev/domain-api-example-queries-25362477.html

 

Example

Specifically, you can use the $count method in the following way. For example, to request the count of invoices on 2020-03-23, use the following query:

~/Crm_Invoicing_Invoices/$count?$filter=DocumentDate eq 2020-03-23T00:00:00Z

This request returns the number of invoices for the specified date.

 

OData Documentation

For more information about the OData method, see:

http://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#_Toc31361043

 

Note For Index Usage

Extracting count in the database might be indexed or non-indexed operation, depending on $filter. If the data, matching the filter, can be extracted using indexes, the $count operation might be extremely efficient. However, if matching the filter cannot be performed with indexes, the whole operation might turn into full table scan. This can have many negative performance effects, including the eviction of the database cache.

 

Note For Index Selectivity

Even if there is index, matching the filter, full table scan might still occur. If the query selects more than 3% of the data, full table scan might be preferred by the database engine.

This applies to all relational database engines. For more information, search Internet about "index selectivity". To avoid this, select fewer data rows.

For example, the query:

~/Crm_Invoicing_Invoices/$count?$filter=DocumentDate eq 2020-03-23T00:00:00Z

might use the index on DocumentDate, because the rows for one day are probably less than 3% of the total rows. However, the query:

~/Crm_Invoicing_Invoices/$count?$filter=DocumentDate gt 2010-03-23T00:00:00Z

will most probably revert to full table scan, because more than 3% of the data rows are selected.