WSAPI returning results slowly when including a dropdown list reference
When a dropdown list is included in the query, the backend database must loop through each value in the list for each work item that would be returned. If a large number of values exists in the dropdown list, this can adversely affect performance.
This issue can be particularly noticeable on portfolio items, particularly upper-level portfolio item types because we also recursively cascade loop through any children of the portfolio items in the same manner.
The best practice, in this case, would be to remove the reference to the dropdown from the query, but return the value in the fetch. Then within code on the customer side, loop through the resultset and drop the undesired values from the resultset.
This sounds counterintuitive since it results in a larger amount of data being pulled, however, the more lightweight query puts less load on the database and allows the query to complete more quickly.