优化子查询性能

Question

我的第一个表查询大约需要40秒，创建了超过80,000行。我想按站点、顺序、总数和任何操作系统版本来获取Windows 7应用程序的数量。

这些子查询可以工作，但它们当然会大大减慢处理速度。它运行了3.5个小时。

有没有更有效的方法来做到这一点？

输出：

SoftwareName    Sequence    Site    Win7/site       Win7Installs/seq    TotWin7apps TotalInstalls
Adobe Acrobat       1       BKN         1                   5               626         7854
AutoCAD LT          1       BKN         1                   1               3           15
Adobe Acrobat       1       CTW         4                   5               626         7854
Adobe Captivate     1       CTW         1                   1               8           60

查询：

WITH PCapps AS (
SELECT DISTINCT
    Computer,
    Sequence,
    Site,
    SoftwareName,
    OS
FROM table
)

SELECT DISTINCT
    SoftwareName,
    Sequence,
    Site,
    (SELECT COUNT(p1.SoftwareName) FROM PCapps p1 WHERE p1.SoftwareName = pc.Softwarename AND OS = 'Windows 7 Enterprise' AND p1.Site = pc.Site) as 'Win7/site',
    (SELECT COUNT(p1.SoftwareName) FROM PCapps p1 WHERE p1.SoftwareName = pc.Softwarename AND OS = 'Windows 7 Enterprise' AND p1.Sequence = pc.Sequence) as 'Win7Installs/seq',
    (SELECT COUNT(p2.SoftwareName) FROM PCapps p2 WHERE p2.SoftwareName = pc.Softwarename AND OS = 'Windows 7 Enterprise') as TotWin7apps,
    (SELECT COUNT(p3.SoftwareName) FROM PCapps p3 WHERE p3.SoftwareName = pc.SoftwareName) as TotalInstalls 
    FROM PCapps pc

**更新：

使用@Jason卡特提供的技巧，我创建了几个#temp表并加入其中。结果在不到一分钟的时间内运行得非常快(不会比初始查询长多少)。这种方法与@Jason卡特的解决方案略有不同，因为我遵循他的初始技巧创建了#temp表。我创建了几个#temp表，每个表都包含COUNT()。

SELECT DISTINCT
    Computer,
    Sequence,
    Site,
    SoftwareName,
    OS
INTO #PCapps
      FROM TABLE    

SELECT
    SoftwareName,
    Site,
    COUNT(SoftwareName) AS [SiteInstalls]
INTO #SiteInstalls
FROM #PCapps 
    WHERE OS = 'Windows 7 Enterprise'
    GROUP BY Site, SoftwareName

SELECT
    SoftwareName,
    Sequence,
    COUNT(SoftwareName) AS [SeqInstalls]
INTO #SeqInstalls
FROM #PCapps
    WHERE OS = 'Windows 7 Enterprise'
    GROUP BY Sequence, SoftwareName

SELECT
    SoftwareName,
    COUNT(SoftwareName) AS [Win7Installs]
INTO #Win7Installs
FROM #PCapps
    WHERE OS = 'Windows 7 Enterprise'
    GROUP BY SoftwareName

SELECT
    SoftwareName,
    COUNT(SoftwareName) AS [AppInstalls]
INTO #AppInstalls
FROM #PCapps
    GROUP BY SoftwareName   

SELECT
    pc.SoftwareName,
    pc.Sequence,
    pc.Site,
    sit7.SiteInstalls,
    seq7.SeqInstalls,
    w7.Win7Installs,
    ai.AppInstalls
    FROM #PCapps pc
    LEFT OUTER JOIN #SiteInstalls sit7 ON sit7.SoftwareName = pc.SoftwareName AND sit7.Site = pc.Site
    LEFT OUTER JOIN #SeqInstalls  seq7 ON seq7.SoftwareName = pc.SoftwareName AND seq7.Sequence = pc.Sequence
    LEFT OUTER JOIN #Win7Installs w7   ON   w7.SoftwareName = pc.SoftwareName
    LEFT OUTER JOIN #AppInstalls ai    ON   ai.Softwarename = pc.SoftwareName

DROP TABLE #PCapps
DROP TABLE #SiteInstalls
DROP TABLE #SeqInstalls
DROP TABLE #Win7Installs
DROP TABLE #AppInstalls

Answer 1

我会先在一个临时表中运行子查询，以收集您的计数，然后提取您的总计数。按照您设置查询的方式，它将为PCapps的每一行运行一次这些子查询，这就是为什么它花费了这么长时间。

尝试如下所示：

declare @t table (Computer varchar(10), Sequence int, Site varchar(3), SoftwareName varchar(20), OS varchar(20) )

insert into @t values
 ('C1',1,'BKN','Adobe Acrobat','Win7')
,('C2',1,'BKN','Adobe Acrobat','Win7')
,('C3',1,'BKN','Adobe Acrobat','Win7')
,('C1',1,'BKN','AutoCAD LT ','Win7')
,('C3',1,'BKN','AutoCAD LT ','Win7')
,('B1',2,'CTW','Adobe Acrobat','Win7')
,('B2',2,'CTW','Adobe Acrobat','Win7')
,('B3',3,'CTW','Adobe LT','Win7')
,('B4',3,'CTW','Adobe Acrobat','Win7')
,('A1',2,'CTW','Adobe Acrobat','Win7')
,('A2',2,'CTW','Adobe LT','Win7')
,('A3',2,'CTW','Adobe Acrobat','Win7')
,('X4',3,'CTW','Adobe Acrobat','WinXP')
,('X1',2,'CTW','Adobe Acrobat','WinXP')
,('X2',2,'CTW','Adobe LT','WinXP')
,('X3',2,'CTW','Adobe Acrobat','WinXP')
,('A4',2,'CTW','Adobe Acrobat','Win7');

SELECT 
    Site,
    OS,
    SoftwareName, 
    Sequence,
    COUNT(SoftwareName)  as 'Count'
into #SiteSpecific
FROM @t
group by Site, OS, SoftwareName, Sequence;


WITH PCapps AS (
SELECT DISTINCT
    Computer,
    Sequence,
    Site,
    SoftwareName,
    OS
FROM @t
)
SELECT 
    DISTINCT
    PC.SoftwareName,
    PC.Sequence,
    PC.Site,
    PerSeq.[Count] as 'Win7Installs/seq',
    PerSite.[Count] as TotWin7apps,
    total.[Count] as TotalInstalls 
FROM PCapps pc
inner join (select SoftwareName, Sequence, OS, sum([Count]) [Count] from #SiteSpecific group by SoftwareName, Sequence, OS) PerSeq 
    on PerSeq.SoftwareName = pc.Softwarename 
    AND PerSeq.Sequence = pc.Sequence
    AND PerSeq.OS = pc.OS
inner join (select SoftwareName, Site, OS, sum([Count]) [Count] from #SiteSpecific group by SoftwareName, Site, OS) PerSite 
    on PerSite.SoftwareName = pc.Softwarename 
    AND PerSite.Site = pc.Site
    AND PerSite.OS = pc.OS
inner join (select SoftwareName, sum([Count]) [Count] from #SiteSpecific group by SoftwareName) Total 
    on Total.SoftwareName = pc.Softwarename 
where Pc.OS='Win7'
order by SoftwareName, Sequence, Site

Answer 2

我真的很惊讶..。优化器利用并行性使子查询非常高效。我用120K条记录填充了一个虚拟表，并尝试了下面的方法。第三个查询只比第一个(您的)效率稍高一些，但代价是更复杂。我会让你的保持原样。也许有一个更好的解决方案，但你的方案在我看来已经足够好了。您的系统处理80K行需要多长时间？

WITH PCapps2 AS (
SELECT DISTINCT
    Computer,
    Sequence,
    Site,
    SoftwareName,
    OS
FROM pcapps
)
SELECT DISTINCT
    SoftwareName,
    Sequence,
    Site,
    (SELECT COUNT(p1.SoftwareName) FROM PCapps2 p1 WHERE p1.SoftwareName = pc.Softwarename AND OS = 'Windows 7 Enterprise' AND p1.Site = pc.Site) as 'Win7/site',
    (SELECT COUNT(p1.SoftwareName) FROM PCapps2 p1 WHERE p1.SoftwareName = pc.Softwarename AND OS = 'Windows 7 Enterprise' AND p1.Sequence = pc.Sequence) as 'Win7Installs/seq',
    (SELECT COUNT(p2.SoftwareName) FROM PCapps2 p2 WHERE p2.SoftwareName = pc.Softwarename AND OS = 'Windows 7 Enterprise') as TotWin7apps,
    (SELECT COUNT(p3.SoftwareName) FROM PCapps2 p3 WHERE p3.SoftwareName = pc.SoftwareName) as TotalInstalls 
    FROM PCapps2 pc

;WITH PCapps2 AS (
SELECT DISTINCT
    Computer,
    Sequence,
    Site,
    SoftwareName,
    OS
FROM pcapps
)
SELECT DISTINCT
    SoftwareName,
    Sequence,
    Site,
    COUNT(case when os = 'Windows 7 Enterprise' then 1 end) over (partition by pc.Softwarename,pc.Site) as 'Win7/site',
    COUNT(case when os = 'Windows 7 Enterprise' then 1 end) over (partition by pc.Softwarename,pc.Sequence) as 'Win7Installs/seq',
    COUNT(case when os = 'Windows 7 Enterprise' then 1 end) over (partition by pc.Softwarename) as TotWin7apps,
    COUNT(*) over (partition by pc.Softwarename) as TotalInstalls
    FROM PCapps2 pc

;WITH PCapps2 AS (
SELECT DISTINCT
    Computer,
    Sequence,
    Site,
    SoftwareName,
    OS
FROM pcapps
), a as (
    select softwarename, site, COUNT(*) as 'Win7/site'
    from pcapps2
    where os = 'Windows 7 Enterprise'
    group by softwarename, site
), b as (
    select softwarename, sequence, COUNT(*) as 'Win7Installs/seq'
    from pcapps2
    where os = 'Windows 7 Enterprise'
    group by softwarename, sequence
), c as (
    select softwarename, COUNT(case when os = 'Windows 7 Enterprise' then 1 end) as TotWin7apps, COUNT(*) as TotalInstalls
    from pcapps2
    group by softwarename
), d as (
    select distinct softwarename, sequence, site
    from    pcapps2
)
select d.*, isnull(a.[Win7/site], 0) as [Win7/site], isnull(b.[Win7Installs/seq],0) as [Win7Installs/seq] 
        , isnull(c.TotWin7apps,0) as TotWin7apps, isnull(c.TotalInstalls, 0) as TotalInstalls  
from    d
        left join a on d.softwarename = a.softwarename and d.site = a.site 
        left join b on d.softwarename = b.softwarename and d.sequence = b.sequence 
        left join c on d.softwarename = c.softwarename 

第一个查询:表'pcapps‘。扫描计数15、逻辑读取6630、物理读取0、预读读取0、lob逻辑读取0、lob物理读取0、lob预读读取0。表'Worktable‘。扫描计数为0、逻辑读取为0、物理读取为0、预读为0、lob逻辑读取为0、lob物理读取为0、lob预读为0。

第二个查询:表'pcapps‘。扫描计数3、逻辑读取1326、物理读取0、预读读取0、lob逻辑读取0、lob物理读取0、lob预读读取0。表'Worktable‘。扫描计数18、逻辑读取1983591、物理读取0、预读读取0、lob逻辑读取0、lob物理读取0、lob预读读取0。

第三个查询:表'pcapps‘。扫描计数12、逻辑读取5304、物理读取0、预读读取0、lob逻辑读取0、lob物理读取0、lob预读读取0。表'Worktable‘。扫描计数为0、逻辑读取为0、物理读取为0、预读为0、lob逻辑读取为0、lob物理读取为0、lob预读为0。