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?

Thanks for your feedback! Did this solve your issue?

Comments (optional):

Thanks for your feedback!
We're glad it was helpful but sorry it didn’t solve your issue. If you need assistance, click Chat with Support below.
We’re sorry to hear that. Please tell us why.

 I don't like how this works.

 The answer is confusing.

 The answer didn't match what I was searching for.

Additional Comments (optional):

Thanks for your feedback! If you need assistance, click Chat with Support below.
Thanks for your feedback. Help us make our products even better by sharing details in our Idea Banks or our online Community.
Thanks for letting us know. We'll work on clarifying the information in the article. If you need assistance, click Chat with Support below.
Thanks for letting us know. We'll work on updating the search engine to return more relevant results.