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

Question

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

You need to POST the EXECUTION PLAN of the query. Otherwise, it's almost impossible to get proper help. — Pablo Santa Cruz, Aug 09 '16 at 15:07
You may also want to post this question to http://codereview.stackexchange.com/questions/tagged/sql-server — S3S, Aug 09 '16 at 15:10
You have a LOT of thing here that would greatly improve the performance. You have the same subqueries over and over, these would be better as a cte...or better yet redesign your query to not need all those subqueries. You also appear to be heavily using views. Are those views nested? As in one view has a query that in turn selects from another view. You have columns named guid which appear to be primary keys. Are those also the clustered indexes? This could end up being a really long process to fix. — Sean Lange, Aug 09 '16 at 15:25
The series of NOT IN at the end are all doing very similar things and can obviously be better arranged to I think just one NOT IN and suitably bracketed logic for text compares- that may give no payback though if the optimizer already kicked in on the duplication — Cato, Aug 09 '16 at 15:30

score 0 · Answer 1 · answered Aug 09 '16 at 15:33

Remove order by from end of the query and sort it if you need in your application.
You Should rewrite this query if you can because it can't use indexes on your joins -> SQL engine can not use indexes when you using filters like 'USSF%'.
Create indexes on FilterName if you don't have any.

score 0 · Accepted Answer · answered Aug 09 '16 at 15:46

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

Thanks for the idea, using this approach did the trick. Thank you very much! — user3009669, Aug 11 '16 at 17:49
@user3009669 If this answer helped, please can you mark it as accepted? — ste-fu, Aug 12 '16 at 10:55

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

2 Answers2