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] ) ;
Martijn Bronkhorst – Premier Field Engineer