Have you noticed when searching for a customer address using the built in Address dialog that only addresses with a Name value are returned. I’ve had instances where the default address name field has been used as House Name and as we all know not all houses have a name.
If you have look at the code that is used to open the Address Lookup it calls an aspx page named lookupsingle.aspx and passes a querystring parameter ‘class’ with a value of ‘BrowserCustomerAddress’. BrowseCustomerAddress is the title of an xml file found in the ‘Microsoft Dynamics CRM\Applications Files’ folder as BrowseCustomerAddress.xml. This file contains the query elements that the Address dialog uses to retrieve the specific customer addresses. A number of fields and also 3 conditions exist in this file and you will notice that one condition relates to the name field with an operator value of not-null. If you remove this condition node from the xml file, save it and then test your address dialog, you will now see all of the addresses for a customer. This is unsupported so only perform this if absolutely necessary and your aware of the implications.
To reuse the Address dialog on your own entities or where the Address Lookup is not available , for example to fill custom address fields on a Case, then Dave Berry has a good example here of how to utilise the standard MSCRM Address Lookup dialog for your own use.
For a more customised approach then there is an MSDN article here on how to retrieve data for a related entity, in this case it demonstrates retrieving addresses.
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.