# Simplify a complex KQL Query

The situation is, I have two fields in a database (or that can be derived) - when the message was received (Received, of type DateTime), and the day of the month (but not the month itself) that the message was supposed to be received (DayOfRun of type Integer, which is the last two digits of an ID column that doesn't otherwise contain date-time information). I need to know the difference (in whole days, ignoring the time) between the two. If the message arrived before the day of run, the number will be negative.

I can guarantee that the entries will not differ by more than half a month in either direction.

The pseudo-code logic I have come up with is as follows:

DayOfRun = Extract last two characters from ID Field
DaysInPreviousMonth = Extract Count of Days in (Month before Received)
Pick the closest to 0 from the following:
A:   If DayReceived < DayOfRun then
:      DaysInPreviousMonth + DayReceived - DayOfRun ;
:   Otherwise
:       DaysInPreviousMonth + DayOfRun - DayReceived ;



Examples:

01 2021-04-29 -2 29th April is 2 days before 1st May (can't be 1st April - would be too far away)
28 2021-05-02 4 02nd May is 4 days after 28th April (which gives a smaller absolute number than assuming 28th May, which would be too far away anyway)
14 2021-04-28 14 28th April is 14 days after 14th April (which gives a smaller absolute number than if we assumed 14th May)

My KQL to implement this logic is as follows:

tableName
| project DayOfRun = toint(substring(['ID Field'], -2, 2)), ['Day Of Month Received'] = datetime_part("day",ErrorTimestamp), ErrorTimestamp
| where DayOfRun <> ['Day Of Month Received']
| project ['Days In Previous Month'] =  datetime_part("day",endofmonth( datetime_add("month",-1, ErrorTimestamp))), DayOfRun, ['Day Of Month Received']
| project ['Day Of Month Received'], Diff = (['Day Of Month Received'] - DayOfRun), CrossMonthDiff = iif(['Day Of Month Received'] < DayOfRun,(['Days In Previous Month']+['Day Of Month Received']-DayOfRun),(['Days In Previous Month']+DayOfRun-['Day Of Month Received']))
| summarize ['Day Of Month Received'], ['Difference (Days)'] = iif(min_of(abs(Diff),abs(CrossMonthDiff)) == abs(Diff),Diff,CrossMonthDiff)
| order by ['Difference (Days)'] desc, ['Day Of Month Received'] asc


This query seems overly complex, and is quite slow to run. Any thoughts on how to improve either the logic or the query itself?

• Feel free to suggest better tags May 4, 2021 at 14:29

2. You didn't add any filters on datetime columns, meaning you're querying the whole table. For this to work efficiently, you need all the data to be in the hot cache. Is this your case? To see the effective retention and caching policies for the table, run .show table tableName details | project RetentionPolicy, CachingPolicy.
3. You might want to try using summarize hint.strategy=shuffle (see this for more info).