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.

 

If you are a System Administrator, or have global access to the entity you are trying to query, the RetrieveMultiple statement does not need to take security into consideration.  In this scenario, the query is very straight forward.  Below is an example of this type of query for Activity records:

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 = 1)) order by
 "activitypointer0".ScheduledEnd asc
, "activitypointer0".ActivityId asc

Tuning the Admin Query

One thing to keep in mind is that the basics of the above query are used in every RetrieveMultiple query, we simply inject the Security checks into it.  Thus, if the admin query is not performing optimally, non-admins are going to see similar performance problems.  Because of this, it is recommended to performance tune the admin query.
 
While SQL, as well as the Database Engine Tuning Advisory (DTA), may not provide a recommendation here for indexing, we can create an index that will improve the performance of the query.  The performance gain may not be immediately noticeable for admins if the query is already running fast, but once security is injected and more records need to be processed, the performance tuning done here will prove to be beneficial. 

Sample Index

 

The following index can be added here to cover the above query.  In my example test environment, this reduced the read count on ActivityPointerBase by 96%.
 
create nonclustered index [activityperftest] on ActivityPointerBase
(ScheduledEnd,ActivityId,IsRegularActivity,OwnerId)
INCLUDE (Subject,PriorityCode,Regardingobjectid,activitytypecode,statecode,scheduledstart,instancetypecode,regardingobjectidyominame,regardingobjecttypecode,regardingobjectidname)
 
The method used to develop this index is as follows.  The indexed columns need to come from the Order By, Where, and Join items of the query, with the Order By coming first.  This is how the indexed columns were determined.  Next, any column in the view should be added as an Include column, unless it is already in the index as an indexed column. 
 
 Next post in this blog series: EnableRetrieveMultipleOptimization.