Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Blogs / Microsoft Dynamics CRM Support Blog / IdsCount* Settings

IdsCount* Settings

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.

These settings are what is used to determine how to format our security clause.  There are three methods that can be used for the security clause based on these settings:  Inline IDs, TVF, or Joins.  Descriptions of the settings used to make this determination are below, along with query examples.  In the query examples, the highlighted portion illustrates the differences made by each setting change.
 

IdsCountForUsingGuidStringForSecurity

 
This is the setting that determines whether we use Inline IDs, or pass the IDs as a string and parse the string using TVF to create an in-memory table of IDs.  The default value for this setting is 20, and can be modified via OrgDbOrgSettings.  This setting cannot be set to a higher value than IdsCountBeforeUsingJoinsForSecurity.
 

IdsCountBeforeUsingJoinsForSecurity

 
This is the setting that determines at what point we no longer use TVF as defined by the above setting, and instead use joins against the different security tables rather than passing IDs to SQL.  The default value for this setting is 1000, and can be modified via OrgDbOrgSettings.  This value cannot be set to a lower setting than IdsCountForUsingGuidStringForSecurity.
  

Query Below IdsCountForUsingGuidStringForSecurity

exec sp_executesql N'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 (@userPrincipalWithBasicDepth00,@userPrincipalWithBasicDepth1100,@userPrincipalWithBasicDepth2100,@userPrincipalWithBasicDepth3100,@userPrincipalWithBasicDepth4100,@userPrincipalWithBasicDepth537,@userPrincipalWithBasicDepth5360,etc.etc)
 or "activitypointer0".OwningBusinessUnit in (@buId00)
)
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))
)
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

Query Above IdsCountForUsingGuidStringForSecurity, Below IdsCountBeforeUsingJoinsForSecurity

exec sp_executesql N'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

Query Above IdsCountForUsingJoinsForSecurity

exec sp_executesql N'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 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)
 
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',N'@IsRegularActivity0 bit,@UserIdOwnerCommand00 uniqueidentifier,@OtcOwnerCommand00 int,@SystemUserId00 uniqueidentifier,@ObjectTypeCode0 int,@buId00 uniqueidentifier',@IsRegularActivity0=1,@UserIdOwnerCommand00='10762A09-58F6-E311-B866-6C3BE5A80FD8',@OtcOwnerCommand00=4200,@SystemUserId00='10762A09-58F6-E311-B866-6C3BE5A80FD8',@ObjectTypeCode0=4200,@buId00='6E9336D9-BDCF-E311-B03A-78E7D162CED1'
Next post in this blog series: Other Considerations. 

Comments

*This post is locked for comments