Our queries showing requirements hierarchy (Feature > User Story > Task) are taking 45+ seconds during planning sessions, making backlog reviews painful. We’re using WSAPI to fetch the full hierarchy for portfolio planning, but the performance has degraded significantly as our backlog grew to 1,800+ items.
Current query approach:
const query = {
fetch: ['FormattedID','Name','Children','Parent'],
query: '(PortfolioItem != null)',
order: 'Rank'
};
I suspect the hierarchical queries aren’t using proper indexes, but I’m not sure how to optimize this. Are there composite indexes we should be leveraging? Would lookback snapshots help with hierarchy traversal?
Hierarchical queries are notoriously slow when you’re fetching the Children collection inline. Each parent record triggers additional queries to load children, creating an N+1 query problem. Try fetching just the parent level first, then make separate queries for children only when needed. This reduces the initial load time dramatically.
The ‘order: Rank’ clause is killing your performance. Rank fields aren’t indexed the same way as other attributes because they’re dynamic. If you must sort by rank, consider doing it client-side after retrieval. Also, that PortfolioItem filter is doing a nullable check across potentially thousands of records - very expensive operation.
Good points about the Children collection and Rank sorting. But we need the hierarchy for portfolio rollups - showing which features have which stories. Is there a better way to structure this query? Maybe using specific PortfolioItem ObjectIDs instead of the null check?
Composite indexes in Rally are automatically maintained for common query patterns, but hierarchical traversal doesn’t benefit much from them. The real issue is your query is too broad. Instead of querying all items with PortfolioItem != null, query specific Features by ObjectID, then fetch their direct children. Build the hierarchy in memory client-side rather than asking Rally to do it.