Query best practices

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

Action Use Don't use Notes
Time filters Use time filters first. Kusto is highly optimized to use time filters.
String operators Use the has operator Don't use contains When looking for full tokens, has works better, since it doesn't look for substrings.
Case-sensitive operators Use == Don't use =~ Use case-sensitive operators when possible.
Use in Don't use in~
Use contains_cs Don't use contains If you can use has/has_cs and not use contains/contains_cs, that's even better.
Searching text Look in a specific column Don't use * * does a full text search across all columns.
Extract fields from dynamic objects across millions of rows Materialize your column at ingestion time if most of your queries extract fields from dynamic objects across millions of rows. This way, you'll only pay once for column extraction.
Lookup for rare keys/values in dynamic objects Use MyTable | where DynamicColumn has "Rare value" | where DynamicColumn.SomeKey == "Rare value" Don't use MyTable | where DynamicColumn.SomeKey == "Rare value" This way, you filter out most records, and do JSON parsing only of the rest.
let statement with a value that you use more than once Use the materialize() function For more information on how to use materialize(), see materialize(). For more information, see Optimize queries that use named expressions.
Apply conversions on more than 1 billion records Reshape your query to reduce the amount of data fed into the conversion. Don't convert large amounts of data if it can be avoided.
New queries Use limit [small number] or count at the end. Running unbound queries over unknown data sets may yield GBs of results to be returned to the client, resulting in a slow response and a busy cluster.
Case-insensitive comparisons Use Col =~ "lowercasestring" Don't use tolower(Col) == "lowercasestring"
Compare data already in lowercase (or uppercase) Col == "lowercasestring" (or Col == "UPPERCASESTRING") Avoid using case insensitive comparisons.
Filtering on columns Filter on a table column. Don't filter on a calculated column.
Use T | where predicate(<expression>) Don't use T | extend _value = <expression> | where predicate(_value)
summarize operator Use the hint.shufflekey=<key> when the group by keys of the summarize operator are with high cardinality. High cardinality is ideally above 1 million.
join operator Select the table with the fewer rows to be the first one (left-most in query).
Use in instead of left semi join for filtering by a single column.
Join across clusters Across clusters, run the query on the "right" side of the join, where most of the data is located.
Join when left side is small and right side is large Use hint.strategy=broadcast Small refers to up to 100,000 records.
Join when both sides are too large Use hint.shufflekey=<key> Use when the join key has high cardinality.
Extract values on column with strings sharing the same format or pattern Use the parse operator Don't use several extract() statements. For example, values like "Time = <time>, ResourceId = <resourceId>, Duration = <duration>, ...."
extract() function Use when parsed strings don't all follow the same format or pattern. Extract the required values by using a REGEX.
materialize() function Push all possible operators that will reduce the materialized data set and still keep the semantics of the query. For example, filters, or project only required columns. For more information, see Optimize queries that use named expressions.
Use materialized views Use materialized views for storing commonly used aggregations. Prefer using the materialized_view() function to query materialized part only materialized_view('MV')