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.