Improving Data Table Metadata Query Performance

Construct performant queries and configure MongoDB to process queries as efficiently as possible.

Data table metadata queries in the DataFrame Service make heavy use of MongoDB. These queries can run slowly or time out for a variety of reasons.

Poorly constructed queries degrade the performance of the DataFrame Service. This degradation can also affect the services that are connected to the same MongoDB instance as the DataFrame Service.

Creating Efficient DataFrame Service Queries

Follow certain guidelines when constructing a performant query for the DataFrame Service.

Guideline Description
Include a clause that adds an equality filter on a high cardinality indexed field inside all query clauses.
Note A high cardinality indexed field contains mostly unique values.

These clauses enable the database to efficiently reduce the query search space to a small number of database entries.

Example clauses include the TestResultId field or any high-cardinality field with a configured index.

Note By default, SystemLink indexes the TestResultId field and Id field.
The clause should also include one of the following filters:
  • A simple equality filter on the high cardinality indexed field.
  • A filter that checks if the field is equal to one of several values.
Order all results by the RowsModifiedAt field.

The indexes created by the DataFrame Service support efficient ordering by RowsModifiedAt. Ordering by other fields requires an expensive in-memory sort in the database.