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)
