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%'
,datediff(ss,EvaluationStartTime,LastRefreshTime) as 'Evaluation time in seconds'
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)


Leave a Reply

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

You are commenting using your 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