Microsoft CRM is built upon a platform that allows for an ever increasing level of customisation and extension. Microsoft is providing this platform packaged as a CRM system and we now have the term XRM which is devised to break Microsoft CRM down to this core platform and then build something up from it. It’s this platform for rapid application development that would be ideally shipped as a framework of some sort that allows for custom systems building and also with the ability to install add ons such as CRM, NAV, GP, etc…
Name it the Microsoft Application Framework or Dynamics Application Framework (DAF, ok maybe not the best acronym) but integrated with Visual Studio, SQL Server, SharePoint and the various other Microsoft technolgies. Imagine the ease of integrating CRM with NAV if both systems existed within the same framework.
Obviously Microsoft is building up and investing alot of effort into this feature rich application framework, namely Microsoft CRM and its underlying platform, and only time will tell if the true potential of this technology mix will be achieved. We could maybe take and alter a statement from HomerSimpson by saying “The Microsoft Application Framework, is there anything it can’t do!”, and although this doesn’t actually exist just yet, I hope it will.
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.