How to Fix a Poorly Written WQL Query

What is a poorly written WQL query? This is a loaded question; you will get lots of answers from different people about how best to write queries. However, what you’ll typically hear is more to do with personal preferences and less about best practices.

In my previous blog post, Configuration Manager Collections and Collection Evaluation Viewer, I talked about why a bad query will slow down collection updates.

Here are some of the worst offenders when it comes to queries:

  • Improper Joins
  • Like
  • Not Like

In most cases if you avoid these offenders the processing time will speed up, which means that there is less overhead on the ConfigMgr site server and SQL server.

Below is the example I gave of a poorly written (and poorly formatted) query which resulted in the dreaded hourglass appearing while the collection tried to update itself.

How to Fix a Poorly Written WQL Query-Bad Query 

The problem with this query is that it has both Like and Not Like within it. Below is how I re-wrote it.

How to Fix a Poorly Written WQL Query-Updated Query

Instead of using Like on the operating system name, I used the system role attribute to find all workstations. You will also see that I changed the Not Like to Not Equal (!=). This will reduce the processing time of the query by not having SQL do a table scan of all data.

Besides removing Like and Not Like, I also formatted the query to make it more readable. I truly dislike it when queries are all massed together and WQL is the worse for that! By default the ConfigMgr console removes all formatting to make it smaller.

How to Fix a Poorly Written WQL Query-Better Query

Now looking at the data within Collection Evaluation Viewer (CEV)* you can see that the Better Query took 1.204 seconds to complete. That is 0.171 seconds faster than the Bad Query (see line above the Better Query) for my lab of 33 computers. This equates to ~12% faster. The execution time is now 0.036 instead of 0.042.

Here’s what the difference in processing time will be for both queries in a 1,000 to 50,000 computer environment.

How to Fix a Poorly Written WQL Query-Processing Time

You can quickly see from the results that the larger your environment, the more important it is to write queries with as few Likes and Not Likes as possible.

If you have any questions, please feel free to contact me @GarthMJ.

* CVE is part of the ConfigMgr Toolkit and can be downloaded here.

Showing 2 comments
  • ben
    Reply

    Out of interest, how can i improve this query ? It was created using the GUI to search in installed programs displayname for x86 and x64

    select 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 inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = “Microsoft Office Professional Plus 2013” or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = “Microsoft Office Professional Plus 2013”

Leave a Comment

Share via
Copy link
Powered by Social Snap