Share
Report
240Hi,
We've resolved the issue in question for the time being.
However, I'm interested if anyone else has experienced this problem and how you tried to manage the situation going forwards?
Scenario:
We have a Dynamics CRM 2013 On-Premise Installation for a customer.
The CRM Organization contains the data for 2 branches of the business, which are of very different sizes.
We have a query run by an external tool, this is run for a specific date range and for a particular value of a lookup field which indicates which branch of the business we are interested in.
- For the branch_1, (the small one). The query will return approx 10,0000 rows.
- For branch_2, (the big one). The query returns approx 500,000 rows
So, an order of magnitude different.
Usually the branch_1 query is run first.
This will cause a parameterised query to be created in SQL server based on the data distribution of the smaller dataset.
Then the query for branch_2 is run. The stored query plan is sub-optimal for the larger dataset.
The query takes longer than 2 minutes to execute. We get a CRM Platform Error warning on the server and a Timeout on the IOrganizationService web service call.
Solution:
We deleted the stored query plan from the SQL Server. We the query ran again, SQL Server recalculated the plan from scratch and it ran in 6 seconds!
So, that is a one-off solution to this issue.
I am interested if others of you out there in Dynamics CRM land have experienced such issues and how you manage them?
I'd be interested to hear of your experiences.
Best Regards,
Shaun
*This post is locked for comments
All responses (
Answers (
