MSCRM Search Performance

So you’ve imported your data, set up your Quick Find/Lookup views and you go and attempt a search. 5 seconds, 10 seconds, 20 seconds, ummm, timeout…

When you search for MSCRM performance on the internet you will find  Optimizing and Maintaining Microsoft Dynamics CRM 4.0 and Improving Microsoft Dynamics CRM Performance and Securing Data with Microsoft SQL Server 2008 as the ideal starters, but after implementing all of these recommendations you still take 20 seconds on a search.

At this point you start to remove search fields from your view and test again but knowing full well that this is not going to meet your business requirement. You use SQL Profiler and the SQL tuning wizard to try to find the required indexes yet your search is just not coming back in the required time. Do you beef up your tin? Increase your RAM? Install a quicker hard disk array? No… You look at where your search fields are within the database and make sure they are all on the same table.

Issues arise when you try and search across an entity’s base table and extension base table in CRM using Quick Find or the Lookup dialog. The indexes are basically ignored when you cross over the tables. So searching on a Contact when you search on Fullname (Contactbase) and new_anotherField (ContactExtensionbase) will get slower performance. If you try searching on only Fullname the search will be lightning quick, mainly because there is an index already on this field but as soon as you add another search field from the extension table you will not be impressed.

You could make sure all your search fields are on the extension base table and add some indexes to see if that improves. If you have data in the base table (i.e Fullname) then you would have to create an attribute on the extension table and fill it in when the entity is saved using a plugin. For example fullname (ContactBase table) data could be stored in new_fullname (ContactExtensionBase table). Then all of your other custom fields which already exist on the extension table can be placed in an index/indexes and you’ll notice a huge improvement. You might say things like its redundant data, its a pain to have a plugin just for that, and so on, but show your end users the 20 seconds search and you won’t be implementing the system.

On an unsupported side of things, if you do use search fields only from the extension base table and modify the SQL Server entity view (i.e Contact) by swapping the base table (i.e ContactBase) with the extensionbase table (i.e. ContactExtensionBase) and vice versa in the view’s SQL then you will get astonishing results.

I’m referring to the Contact entity here for reference but the same applies across the system.

The timeout period elapsed prior to completion of the operation or the server is not responding

There are a number of causes for this error message and their are a range of msdn articles covering these issues including importing customisations through to changing the user’s business unit. The default of 30 seconds is sometimes just not enough. The below instructions are taken from one of the many msdn articles.

Restart IIS after adding the values

Add the OLEDBTimeout and the ExtendedTimeout registry subkeys to increase the time-out values

  1. Click Start, click Run, type regedit, and then click OK.
  2. Locate the following registry subkey:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM
  3. Right-click MSCRM, point to New, and then click DWORD Value to create a new DWORD value.
  4. Rename the DWORD value to the following value:
    OLEDBTimeout
  5. Right-click the DWORD value, and then click Modify.
  6. In the Edit DWORD Value dialog box, type 86400 in the Value data box, click Decimal in the Base option, and then click OK.Note According to the requirement of the computer that is running SQL server and the number of customization files, the value can be larger than 86400. The value of 86400 is equivalent to 24 hours.
  7. Right-click MSCRM, point to New, and then click DWORD Value to create a new DWORD value.
  8. Rename the DWORD value to the following value:
    ExtendedTimeout
  9. Right-click the DWORD value, and then click Modify.
  10. In the Edit DWORD Value dialog box, type 1000000 in the Value data box, and then click OK.Notes
    • In the Value data box, you can type a value that is larger than 1,000,000. However, do not type a value that is larger than 2,147,483,647. This is hexadecimal 0x7FFFFFFF.
    • If this key already exists, notice the current value. After you have completed the import or the upgrade for Microsoft Dynamics CRM, set the value of this key back to the original value or delete the key if it did not previously exist. The default OLEDB timeout value is 30 seconds.