KQL – Materialize query?

I have checked few times the article for materialize() and while it provides good overview of the query, I felt that it could benefit from example that has recorded speed benefit.

My experiences

For just caching a data set, materialize does not increase performance – in fact, if you look at the KQL ”linter”, it displays a very specific recommendation about the operator:

When does it increase performance?

According to my tests, the performance begins when you start to add subqueries to materialized data set. In the example below the materialize function increases performance by about factor of ~1.5x

Example set for Azure AD Sign-in logs

With materialize()

let mass = materialize (union AADNonInteractiveUserSignInLogs, AADServicePrincipalSignInLogs, AADManagedIdentitySignInLogs, SigninLogs
    | where TimeGenerated > now() -90d
    | extend displayName = iff (isempty( AppDisplayName), ServicePrincipalName, AppDisplayName));
let sd=   mass
    | summarize max(TimeGenerated) by displayName
    | join kind=inner (mass | summarize count() by displayName) on displayName
| project-away displayName1;
let subq2 = mass 
| summarize make_set(IPAddress) by displayName;
let subq3 = mass
| summarize make_set(UserType) by displayName;
sd 
| join kind=inner  subq2 on displayName
| join kind=inner  subq3 on displayName
| project-away displayName1, displayName2

without materialize()

//With SignInLogs (requires that signInLogs are stored in the same space)
let mass = union AADNonInteractiveUserSignInLogs, AADServicePrincipalSignInLogs, AADManagedIdentitySignInLogs, SigninLogs
    | where TimeGenerated > now() -90d
    | extend displayName = iff (isempty( AppDisplayName), ServicePrincipalName, AppDisplayName);
let sd=   mass
    | summarize max(TimeGenerated) by displayName
    | join kind=inner (mass | summarize count() by displayName) on displayName
| project-away displayName1;
let subq2 = mass 
| summarize make_set(IPAddress) by displayName;
let subq3 = mass
| summarize make_set(UserType) by displayName;
sd 
| join kind=inner  subq2 on displayName
| join kind=inner  subq3 on displayName
| project-away displayName1, displayName2