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.

 

This setting is what is used to determine at which point a CTE query should be executed for RetrieveMultiple.  The default value for this setting is 75,000, and can be modified via OrgDbOrgSettings.  If the entity’s record count is above the value for this setting, a CTE query will be executed.  If the entity’s record count is below the value for this setting, an OR-based query using joins will be used instead.

 
The entity’s record count is determined by retrieving the value from the RecordCountSnapshot table.  This table is updated daily by Maintenance Job 45 (RefreshRowCountSnapshots).  A helpful query that can be manually executed to review the Record Count sizes of every entity is below:
select a.count,b.name from RecordCountSnapshot a
join entity b on a.objecttypecode=b.ObjectTypeCode
order by a.count desc
Query examples of what you can expect to see from a CTE query and an or-based join query are below:

CTE Query

WITH "activitypointer0Security" as (
select Subject as "Subject", PriorityCode as "PriorityCode", RegardingObjectId as "RegardingObjectId", ActivityTypeCode as "ActivityTypeCode", StateCode as "StateCode", ScheduledStart as "ScheduledStart", ScheduledEnd as "ScheduledEnd", InstanceTypeCode as "InstanceTypeCode", ActivityId as "ActivityId", RegardingObjectIdYomiName as "RegardingObjectIdYomiName", RegardingObjectTypeCode as "RegardingObjectTypeCode", RegardingObjectIdName as "RegardingObjectIdName", 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 Subject as "Subject", PriorityCode as "PriorityCode", RegardingObjectId as "RegardingObjectId", ActivityTypeCode as "ActivityTypeCode", StateCode as "StateCode", ScheduledStart as "ScheduledStart", ScheduledEnd as "ScheduledEnd", InstanceTypeCode as "InstanceTypeCode", ActivityId as "ActivityId", RegardingObjectIdYomiName as "RegardingObjectIdYomiName", RegardingObjectTypeCode as "RegardingObjectTypeCode", RegardingObjectIdName as "RegardingObjectIdName", 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 Subject as "Subject", PriorityCode as "PriorityCode", RegardingObjectId as "RegardingObjectId", ActivityTypeCode as "ActivityTypeCode", StateCode as "StateCode", ScheduledStart as "ScheduledStart", ScheduledEnd as "ScheduledEnd", InstanceTypeCode as "InstanceTypeCode", ActivityId as "ActivityId", RegardingObjectIdYomiName as "RegardingObjectIdYomiName", RegardingObjectTypeCode as "RegardingObjectTypeCode", RegardingObjectIdName as "RegardingObjectIdName", IsRegularActivity as "IsRegularActivity", OwnerId as "OwnerId", OwningBusinessUnit as "OwningBusinessUnit", OwningUser as "OwningUser" from [ActivityPointer] as "activitypointer0" where "activitypointer0".OwningBusinessUnit in (@buId00)
 
)
select
top 51 "activitypointer0".Subject as "subject"
, "activitypointer0".PriorityCode as "prioritycode"
, "activitypointer0".RegardingObjectId as "regardingobjectid"
, "activitypointer0".ActivityTypeCode as "activitytypecode"
, "activitypointer0".StateCode as "statecode"
, "activitypointer0".ScheduledStart as "scheduledstart"
, "activitypointer0".ScheduledEnd as "scheduledend"
, "activitypointer0".InstanceTypeCode as "instancetypecode"
, "activitypointer0".ActivityId as "activityid"
, "activitypointer0".RegardingObjectIdYomiName as "regardingobjectidyominame"
, "activitypointer0".RegardingObjectTypeCode as "regardingobjecttypecode"
, "activitypointer0".RegardingObjectIdName as "regardingobjectidname"
, "activitypointerowningusersystemusersystemuserid".InternalEMailAddress as "activitypointerowningusersystemusersystemuserid.internalemailaddress"
from
 activitypointer0Security as "activitypointer0" WITH (NOLOCK)  left outer join SystemUser as "activitypointerowningusersystemusersystemuserid" WITH (NOLOCK)  on ("activitypointer0".OwningUser  =  "activitypointerowningusersystemusersystemuserid".SystemUserId)
where
 (("activitypointer0".IsRegularActivity = @IsRegularActivity0)) order by
 "activitypointer0".ScheduledEnd asc
, "activitypointer0".ActivityId asc

OR-Based Query Using Joins

exec sp_executesql N'select
top 51 "activitypointer0".Subject as "subject"
, "activitypointer0".PriorityCode as "prioritycode"
, "activitypointer0".RegardingObjectId as "regardingobjectid"
, "activitypointer0".ActivityTypeCode as "activitytypecode"
, "activitypointer0".StateCode as "statecode"
, "activitypointer0".ScheduledStart as "scheduledstart"
, "activitypointer0".ScheduledEnd as "scheduledend"
, "activitypointer0".InstanceTypeCode as "instancetypecode"
, "activitypointer0".ActivityId as "activityid"
, "activitypointer0".RegardingObjectIdYomiName as "regardingobjectidyominame"
, "activitypointer0".RegardingObjectTypeCode as "regardingobjecttypecode"
, "activitypointer0".RegardingObjectIdName as "regardingobjectidname"
, "activitypointerowningusersystemusersystemuserid".InternalEMailAddress as "activitypointerowningusersystemusersystemuserid.internalemailaddress"
from
 ActivityPointer as "activitypointer0" WITH (NOLOCK)  left outer join SystemUser as "activitypointerowningusersystemusersystemuserid" WITH (NOLOCK)  on ("activitypointer0".OwningUser  =  "activitypointerowningusersystemusersystemuserid".SystemUserId)
where
 (("activitypointer0".IsRegularActivity = @IsRegularActivity0) and ("activitypointer0".OwnerId in (select pem.PrincipalId from PrincipalEntityMap pem  WITH (NOLOCK) join SystemUserPrincipals sup  WITH (NOLOCK) on pem.PrincipalId = sup.PrincipalId where sup.SystemUserId = @UserIdOwnerCommand00 and pem.ObjectTypeCode = @OtcOwnerCommand00)
 or "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) or "activitypointer0".OwningBusinessUnit in (select BusinessUnitId from SystemUserBusinessUnitEntityMap WITH (NOLOCK) where SystemUserId = @SystemUserId0 and ObjectTypeCode = @ObjectTypeCode1))) order by
 "activitypointer0".ScheduledEnd asc
, "activitypointer0".ActivityId asc',N'@IsRegularActivity0 bit,@UserIdOwnerCommand00 uniqueidentifier,@OtcOwnerCommand00 int,@SystemUserId00 uniqueidentifier,@ObjectTypeCode0 int,@SystemUserId0 uniqueidentifier,@ObjectTypeCode1 int',@IsRegularActivity0=1,@UserIdOwnerCommand00='10762A09-58F6-E311-B866-6C3BE5A80FD8',@OtcOwnerCommand00=4200,@SystemUserId00='10762A09-58F6-E311-B866-6C3BE5A80FD8',@ObjectTypeCode0=4200,@SystemUserId0='10762A09-58F6-E311-B866-6C3BE5A80FD8',@ObjectTypeCode1=4200
 

Next post in this blog series: Sharing Considerations