Simplify a complex KQL Query

Question

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
DayReceived = Extract DayOfMonth from Received;
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 ;

B:   DayReceived - DayOfRun;

Examples:

Day of Run	Received	Answer	Reasoning
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?

Answer 1

A few suggestions:

1. You're performing string manipulations, and then extracting numbers out of the string result, which is slow. The best thing to do is to ingest the data in a better format in the first place (so that the number you're extracting will be ingested in a separate numeric column). If it's not possible to do it in ingestion, then you should do it in an Update Policy.

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).