CODEWITHSUNDEEP
azure-data-explorerkql
vaishnavi
vaishnavi

Reputation: 105

is it possible for better optimization of my kusto query

below is my Kusto query, it takes 2+ mins in lens dashboard to show the data, I have optimized my query to have materialize() in let statements and contains with has. is there anyother way to optimize it in a better way.

let C_masfunteams = materialize(find withsource=source in (cluster(X).database('oci-*').['TextFileLogs'])     where AttemptedIngestTime > ago(7d)
and FileLineContent  has "<li>Build Number:"  | summarize min(AttemptedIngestTime) by source, FileLineContent);//, AttemptedIngestTime
let n = C_masfunteams | extend databaseName = extract(@"""(oci-[^""]*)""", 1, source)
| extend BuildNumber = extract(@"([A-Z]\w*\.[0-9]\d*\.[0-9]\d*\.[0-9]\d*)",1,FileLineContent)
| extend StampVersion = extract(@"([0-9]\d*\.[0-9]\d*\.[0-9]\d*\.[0-9]\d*)",1,FileLineContent)
|extend cluster ='masfunteams'
| project BuildNumber , StampVersion , min_AttemptedIngestTime
|  summarize NumberOfRuns=count() , ingestedtime = min(min_AttemptedIngestTime) by BuildNumber,StampVersion;
let C_masfun= materialize(find withsource=source in (cluster(Y).database('oci-*').['TextFileLogs'])     where AttemptedIngestTime > ago(7d)
and FileLineContent  has "<li>Build Number:"  | summarize min(AttemptedIngestTime) by source, FileLineContent);//, AttemptedIngestTime
let m = C_masfun | extend databaseName = extract(@"""(oci-[^""]*)""", 1, source)
| extend BuildNumber = extract(@"([A-Z]\w*\.[0-9]\d*\.[0-9]\d*\.[0-9]\d*)",1,FileLineContent)
| extend StampVersion = extract(@"([0-9]\d*\.[0-9]\d*\.[0-9]\d*\.[0-9]\d*)",1,FileLineContent)
|extend cluster ='masfunteams'
| project BuildNumber , StampVersion , min_AttemptedIngestTime
|  summarize NumberOfRuns=count() , ingestedtime = min(min_AttemptedIngestTime) by BuildNumber,StampVersion;
let C_masvaas = materialize(find withsource=source in (cluster(z).database('oci-*').['TextFileLogs'])     where AttemptedIngestTime > ago(7d)
and FileLineContent  has "<li>Build Number:"  | summarize min(AttemptedIngestTime) by source, FileLineContent);//, AttemptedIngestTime
let o= C_masvaas | extend databaseName = extract(@"""(oci-[^""]*)""", 1, source)
| extend BuildNumber = extract(@"([A-Z]\w*\.[0-9]\d*\.[0-9]\d*\.[0-9]\d*)",1,FileLineContent)
| extend StampVersion = extract(@"([0-9]\d*\.[0-9]\d*\.[0-9]\d*\.[0-9]\d*)",1,FileLineContent)
|extend cluster ='masfunteams'
| project BuildNumber , StampVersion , min_AttemptedIngestTime
|  summarize NumberOfRuns=count() , ingestedtime = min(min_AttemptedIngestTime) by BuildNumber,StampVersion;
union isfuzzy=true  m,n,o
| summarize Ingestedtime =min(ingestedtime) by BuildNumber,StampVersion

Upvotes: 0

Views: 2275

Answers (1)

Avnera
Avnera

Reputation: 7618

Hi the query is quite complex and without running it on the actual cluster it is hard to figure out what is the expected results. So here are a few tips:

  1. Consider starting the union operator as the first operator with a uniform logic for the filtering, parsing and summarize operations
  2. Consider removing the materialize() if you are only using each dataset only once
  3. Consider removing the 'find' as you are not doing search across multiple columns, If you are using it to get the source table in your output records set, consider adding "withsource" to the union statement
  4. If possible consider using the 'parse' operator instead of the regular expression

Hope this helps!

Upvotes: 1

Related Questions