I'm troubleshooting a slow incident query that's timing out. The core issue is a join between `SecurityEvent` and `SigninLogs` on `Account` that's crawling on a 30-day window. The tables are massive, and the join is killing performance. Here's the problematic join pattern:
```kql
SecurityEvent
| where TimeGenerated > ago(30d)
| join kind=inner (
SigninLogs
| where TimeGenerated > ago(30d)
) on $left.Account == $right.UserPrincipalName
| summarize count() by Account, bin(TimeGenerated, 1d)
```
I've already checked the basics: time range is necessary, and the workspace SKU is at capacity. The query plan shows a huge shuffle operation.
What specific optimization patterns work for you on large-table joins in Sentinel? I'm looking for concrete tactics like:
* Forcing left-heavy vs. right-heavy join structures
* Using the `hint.strategy=broadcast` trick effectively on large result sets
* Pre-aggregating or filtering subqueries more aggressively than I already am
* Any hard limits on table sizes before joins become untenable
I'll run benchmarks on any suggested patterns and post the execution time deltas from the query logs.
-shift
shift left or go home
The shuffle is your biggest clue there - it means the system is trying to hash and redistribute huge volumes of data across nodes to perform that join. Your instinct on pre-aggregation is the right path.
You mentioned forcing left/right heavy, but with tables that large, your goal should be to make one side of the join as small as possible *before* the operation. Can you filter the SigninLogs side down further, maybe to only failed logins or a specific app? Even adding an extra predicate like `| where ResultType != 0` before the join can drastically cut the data volume for the hash match.
I'd be careful with `hint.strategy=broadcast` on a 30-day window; that works by sending the entire smaller table to every node processing the large one. If your "smaller" table is still millions of rows, you might just blow up memory. Try it on a 1-hour window first to test.
Have you looked at the actual distribution of your join key? If Account names are mostly unique or highly varied, that hash operation is going to be expensive no matter what. Sometimes summarizing counts per account *before* the join, then joining the summaries, is the only way forward.
Yeah, the shuffle comment makes total sense. I'm new to this but hit something similar last week, and my lead mentioned using `join kind=leftsemi` or `leftanti` as an alternative when you don't actually need columns from the second table. Just to see if a match exists. Would that help at all here, or is the `summarize` needing data from both tables?
Filtering the SigninLogs side further is the obvious move, but I've found it often just pushes the bottleneck upstream. You cut the right table down, sure, but then the shuffle on the left table becomes the new hot mess.
The real pattern I've had to adopt is a two-step summarize, like you hinted at. First, summarize counts per Account per day on *each* table separately, *then* join those tiny aggregated results. It feels clunky, but it's the only reliable way I've stopped the timeout clock on these massive log joins.
And you're dead right about testing broadcast hints on a tiny window first. I learned that the hard way last quarter. It's a quick way to get a cluster resource warning that makes our infra team side-eye me in Slack.