Best practices for KQL queries

• 1: Best practices for Kusto Query Language queries
• 2: Named expressions

1 - Best practices for Kusto Query Language queries

Here are several best practices to follow to make your query run faster.

In short

Reduce the amount of data being processed

A query’s performance depends directly on the amount of data it needs to process. The less data is processed, the quicker the query (and the fewer resources it consumes). Therefore, the most important best-practice is to structure the query in such a way that reduces the amount of data being processed.

In order of importance:

• Only reference tables whose data is needed by the query. For example, when using the union operator with wildcard table references, it’s better from a performance point-of-view to only reference a handful of tables, instead of using a wildcard (*) to reference all tables and then filter data out using a predicate on the source table name.

• Take advantage of a table’s data scope if the query is relevant only for a specific scope. The table() function provides an efficient way to eliminate data by scoping it according to the caching policy (the DataScope parameter).

• Apply the where query operator immediately following table references.

• When using the where query operator, the order in which you place the predicates, whether you use a single where operator, or multiple consecutive where operators, can have a significant effect on the query performance.

• Apply predicates that act upon datetime table columns first. Kusto includes an efficient index on such columns, often completely eliminating whole data shards without needing to access those shards.

• Then apply predicates that act upon string and dynamic columns, especially such predicates that apply at the term-level. Order the predicates by the selectivity. For example, searching for a user ID when there are millions of users is highly selective and usually involves a term search, for which the index is very efficient.

• Then apply predicates that are selective and are based on numeric columns.

• Last, for queries that scan a table column’s data (for example, for predicates such as contains "@!@!", that have no terms and don’t benefit from indexing), order the predicates such that the ones that scan columns with less data are first. Doing so reduces the need to decompress and scan large columns.

Avoid using redundant qualified references

Reference entities such as tables and materialized views by name.

For example, the table T can be referenced as simply T (the unqualified name), or by using a database qualifier (for example, database("DB").T when the table is in a database called DB), or by using a fully qualified name (for example, cluster("<serviceURL>").database("DB").T).

For example, the table T can be referenced as simply T (the unqualified name), or by using a database qualifier (for example, database("DB").T when the table is in a database called DB), or by using a fully qualified name (for example, cluster("X.Y.kusto.windows.net").database("DB").T).

It’s a best practice to avoid using name qualifications when they’re redundant, for the following reasons:

1. Unqualified names are easier to identify (for a human reader) as belonging to the database-in-scope.

2. Referencing database-in-scope entities is always at least as fast, and in some cases much faster, then entities that belong to other databases. This is especially true when those databases are in a different cluster. This is especially true when those databases are in a different Eventhouse. Avoiding qualified names helps the reader to do the right thing.

2 - Named expressions

This article discusses how to optimize repeat use of named expressions in a query.

In Kusto Query Language, you can bind names to complex expressions in several different ways:

• In a let statement
• In the as operator
• In the formal parameters list of user-defined functions

When you reference these named expressions in a query, the following steps occur:

1. The calculation within the named expression is evaluated. This calculation produces either a scalar or tabular value.
2. The named expression is replaced with the calculated value.

If the same bound name is used multiple times, then the underlying calculation will be repeated multiple times. When is this a concern?

• When the calculations consume many resources and are used many times.
• When the calculation is non-deterministic, but the query assumes all invocations to return the same value.

Mitigation

To mitigate these concerns, you can materialize the calculation results in memory during the query. Depending on the way the named calculation is defined, you’ll use different materialization strategies:

Tabular functions

Use the following strategies for tabular functions:

• let statements and function parameters: Use the materialize() function.
• as operator: Set the hint.materialized hint value to true.

For example, the following query uses the non-deterministic tabular sample operator:

Behavior without using the materialize function

range x from 1 to 100 step 1
| sample 1
| as T
| union T

Output

Behavior using the materialize function

range x from 1 to 100 step 1
| sample 1
| as hint.materialized=true T
| union T

Output

Scalar functions

Non-deterministic scalar functions can be forced to calculate exactly once by using toscalar().

For example, the following query uses the non-deterministic function, rand():

let x = () {rand(1000)};
let y = () {toscalar(rand(1000))};
print x, x, y, y

Output

Related content

• Let statement
• as operator
• toscalar()