Speed Up Data Mining In Microsoft Dynamics CRM

Does your CRM system run slow when you are mining data? Is that report or chart that you built a couple years ago taking longer to render? As you add more data to any system, it will always take longer to find what you are looking for. These may be symptoms of missing SQL indexes, which are built against your database’s tables and views to help speed the retrieval of data. SQL indexes are traditionally built directly on a SQL server by a database administrator, but Microsoft CRM offers you the ability to create these from the CRM interface. Are you ready to learn how?

In this example, I’ve built a few reports and charts that filter on the Item Id field on an Invoice Item. These searches were very quick when I had a few thousand items, but have slowed down significantly now that I have millions of records.

The first step is to navigate to the Quick Find view on the Invoice Item entity, and click Add Find Columns.

Click the box next to Item ID.

CRM will now build your indexes for you, typically overnight.   Here’s a screenshot of the SQL indexes created in my system. You can see this if you have SQL access in CRM onPremise, but you will not have this visibility online.

You can gauge the success of your indexes by timing the report generation pre & post SQL indexes. In my example, my report generation dropped from 71 seconds to 11 seconds, when querying the same amount of data. Be careful! It is possible to create too many indexes on any database, which can increase the database size and slow down the system overall. Be very selective when choosing your indexes, or reach out to your partner for guidance.

If you are looking for a certified Microsoft solution partner to help you with your CRM installation, make sure you pick the one that keeps in sync with all of the latest technologies. How can we help you? Please feel free to contact us.

[code-snippet name=”blog”]