Performance: Ad-hoc revenue query processes slowly and produces 0 results

When processing complex revenue queries, the process hangs and may produce 0 results.
The problem is not in [V_QUERY_REVENUERECOGNITION], but rather in the plan that the optimizer generates as a result of the filter criteria, namely the Application Details\Revenue Recognition Credits\Type filter. When the plan is created, the estimates for how many rows will be returned from the V_QUERY_REVENUE and V_QUERY_REVENUESPLIT views in the inner query is well below the number of rows that actually get returned when run. Based on the estimate, the optimizer attempts to use an index seek to get the results for both, which will perform horribly in this case. Without messing with those query views, which could have negative performance impacts in other queries, the best alternative is to create a static selection to get all revenue records with the recognition type of "Mirror" and then filter them out in the main query. This solution will require more overhead to make sure that the selection is up to date, but from my testing using dynamic selections was as bad or worse than doing nothing. It may also be worth testing to see if updating statistics on the revenue and revenue split tables has any effect before creating the selection, but it seems unlikely based on the way other parts of the query making use of these query views is behaving.




Was this article helpful?