Collections that have wildcard “%” prefixes in search strings

 

/*
I find a lot of collection membership rules that people create 
that have wildcards where they are not needed, something like
DisplayName like '%QuickTime%'
This causes the collection evaluator to take longer to evaluate these rules.
I try to find them and when appropriate change them to something like
DisplayName like 'QuickTime%'
*/
SELECT [SiteID]
,[CollectionID]
,[CollectionName]
,datediff(ss,EvaluationStartTime,LastRefreshTime) as 'Evaluation time in seconds'
,[MemberCount]
,[LocalMemberCount]
FROM [dbo].[v_Collections]
where --datediff(MINUTE, DATEADD(HH, DATEDIFF (hh, GETUTCDATE(),GETDATE()), EvaluationStartTime), getdate()) < 5 and SiteID not like 'SMS%' and
[SiteID] in (select CollectionID from v_CollectionRuleQuery where QueryExpression like '%''[%]%')
ORDER BY datediff(ss,EvaluationStartTime,LastRefreshTime)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s