Tree Grid View slow: reads whole branch on expand — improvement ideas?

Hi Aras Community Folks,

I’m facing a performance issue with the Aras TGV, expanding a node is extremely slow at large data volumes (~10 minutes at 1M items), and it looks like the server reads all descendant rows even though only the first 100 are shown. Details below — would really appreciate any guidance.

Environment: Aras Innovator 14.36(14 SP36). Standard TreeGridView over a recursive Query Definition.

Short version of the problem:
When a user expands a node, the grid shows only the first 100 children (page size 100, with a “Show More” row). But under the hood, the server reads every descendant item beneath that branch — hundreds of thousands of rows — even though only 100 are displayed and none of the descendants are returned in the response. At 1Million items this makes a single expand take ~10 minutes.

Root item (starting point)
│
└─ Level B item                      ← via root's reference property
   │
   ├─ [B → B]  Count(child B) > 0
   │     └─ Level B item   ⟲ "Reuse Query Element Definition" → back to B   (recursion)
   │
   └─ [B → C]  Count(C) > 0
         └─ Level C item   (is_current = 1)
            │
            └─ [C → C]  Count(C) > 0
                  └─ Level C item   ⟲ "Reuse Query Element Definition" → back to C   (recursion)

Page size (max_child_items) = 100   |   Default grow depth = 2
Two recursive legs: B→B and C→C, each gated by Count(...) > 0

• Two recursive legs: B→B and C→C, each via Reuse Query Element Definition.
• Each relationship is gated by a Count(...) > 0 child-existence filter.
• TGV settings: page size max_child_items = 100, default grow depth = 2.

What we measured:

• Expand one mid-level B node at 1M items: ~8 min. Shows 100 B rows + “Show More”, 0 C rows — but SQL reads the full descendant set (hundreds of thousands of rows) twice (paging query + property read).
• Count(...) > 0 already compiles to a permission-aware EXISTS; the cost is the broad descendant read, tied to the expand-icon (hasChild) check.
• Relationship source_id/related_id indexes already exist.

Questions:

• On expand of an item, only the first 100 children are shown — but the server reads all descendants beneath the branch (full data) first, then returns 100 items. The descendants are read in full just to show 100 rows and to decide each row’s “+” expand icon. Is there a supported way to fetch only the 100 displayed rows from the start, and to make the expand-icon check stop at the first match instead of reading everything?

• Regarding the global tgv_MaxFetchItemsCount variable (default 1,000,000): we’re considering temporarily lowering it (e.g. to 100) before an expand and restoring the default afterwards, to limit how much is fetched. Does it actually limit the result to that many rows (return just the first N), or does it only reject an over-limit request with an error? And is it checked before the heavy SQL runs (so it saves the work), or after (so the rows are already read)?

Would really appreciate any guidance or suggestions on the queries above