Cross-workspace query with less CPU consumption

[Luizao_f]

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!

[#2]

Hi,
Is it correct that you get the result you want, but it takes too much time to run the query?

[#3]

Yes, exactly that.
The union brings the answers, but the query ends up being "heavy". Are you experiencing the same problems?

[#4]

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.