Forum Discussion

Luizao_f's avatar
Luizao_f
Brass Contributor
Jul 31, 2021

Cross-workspace query with less CPU consumption

Good morning people.

I'm using the concept of Cross Workspace query to query in more than one space because it's an MSSP environment.

The point is that the "union" command consumes a lot of processing, since it joins all the data from the tables that are in the union and then apply the filters.

Do you know any way or resource/method that I can make Cross Workspace queries that don't consume as much processing?

Thank you!

  • Hi,

    Have you noticed that workspace("<WorkspaceID>").TableName can be used in many commands, for example, JOIN too. But maybe you don't have a key to do the JOIN on. Else JOIN rather than UNIONS is recommended. Also to filter the data as much as possible prior to JOIN and project-away any unnecessary columns. Also, have the data in as few regions as possible. 

     

    Have you read about FIND at this page union operator - Azure Data Explorer | Microsoft Docs 

    I did a couple of small tests in my lab and I can see an improvement using FIND instead of UNION. 

  • Hi,
    Is it correct that you get the result you want, but it takes too much time to run the query?
    • Luizao_f's avatar
      Luizao_f
      Brass Contributor
      Yes, exactly that.
      The union brings the answers, but the query ends up being "heavy". Are you experiencing the same problems?

Resources