Forum Discussion
Query Taking over 15 Seconds
Hello All,
I have inherited a report and am trying to optimize it for better performance.
The query below is taking over 15 seconds.
The tile is a Matrix.
I will greatly appreciate your suggestions to optimize it.
Many thanks!
Pavan
// =================
// Operation : 9
// Visual : SISO Snapshots Comparison
// Query Start : 3/11/2024 8:56:55 PM
// Query End : 3/11/2024 8:57:11 PM
// Render Start : 3/11/2024 8:57:11 PM
// Render End : 3/11/2024 8:57:11 PM
// Query Duration : 15564 ms
// Render Duration : 116 ms
// Total Duration : 15680 ms
// Row Count : 96
// =================
DEFINE
VAR __DS0FilterTable =
TREATAS({2024}, 'ChainCalendar'[Year_NO])
VAR __DS0FilterTable2 =
TREATAS({52}, 'ChainCalendar'[WeekNumber])
VAR __DS0FilterTable3 =
TREATAS({"Aligned"}, 'ChainCalendar'[CalendarName])
VAR __DS0FilterTable4 =
TREATAS({"Actuals"}, 'SISO'[Target_Name])
VAR __DS0FilterTable5 =
TREATAS({"Active"}, 'SISO'[SIGNATURE_Main])
VAR __DS0FilterTable6 =
TREATAS({"'SISO'[RETAILER (groups)]"}, 'Parameters'[Parameters Campos])
VAR __DS0FilterTable7 =
TREATAS(
{"Sell-In",
"Sell-Out",
"Stock-in-Trade",
"SISO Variance"},
'Metrics'[SISO calc]
)
VAR __DS0FilterTable8 =
TREATAS({"% Chg",
"Abs"}, 'MeasureSwitch'[Measure])
VAR __DS0FilterTable9 =
TREATAS({"Dollar"}, 'DollarUnitSwitch'[$/UN])
VAR __DM3FilterTable =
TREATAS({"AMAZON",
"JEAN-COUTU",
"OTHERS",
"WALMART"}, 'SISO'[RETAILER (groups)])
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
'SISO'[RETAILER (groups)], "IsGrandTotalRowTotal",
ROLLUPGROUP('Metrics'[SISO calc], 'Metrics'[Ordinal]),
"IsDM1Total",
NONVISUAL(__DM3FilterTable)
),
'Calendar Intelligence'[Calendar Calc],
'Calendar Intelligence'[Ordinal],
'MeasureSwitch'[Measure],
'MeasureSwitch'[Ordinal],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
__DS0FilterTable5,
__DS0FilterTable6,
__DS0FilterTable7,
__DS0FilterTable8,
__DS0FilterTable9,
"v__DR", 'Metrics'[$ DR],
"v___DR_FormatString", IGNORE('Metrics'[_$ DR FormatString])
)
VAR __DS0PrimaryWindowed =
TOPN(
102,
SUMMARIZE(
__DS0Core,
'SISO'[RETAILER (groups)],
[IsGrandTotalRowTotal],
'Metrics'[SISO calc],
'Metrics'[Ordinal],
[IsDM1Total]
),
[IsGrandTotalRowTotal],
0,
'SISO'[RETAILER (groups)],
1,
[IsDM1Total],
0,
'Metrics'[Ordinal],
1,
'Metrics'[SISO calc],
1
)
VAR __DS0SecondaryBase =
SUMMARIZE(
__DS0Core,
'Calendar Intelligence'[Calendar Calc],
'Calendar Intelligence'[Ordinal],
'MeasureSwitch'[Measure],
'MeasureSwitch'[Ordinal]
)
VAR __DS0Secondary =
TOPN(
102,
__DS0SecondaryBase,
'Calendar Intelligence'[Ordinal],
1,
'Calendar Intelligence'[Calendar Calc],
1,
'MeasureSwitch'[Ordinal],
1,
'MeasureSwitch'[Measure],
1
)
VAR __DS0BodyLimited =
NATURALLEFTOUTERJOIN(
__DS0PrimaryWindowed,
SUBSTITUTEWITHINDEX(
__DS0Core,
"ColumnIndex",
__DS0Secondary,
'Calendar Intelligence'[Ordinal],
ASC,
'Calendar Intelligence'[Calendar Calc],
ASC,
'MeasureSwitch'[Ordinal],
ASC,
'MeasureSwitch'[Measure],
ASC
)
)
EVALUATE
__DS0Secondary
ORDER BY
'Calendar Intelligence'[Ordinal],
'Calendar Intelligence'[Calendar Calc],
'MeasureSwitch'[Ordinal],
'MeasureSwitch'[Measure]
EVALUATE
__DS0BodyLimited
ORDER BY
[IsGrandTotalRowTotal] DESC,
'SISO'[RETAILER (groups)],
[IsDM1Total] DESC,
'Metrics'[Ordinal],
'Metrics'[SISO calc],
[ColumnIndex]
1 Reply
Hi Anonymous
The SummarizeColumns() function can slow down Measure dramatically.
The measure is calculating dynamically, everytime you refresh the visual, it will re-run the whole flow.
Maybe you can consider change the variable __DS0Core into a real table, so that can greatly increase the speed.
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.