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
- 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.
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”
Hi Ben. Sorry your comment was flagged as SPAM. I was cleaning out the SPAM messages when I saw it.
From a performance standpoint there isn’t anything you need to do. However from results standpoint, Your query will ONLY find x64 SW titles. This is because you have an inner join on the x86 view. You need an Left outer join or a subselect to fix this. This blog will get you started.
Also We are having a SQL/Reporting AMA on Jan 2 at 11:30am EST.