Early Filter and Cache

When writing KQL (hunting queries or ASIM parsers), optimize for fewer rows and fewer expensive computations:

1) Prefilter early (before parse/branch work)

• Apply vendor/product/event-id filters and any simple parameter filters as close to the source table as possible.
• Avoid “post-filtering” after heavy parsing/union/branching. If a filter can’t be applied globally, it must be applied inside each branch (so you don’t effectively re-filter the same data multiple times).

2) Materialize reused datasets

• If you deduplicate or aggregate the same high-volume table more than once, compute it once and reuse it (e.g., Latest = materialize(…)).

3) Cache expensive expressions

• If you call costly functions repeatedly (e.g., todynamic(collection_arr), multiple split/extract/regex operations), compute once into a let/extend variable, then reference it.
• If a field is equivalent to another derived value, alias instead of recomputing.

4) Keep dedup correct and efficient

• Be careful with arg_max(*)-style wide projections; if dedup requires the whole winning row, ensure the “winner” selection is correct while minimizing unnecessary wide operations.

Example pattern

let LatestAgents = materialize(
    AgentsInfo
    | summarize arg_max(Timestamp, *) by AgentId
    | where LifecycleStatus != "Deleted"
);

let AgentToolNames = LatestAgents
| where array_length(DeclaredTools) > 0
| mv-expand Tool = DeclaredTools
| extend ToolName = tostring(Tool.name)
| where isnotempty(ToolName)
| summarize ToolNames = make_set(ToolName) by AgentId;

LatestAgents
| join kind=leftouter AgentToolNames on AgentId

Checklist

• Can any filter be moved earlier (before parse-kv, mv-apply, lookup/union branches)?
• Is any large subquery scanned twice? If yes, materialize it.
• Are any expensive expressions computed multiple times? If yes, compute once and reuse/alias.