Last updated on August 7th, 2022 at 09:16 pm
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.
WQL Query
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. It resulted in the dreaded hourglass appearing while the collection tried to update itself.
The problem with this query is that it has both Like and Not Like within it. Below is how I re-wrote it.
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.
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.
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.
*In ConfigMgr Current Branch, the Collection Evaluation Viewer (CEV) is available on any primary site server. It’s located under tools\ServerTools and ceviewer.exe is the file name. In ConfigMgr 2012, CEV is part of the ConfigMgr Toolkit and can be downloaded here.