sql server - Optimize performance of sub-queries

Question

My first query from table takes about 40 seconds and creates over 80,000 rows. I want to get the counts of Windows 7 applications by Site, Sequence, Total and any OS version.

These sub-queries work, but of course they slow the process down considerably. It took 3.5 hours to run.

Is there a more efficient way to do this?

Output:

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

Query:

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

** UPDATE:

Using the tips from @Jason Carter, I created a few #temp tables and join them. The result runs very fast in less than a minute (not much longer than the initial query). This method is slightly different than @JasonCarter's solution as I was following his initial tip to create #temp tables. I created several #temp tables, each including the COUNT()s.

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

I would try running the subqueries first into a temp table to gather your counts, then pull your total counts. With the way you have the query setup its going to run each of those subqueries once for every line of PCapps, which is why it is taking so long.

Try something like this:

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