Forum Discussion
Luizao_f
Jul 31, 2021Brass Contributor
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!
- Anders Bengtsson
Microsoft
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.
- Anders Bengtsson
Microsoft
Hi,
Is it correct that you get the result you want, but it takes too much time to run the query?- Luizao_fBrass ContributorYes, exactly that.
The union brings the answers, but the query ends up being "heavy". Are you experiencing the same problems?