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
See Question&Answers more detail:os