Search code examples
sqlsql-serversymantecaltiris

This query takes forever to run, how can I write it so it runs quicker?

select distinct v1.name 'Machine Name', v1.[user] 'Primary User', CASE

    When v1.Guid in (select distinct v1.guid from vComputer v1
inner join Inv_AddRemoveProgram t1 on v1.Guid = t1._ResourceGuid
inner join Inv_OfficeSuiteVersions3 t2 on v1.guid = t2._ResourceGuid
where t1.DisplayName = 'Microsoft Office Professional Plus 2007' and t1.InstallFlag = '1'
and t2.Outlook2007Ver <> 'Not Present')
    Then 'Microsoft Office Professional Plus 2007'
    When v1.Guid in (select distinct v1.guid from vComputer v1
inner join Inv_AddRemoveProgram t1 on v1.Guid = t1._ResourceGuid
inner join Inv_OfficeSuiteVersions3 t2 on v1.guid = t2._ResourceGuid
where t1.DisplayName = 'Microsoft Office Professional Plus 2010' and t1.InstallFlag = '1'
and t2.Outlook2010Ver <> 'Not Present')
    Then 'Microsoft Office Professional Plus 2010'
    When v1.Guid in (select distinct v1.guid from vComputer v1
inner join Inv_AddRemoveProgram t1 on v1.Guid = t1._ResourceGuid
inner join Inv_OfficeSuiteVersions4 t2 on v1.guid = t2._ResourceGuid
where t1.DisplayName = 'Microsoft Office Professional Plus 2013' and t1.InstallFlag = '1'
and t2.Outlook2013Ver <> 'Not Present')
    Then 'Microsoft Office Professional Plus 2013'
    When v1.Guid in (select distinct v1.guid from vComputer v1
inner join Inv_AddRemoveProgram t1 on v1.Guid = t1._ResourceGuid
inner join Inv_OfficeSuiteVersions5 t2 on v1.guid = t2._ResourceGuid
where t1.DisplayName like 'Microsoft Office 365 ProPlus%' and t1.InstallFlag = '1'
and Outlook2016Ver <> 'Not Present')
    Then 'Microsoft Office 365 ProPlus' 
    End [Office Version], v2.[Location by Subnet] 'Location'

from vComputer v1

inner join vcomputerlocations v2 on v1.Guid = v2.Guid

and v1.Name like 'USSD%'

and v1.Guid not in (select Guid from CollectionMembership where FilterName = 'Software Delivery Exclusions')

or v1.Name like 'USSF%'

and v1.Guid not in (select Guid from CollectionMembership where FilterName = 'Software Delivery Exclusions')

or v1.Name like 'USSEA%'

and v1.Guid not in (select Guid from CollectionMembership where FilterName = 'Software Delivery Exclusions')

or v1.Name like 'USBES%'

and v1.Guid not in (select Guid from CollectionMembership where FilterName = 'Software Delivery Exclusions')

or v1.Name like 'USCAM%'

and v1.Guid not in (select Guid from CollectionMembership where FilterName = 'Software Delivery Exclusions')

order by 3,4,1
Solution

  • Consider using Common Table Expressions, Temp Tables or Table Variables for the repeated operations, and assuming that the various Guids are the primary keys, try selecting using their values rather than the display or filter name columns.

    Something like this perhaps:

    WITH a AS
(
    SELECT v1.Guid, v1.Name, v1.[user], t1.DisplayName from vComputer v1
    INNER JOIN Inv_AddRemoveProgram t1 on v1.Guid = t1._ResourceGuid
    INNER JOIN Inv_OfficeSuiteVersions3 t2 on v1.Guid = t2._ResourceGuid
    WHERE t1.InstallFlag = '1' 
    AND
    (t1.PrimaryKey = '2007 PrimaryKey Value' AND t2.Outlook2007Ver <> 'Not Present')
    OR
    ((t1.PrimaryKey = '2010 PrimaryKey Value' AND t2.Outlook2010Ver <> 'Not Present') --etc etc
),

b AS
(
    SELECT Guid FROM CollectionMembership WHERE FilterName = 'Software Delivery Exclusions'
) -- would be better to search by primary key

SELECT  a.*, v2.[Location by Subnet] 
FROM a INNER JOIN vcomputerlocations v2 on a.Guid = v2.Guid  
LEFT JOIN b ON a.Guid = b.Guid 
WHERE b.Guid IS NULL

    Might not be perfect, but I hope you get the idea