Skip to main content

Performance considerations with DirectQuery

Introduction​

When using DirectQuery, the performance of the queries can be impacted by several factors.

N-B: do not forget that for queries fired once the application is started, in-memory aggregate providers can be used to serve queries and to avoid any computation.

End-to-end query execution time​

To identify bottlenecks and optimize the performance of a DirectQuery application, one should monitor the end-to-end query execution time.

The end-to-end execution time of a query within DirectQuery context is composed of several parts:

  • Query generation on the Atoti side
  • Query execution on the external database
  • Downloading the query result from the external database
  • Processing the query result on the Atoti side

Traces and the logs give insights into the execution time of the different steps.

For the logs, additional metrics are available when the query handling logger (atoti.server.directquery.query_handling) is activated at the FINE level.

Bottleneck on the query execution on the external database​

If the query execution on the external database is the bottleneck, the following strategies can be applied:

  • using an aggregate table to precompute the results of the query and store them in the external database
  • using clustering fields to help the database query engine craft optimized query plans
    • it is very beneficial when the clustering field matches the data partitioning of the external database
    • it might sometimes have a negative impact, as it leads to more queries being run on the external database, resulting in contention.

Bottleneck on the downloading of the query result from the external database​

If the downloading of the query result from the external database is the bottleneck, the following strategies can be applied:

  • use clustering fields
    • this enables to download the query result in a multithreaded fashion, as there will be one downloading thread per sub-query
    • as already mentioned, this might lead to contention on the external database.

N-B: if your network bandwidth is the bottleneck, it might not be possible to download the query result faster.

Bottleneck on the processing of the query result on the Atoti side​

If the processing of the query result on the Atoti side is the bottleneck, the following strategies can be applied:

  • use clustering fields

    • this enables to process the query result in a multithreaded fashion, as it will be split by sub-queries
    • as already mentioned, this might lead to contention on the external database.

  • rely on the built-in multithreaded processing of the query result