No hard proof, but for us, usually the list values query is faster in both WQL and SQL.
Daniel Ratliff From: [email protected] [mailto:[email protected]] On Behalf Of Garth Jones Sent: Tuesday, April 04, 2017 7:32 AM To: [email protected] Subject: [mssms] RE: SQL Query question Hi Henry, what exactly is the goal? Is it to find all computers without .net 4.5.2? From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of [email protected]<mailto:[email protected]> Sent: April 4, 2017 6:16 AM To: [email protected]<mailto:[email protected]> Subject: [mssms] SQL Query question Hello SQL Guru's We're having a discussion with some Admin's in Europe and I would like an opinion on 2 Collection queries looking for the same result: Which one is more efficient? Collection Query #1: 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 where SMS_R_System.ResourceId not in ( select SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName in ("Microsoft .NET Framework 4.5.2","Microsoft .NET Framework 4.6.1","Microsoft .NET Framework 4.6","Microsoft .NET Framework 4.6.2") ) ================================================================= Collection Query #2: 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 WHERE sms_r_system.resourceid NOT IN (SELECT sms_r_system.resourceid FROM sms_r_system INNER JOIN sms_g_system_installed_software ON sms_g_system_installed_software.resourceid = sms_r_system.resourceid WHERE sms_g_system_installed_software.arpdisplayname = "microsoft .net framework 4.5.2" or sms_g_system_installed_software.arpdisplayname = "microsoft .net framework 4.6.1" or sms_g_system_installed_software.arpdisplayname = "microsoft .net framework 4.6" or sms_g_system_installed_software.arpdisplayname = "microsoft .net framework 4.6.2" ====================================================================== A DCM has also been mentioned to achieve the same result. Comments or recommendations? Thanks Best regards / Cordialement Henry E. WILSON Technical Expert Engineer Information Technology & Solutions Infrastructure Management Automation Services 55 Corporate Drive Bridgewater, NJ 08807 TEL.: +1 908 981 3288 CELL.: +1 610 724 1169 FAX: +1 908 635 5858 [cid:[email protected]] System Center Technical Blog<http://wssamer.sanofi.com/ws/AutomationCenterProvTools/SCTechBlog/Lists/Posts/AllPosts.aspx> (for internal use only) SCCM 2012 Dashboard<http://xspw10w201w/Reports/Pages/Report.aspx?ItemPath=%2fUnRestricted+Reports%2fInfrastructure+Health+Status%2fConfigMgr+2012+Dashboard> (for internal use only) Provisioning Tools & Lifecycle Sharepoint Site<http://wssamer.sanofi.com/ws/AutomationCenterProvTools/Documents/Forms/AllItems.aspx> (for internal use only) The information transmitted is intended only for the person or entity to which it is addressed and may contain CONFIDENTIAL material. If you receive this material/information in error, please contact the sender and delete or destroy the material/information.
