Hi, we have an on-prem Dynamics CRM server running version 8.2.5.4. We have one entity with about 1.2 million records (1,284,686). The SQL server is 2016 SP2. We are facing some performance issues pertaining this entity (and beyond)
The issue is that it takes about 10 seconds to click into the entity (if using the All Active Records View), and then about 10 seconds to save a record created from this View (and no its not a pending Sync job issue). Records created using Quick Create or from within Advanced Find do not experience this delay.
My question is twofold:
- The first obvious question that comes to mind is: If the first page only loads 50 records, then the SQL query should really take less than a millisecond. In fact, if a go directly to SQL and perform on a "Select Top 50" query against this Table or View, it completes in less than a millisecond. The issue however is the "Order By". If i added the "Order By" defined by the Dynamics View to my Table, the query jumped to 5 seconds (including all columns). I then used Profiler to capture the exact SQL query as performed by Dynamics, and sure enough that query took 9 seconds to complete. Dynamics is using the SQL View (not to confuse with the Dynamics View), not the table. I then tested the "Order By" i saw in the Profiler against the View. It took 30 seconds if i specified all columns, and 3 seconds if i specified just one column. If i add a filter to significantly limit the results (where CreatedOn =) , the SQL query completes is milliseconds. So yeah, the probable solution is to default a (Dynamics) View with less results. That said, i have a few questions.
- Is it possible to indicate to Dynamics to Not use OrderBy in the query?
- Why is the SQL View of the Dynamics CRM DB so much slower than the table?
- Are there any other suggestions and recommendations to improve Dynamics performance when dealing with a growing number of records (on a specific entity)?
- When adding a new record from the "All Active Records" View, it takes 10 seconds to Save the record. Sounds like its refreshing the View from which i clicked the New button, which is why when using "Quick Create" or from the "Advanced Find", it Saves in less than a millisecond. Is it possible to change this behavior?
