Performance Optimization of CRM 2011 Quick Find Queries

Quick Find is a basic navigation tool to help you find specific records in CRM 2011. Quick Find optimizations have been included in Dynamics CRM 2011 Update Rollup 10 (UR10). By default, these are enabled after applying UR10; however, you can disable them using the option in System Settings:

(Settings | Administration | System Settings | General Tab)

The setting is at the bottom of the “General” tab called “Enable Quick Find Record Limits,” which is set to “Yes” by default.

Engable or disable CRM 2011 quick find records

When the optimizations are enabled, if more than 10,000 records are returned by a Quick Find query, the following error will occur:  “Quick find limit exceeded. Please use a more selective search value, or use Advanced Find for your search.”

If users need to search using terms that will potentially match over 10,000 records, this setting can be disabled. However, disabling the setting may result in slower overall performance for Quick Find queries. A more appropriate tool for such queries would be to use Advanced Find.

In order to take full advantage of the new Quick Find optimizations, customers will need to implement custom SQL indexes on their Find Columns.  As SQL indexes currently cannot be created in CRM Online, this can only be done in On Premise.

Example:

If you have a custom field on the Contact entity called po_CustomField, and you have enabled this as a Find Column for Quick Find searches.  In order to take full advantage of the new optimizations, create the index:

CREATE NONCLUSTERED INDEX IndexName

ON [dbo].[ContactExtensionBase] ([new_CustomField])

GO

You can optimize the way query performance by modifying the way CRM displays the query results. With the release of Dynamics CRM 2011 UR10, we can now limit the search results returned by quick find and also utilize the new Quick Find query structure by implementing SQL indexes on the column or set of columns used in a Quick Find query.

For more information on performance improvements in Dynamics CRM 2011 UR10 and Quick Find queries please Optimizing and Maintaining a Microsoft Dynamics CRM 2011 Server Infrastructure Performance white paper.

Source: Power Objects Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: