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
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))UNIONselect 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)UNIONselect ActivityId as "ActivityId", IsRegularActivity as "IsRegularActivity", OwnerId as "OwnerId", OwningBusinessUnit as "OwningBusinessUnit", OwningUser as "OwningUser" from [ActivityPointer] as "activitypointer0" where "activitypointer0".OwningBusinessUnit in (@buId00))selectCOUNT(*) as [#TotalRecordCount]from(selecttop 5001 "activitypointer0".ActivityId as "activityid", "activitypointerowningusersystemusersystemuserid".SystemUserId as "activitypointerowningusersystemusersystemuserid.systemuserid"fromactivitypointer0Security 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 post is locked for comments