MS Dynamics CRM 2011 how to increasing the performance of Excel dynamic worksheets

Exporting Microsoft Dynamics CRM data to Excel can be a great way to enable quick and flexible reporting. Users can export data to Excel in a static and dynamic form to export either the data or the query to the data. When exporting Microsoft Dynamics CRM data to a dynamic worksheet, the worksheet contains a query that utilizes the FilteredViews in the CRM database. Running queries on the FilteredViews in an organization with a high volume of users will dramatically impact the performance of the FilteredViews. This degradation in performance is a result of the fact that FilteredViews use a SQL scalar function (fn_FindUserGuid) that retrieves users’ ID based on username.

The query used by the function is:

select @userGuid = s.SystemUserId

from SystemUserBase s
where s.DomainName = SUSER_SNAME()

A quick and easy way to enhance query performance is to add indexes to the CRM database. Index maintenance is the preferred way in CRM to performance tune the CRM database. To increase the performance of the scalar function that is used by the Excel dynamic worksheets exported from CRM, create an index on the ‘DomainName’ column using the following statement (be sure to replace ‘YourOrgName’ with the unique name of your CRM organization):

CREATE NONCLUSTERED INDEX ix_SystemUserBase_DomainName ON

[YourOrgName_MSCRM].[dbo].[SystemUserBase] ( [DomainName] ) ;

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: