Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Blogs / Microsoft Dynamics CRM Support Blog / Other Considerations

Other Considerations

Erik Hagen Profile Picture Erik Hagen

This post is part of a multi-part blog series regarding RetrieveMultiple performance.  To see a list of the other parts of this series, click here.

 

TotalRecordCount Considerations

The TotalRecordCount query will also execute using the security considerations of RetrieveMultiple.  Both the TotalRecordCount and RetrieveMultiple query need to complete prior to data being displayed for the end user.  If the customer does not need the TotalRecordCount value to be displayed, it is recommended to disable it for performance reasons by setting SkipGettingRecordCountForPaging to True using OrgDbOrgSettings.


An example of a TotalRecordCount query is below:

WITH "activitypointer0Security" as (
select ActivityId as "ActivityId", IsRegularActivity as "IsRegularActivity", OwnerId as "OwnerId", OwningBusinessUnit as "OwningBusinessUnit", OwningUser as "OwningUser" from [ActivityPointer] as "activitypointer0" where "activitypointer0".OwnerId in (SELECT id from dbo.fn_GetGuidsFromString(@buIds0))
 
UNION
select ActivityId as "ActivityId", IsRegularActivity as "IsRegularActivity", OwnerId as "OwnerId", OwningBusinessUnit as "OwningBusinessUnit", OwningUser as "OwningUser" from [ActivityPointer] as "activitypointer0" where "activitypointer0".ActivityId in (select POA.ObjectId from PrincipalObjectAccess POA  WITH (NOLOCK) join SystemUserPrincipals sup  WITH (NOLOCK) on POA.PrincipalId = sup.PrincipalId where sup.SystemUserId = @SystemUserId00 and POA.ObjectTypeCode = @ObjectTypeCode0 and ((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1) = 1)
UNION
select ActivityId as "ActivityId", IsRegularActivity as "IsRegularActivity", OwnerId as "OwnerId", OwningBusinessUnit as "OwningBusinessUnit", OwningUser as "OwningUser" from [ActivityPointer] as "activitypointer0" where "activitypointer0".OwningBusinessUnit in (@buId00)
 
)
select
COUNT(*) as [#TotalRecordCount]
from
 (select
top 5001 "activitypointer0".ActivityId as "activityid"
, "activitypointerowningusersystemusersystemuserid".SystemUserId as "activitypointerowningusersystemusersystemuserid.systemuserid"
from
 activitypointer0Security as "activitypointer0" WITH (NOLOCK)  left outer join SystemUser as "activitypointerowningusersystemusersystemuserid" WITH (NOLOCK)  on ("activitypointer0".OwningUser  =  "activitypointerowningusersystemusersystemuserid".SystemUserId)
where
 (("activitypointer0".IsRegularActivity = @IsRegularActivity0))) as "#Subquery"

Other Considerations and Limitations

 

·       When a query is being executed offline, we will default to ERMO = 3.  Data is filtered when offline, so there isn’t a need for these optimizations.  Also, security is not a big consideration when offline, since you cannot take any data offline that you don’t have access to read anyway.  Additionally, the Shapshot tables are not taken offline.

 

·       Quick Find defaults to ERMO = 3.  The reason for this is that the quick find will be the most selective criteria, thus there is no need to add the overhead of a CTE query.

 

·       RetrieveMultiple settings are universal, so currently they cannot be set differently per entity.  This is being considered for a future release.

 
This is the last part of this blog series.

Comments

*This post is locked for comments