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

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

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!

RBA Queries - Computer Software Update Details by Classification

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 Role-Based Administration (RBA) Queries in SQL Server Management Studio (SSMS) blog post for more details.

RBA and Non-RBA Queries - Test Results

Don’t believe me? Try it for yourself!

RBA Query

Declare @UserSIDs as nvarchar(250) = ‘disabled’

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.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

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

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

 

 

Comments
pingbacks / trackbacks
  • […] Unfortunately, this is a bit of a long story. I was updating a report within Enhansoft Reporting called, Computer Software Update Details by Classification, because I found out that Microsoft had tweaked the SQL Server performance of Role-Based Administration (RBA) functions. These functions now only take a few seconds versus a few minutes! You can read more about this in my blog post, “RBA and Non-RBA Queries: When is Slower Actually Faster?” […]

Leave a Comment