Why you should not like “like”

So for the past two days I have been checking and triple checking the Configuration Manager environment I support at work. Nothing like a hurricane being labeled “worst case” storm to make you shake the dust of the DR plans. After all the backup checks and distribution point health and content validations, I started looking and the performance of various components. Overall nothing major found but while checking the collection evaluations I did find a few collections that stood out for poor performance. Only one was real nasty and over 2 minutes. The collection is not very large in terms of members but the query populating it needed a little work. So before I dig into the details, you may like to know how to identify the issue. You can find all the info you need in the “colleval.log”.  If you use a little googlefu there are some good tips on how to parse the log with powershell and identify your troublemakers. Or you can use the Collection Evaluation Viewer from the System Center 2012 R2 Toolkit. If you have never used it before The Config Ninja has a great post walking you through it and some reports to display the same info.

With the Collection Evaluation Viewer you can use the run time to identify collections that need some review. When you identify a collection to review open the properties and look at the membership rules.  Here is an example of a collection query that was running longer then it should.

In some environments this may complete in just a few seconds but it was taking over 2 minutes for me. On the whole this is a fairly normal requirement for a deployment. All computers in a location with Software X installed. But the database has to get all of the system records in the location and then check all of the product names it has reported and check to see if the name is “Like” the value in the query. Now there is nothing wrong with Like and there are lots of cases where you must use it. But you have to understand that it is a more expensive cost to SQL queries using them. So to check out what the possible returns were and what the wild cards were allowing the query to collect I queried the view in sql.

And the query returned a single product name of  SAP GUI for Windows

So to solve this query’s performance issue, I just switched to = and the collection evaluation run time went down to 3.5 seconds

Now that the longest running evaluation was resolved there where a couple of collections that where taking 15 – 20 seconds to complete. Not terrible but not good either, as I looked through them I found a couple of things to share. First up is another collection for computers with a specific type of software installed.

So I go to SQL and check how many Display names are returned by the wild card query and get back two. So this time changing to a “in list” query reduced collection evaluation time.

One thing to make note of is you need to consider is if the values being returned are going to change often and will you know about the changes. But in general I would use the original query with an ad-hoc query or a report. The explicit values for the collection membership query are appropriate because of the impact to the collection evaluation process.

For the last example I am going to use a query that need to use like.  This query is evaluating the computer name and the author needed to include systems with a specific range of ending values and a specific character starting the computer name. Along with a few exclusions.

Right away we know the original author did not understand WQL operators.  By using the correct operators when you must use like, the query is simplified and performs much better.

Evaluating for single characters with and underscore “_” is quicker then using the percent “%” for any and all character combinations. If you need to query for a specific number of any characters use multiple underscores. Specifying the range allows the query to be much shorter and simpler.

 

2 thoughts on “Why you should not like “like”

  1. have you compared performance of the query when you don’t join sms_r_system with sms_g_system_add_remove_programs? Instead of the join look for resourceID IN or NOT IN the set of computers with the software you are looking for. For example:
    SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client
    from SMS_R_System
    where SMS_R_SYSTEM.ResourceID in (Select SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID
    where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE “ODBC Driver for Teradata 14”)

    The join can be expensive because you are creating a much larger data set to then filter.

    1. I have not because these are build by the query wizard within the console. But you are correct and if you know the data you can create a better performing query then the wizard.
      Thanks for the tip

Leave a Reply

Your email address will not be published. Required fields are marked *