RBA and Non-RBA Queries: When is Slower Actually Faster?

Last updated on August 6th, 2022 at 04:02 pm

Up until now, my experience working with SCCM’s Role-Based Administration (RBA) queries showed them to be slower than the normal SQL Server views. My thoughts changed, however, when I recently turned my attention to one of Enhansoft’s Software Update (SU) reports. Could RBA queries be faster than non-RBA queries? If RBA queries were faster, it just didn’t make any sense.

Software Update Reports

Up until now, my experience working with SCCM’s Role-Based Administration (RBA) queries showed them to be slower than the normal SQL Server views. My thoughts changed, however, when I recently turned my attention to one of Enhansoft’s Software Update (SU) reports. Could RBA queries be faster than non-RBA queries? If RBA queries were faster, it just didn’t make any sense.

cls.CategoryInstanceName as ‘UpdateClassification’,

ui.Title,

ui.BulletinID,

ui.ArticleID,

(case when ctm.ResourceID is not null then ‘*’ else ” end) as ‘Targeted’,

(case when css.Status=3 then ‘*’ else ” end) as ‘Installed’,

(case when css.Status=2 then ‘*’ else ” end) as ‘IsRequired’,

cdl.Deadline as ‘Deadline’,

ui.CI_UniqueID as ‘UniqueUpdateID’,

ui.InfoURL as ‘InformationURL’

from

dbo.v_UpdateComplianceStatus css

inner join dbo.v_UpdateInfo ui on ui.CI_ID=css.CI_ID

inner join dbo.v_CICategoryInfo_All vnd on vnd.CI_ID=ui.CI_ID and vnd.CategoryTypeName=’Company’

inner join dbo.v_CICategoryInfo_All cls on cls.CI_ID=ui.CI_ID and cls.CategoryTypeName=’UpdateClassification’

left outer join dbo.v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = css.ResourceID

outer apply (

select

Deadline=min(a.EnforcementDeadline)

from

dbo.v_CIAssignment a

join dbo.v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID and atc.CI_ID=css.CI_ID

) cdl

/* where

css.ResourceID = @RscID

and (@Vendor = ” or vnd.CategoryInstanceName = @Vendor)

and (@UpdateClass = ” or cls.CategoryInstanceName = @UpdateClass) */

order by

If you have any questions about RBA and non-RBA queries, please feel free to contact me @GarthMJ.

Software Update Reports

I think we can all agree that SU reports are a bit slow in SCCM regardless of whether you are using RBA or non-RBA queries. However, I wanted to squeeze out every second to make Enhansoft Reporting’s Computer Software Update Details by Classification report as fast as possible!

I started my review by first looking at the built-in SCCM report, Compliance 5 – Specific computer and its SQL Server query. I edited the SQL Server query to show, “all computers,” and to also display what SUs were applied to each computer. How did I do this? I commented out the Where section of the SQL Server query. I then ran the query in SQL Server Management Studio (SSMS).

Imagine my surprise when the RBA query took 5 seconds to complete. Under the assumption that non-RBA queries were faster, I converted the query, ran it, and then stared in disbelief. The non-RBA query took over 3 minutes LONGER! Why?! I ran this test over and over again, and each time the results were the same.

RBA Reporting Feature

I know from talking to the SCCM team that they work diligently on speeding up reporting times. In this case they clearly optimized the RBA function to squeeze every second out of every query.

What does this mean? First, it means that if you used only non-RBA SQL Server views in your custom reports, like most people do, those views are actually slower than the RBA versions of the same query!

Second, it means that it is time for you to review your custom reports and adjust your queries to leverage the RBA function. Or, at the very least, you should check the speed of the queries.

What was the end result of my review and update of Enhansoft Reporting’s Computer Software Update Details by Classification report? A significant decrease in execution time for both the RBA and non-RBA report versions!

Test RBA and Non-RBA Queries

Want to see the query time difference for yourself? Run the following two queries in SSMS to see how long it takes within your own environment. Below are my results.

Also, don’t forget that you can test this as a regular user by editing the UserSIDs value. Review the Test Your

Non-RBA Query

Declare @locale as nvarchar(250) = 'en-us'
Declare @lcid as int set @lcid = dbo.fn_LShortNameToLCID(@locale)

select
	vnd.CategoryInstanceName as 'Vendor', 
	cls.CategoryInstanceName as 'UpdateClassification',
	ui.Title,
	ui.BulletinID, 
	ui.ArticleID, 
	(case when ctm.ResourceID is not null then '*' else '' end) as 'Targeted', 
	(case when css.Status=3 then '*' else '' end) as 'Installed', 
	(case when css.Status=2 then '*' else '' end) as 'IsRequired', 
	cdl.Deadline as 'Deadline', 
	ui.CI_UniqueID as 'UniqueUpdateID', 
	ui.InfoURL as 'InformationURL' 
from 
	dbo.v_UpdateComplianceStatus css 
	inner join dbo.v_UpdateInfo ui on ui.CI_ID=css.CI_ID 
	inner join dbo.v_CICategoryInfo_All vnd on vnd.CI_ID=ui.CI_ID and vnd.CategoryTypeName='Company'
	inner join dbo.v_CICategoryInfo_All cls on cls.CI_ID=ui.CI_ID and cls.CategoryTypeName='UpdateClassification'
	left outer join dbo.v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = css.ResourceID
	outer apply (
		select 
			Deadline=min(a.EnforcementDeadline) 
		from 
			dbo.v_CIAssignment a 
			join dbo.v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID and atc.CI_ID=css.CI_ID
	) cdl
/* where 
	css.ResourceID = @RscID
	and (@Vendor = '' or vnd.CategoryInstanceName = @Vendor) 
	and (@UpdateClass = '' or cls.CategoryInstanceName = @UpdateClass) */
order by 
	ui.Title

RBA Query

Declare @locale as nvarchar(250) = 'en-us'
Declare @lcid as int set @lcid = dbo.fn_LShortNameToLCID(@locale)
Declare @UserSIDs as nvarchar(250) = 'disabled'

select
	vnd.CategoryInstanceName as 'Vendor', 
	cls.CategoryInstanceName as 'UpdateClassification',
	ui.Title,
	ui.BulletinID, 
	ui.ArticleID, 
	(case when ctm.ResourceID is not null then '*' else '' end) as 'Targeted', 
	(case when css.Status=3 then '*' else '' end) as 'Installed', 
	(case when css.Status=2 then '*' else '' end) as 'IsRequired', 
	cdl.Deadline as 'Deadline', 
	ui.CI_UniqueID as 'UniqueUpdateID', 
	ui.InfoURL as 'InformationURL' 
from 
	dbo.fn_rbac_UpdateComplianceStatus(@UserSIDs) css
	inner join dbo.fn_rbac_UpdateInfo(@lcid, @UserSIDs) ui on ui.CI_ID=css.CI_ID
	inner join dbo.fn_rbac_CICategoryInfo_All(@lcid, @UserSIDs) vnd on vnd.CI_ID=ui.CI_ID and vnd.CategoryTypeName='Company'
	inner join dbo.fn_rbac_CICategoryInfo_All(@lcid, @UserSIDs) cls on cls.CI_ID=ui.CI_ID and cls.CategoryTypeName='UpdateClassification'
	left outer join dbo.fn_rbac_CITargetedMachines(@UserSIDs) ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = css.ResourceID
	outer apply (
		select
			Deadline=min(a.EnforcementDeadline)
		from
			dbo.fn_rbac_CIAssignment(@UserSIDs) a
			join dbo.fn_rbac_CIAssignmentToCI(@UserSIDs) atc on atc.AssignmentID=a.AssignmentID and atc.CI_ID=css.CI_ID
	) cdl
/* where 
	css.ResourceID = @RscID
	and (@Vendor = '' or vnd.CategoryInstanceName = @Vendor) 
	and (@UpdateClass = '' or cls.CategoryInstanceName = @UpdateClass) */
order by 
	ui.Title

Finally, don’t forget that you can subscribe to my RRS feed to stay on top of the latest trips and tricks. Additionally, If you have any questions, please feel free to touch base @Garthmj.