materialize can reduce CPU time?

Question

From official document https://kusto.azurewebsites.net/docs/kusto/query/materializefunction.html, materialize can improve the performance when use operator "union":

• Use materialize with join or union when their operands have mutual subqueries that can be executed once. For example, join/union fork legs. See example of using join operator.

let View_SelectArgMax = (

let View_SelectArgMin = (

let View_Grid = materialize(     union View_SelectArgMax, View_SelectArgMin

Do we need to add materialize to variable "View_SelectArgMax " and "View_SelectArgMin"? I tested on my local, it seems if we add materialize then the cpu performance will improve. Could you help to explain why? For above code, do we need to add materialize as below? let View_SelectArgMax = materialize(

let View_SelectArgMin = materialize(

let View_Grid = materialize(     union View_SelectArgMax, View_SelectArgMin

Answer 1

Hello Jianbo Yao (Centific Technologies Inc),

Welcome to the Microsoft Q&A forum.

materialize() caches a result set.  It is useful when you reuse that result set multiple times during a query.

so it doesn’t need to be computed again when it’s used multiple times in the main query.

In your case, if View_SelectArgMax and View_SelectArgMin are used multiple times in your queries, then adding materialize to these variables can indeed improve performance.

This is because the results of these subqueries are computed once and then stored, reducing the amount of computation needed when they’re used again.

However, if these variables are only used once in your queries, then adding materialize might not lead to a noticeable improvement in performance.

I hope this helps. Please let me know if you have any further questions.