这个查询需要永久运行，我该如何编写它才能运行得更快？

问题描述：

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

您需要发布查询的执行计划。否则，几乎不可能获得适当的帮助。 –

您是否设置了正确的索引并尝试使用EXPLAIN？ –

你可能也想发布这个问题http://codereview.stackexchange.com/questions/tagged/sql-server – scsimon

答

考虑使用Common Table Expressions, Temp Tables or Table Variables进行重复操作，并假设各种Guid是主键，请尝试使用它们的值而不是显示或过滤器名称列进行选择。

像这样的东西可能：

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

也许不是完美的，但我希望你的想法

感谢您的想法，使用这种方法做了伎俩。非常感谢你！ – user3009669

@ user3009669如果这个答案有帮助，请把它标记为已接受？ –

答

从查询的结束
删除订单的，如果你在您的应用程序需要排序。
如果可以，则应该重写此查询，因为它无法在您的连接上使用索引 - >当您使用像“USSF％”这样的过滤器时，SQL引擎无法使用索引。
如果您没有任何，请在FilterName上创建索引。

这个查询需要永久运行，我该如何编写它才能运行得更快？

相关推荐