I’ve got a formatting rule that is comparing the quantities over two different SELECT commands. The formatting rule works, but I’m getting a performance warning about how expensive SELECTs are.
Has anybody ran into the issues with performance over thousands or records? If so, what can you share about it?
Here is my filtering expression
SUM(
SELECT(
CUST_ORDER_LINE[ORDER_QTY],
AND(
[CUST_ORDER_ID] = [_THISROW].[OrderId],
[LINE_NO] = [_THISROW].[OrderIdLineNo]) ) )
>=
SUM(
SELECT(
WorkOrders[RunQty],
AND(
[OrderId] = [_THISROW].[OrderId],
[OrderIdLineNo] = [_THISROW].[OrderIdLineNo]) ) )
This is equivalent to asking “how much money is enough?”
Formatting rules take time to calculate every time the user navigates to the view. It is far better to store a calculated value in a column, and make the format rule much simpler, like a simple equality.
User | Count |
---|---|
41 | |
28 | |
24 | |
24 | |
13 |