hi All,
I have setup a SQL 2005 reporting service and i would like to run a report for top 10 app per node per month. I have a query working but It takes 3 minutes to finish and CPU( 8 cores) hits almost 100% during that period. the query as below, could any help to customize the query? appreciate.
SELECT
CONVERT(DateTime,
LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
101) AS SummaryMonth,
Nodes.Caption AS NodeName,
NetflowApplicationSummary.AppName AS Application_Name,
NetflowApplicationSummary.Port AS Port_Number,
ROUND(SUM(NetflowApplicationSummary.TotalBytes)/1000000000,2) AS SUM_of_Bytes_Transferred
FROM
NetflowApplicationSummary INNER JOIN Nodes ON (NetflowApplicationSummary.NodeID = Nodes.NodeID)
WHERE NetflowApplicationSummary.AppName IN
(SELECT TOP 10
NetflowApplicationSummary.AppName AS Application_Name
FROM
NetflowApplicationSummary INNER JOIN Nodes ON (NetflowApplicationSummary.NodeID = Nodes.NodeID)
WHERE
( DateTime BETWEEN
CONVERT(DateTime,LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),101)
AND
DateAdd("month",1, CONVERT(DateTime,LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),101) )
)
AND
(
(Nodes.Caption LIKE '%RouterA%')
)
GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
NetflowApplicationSummary.AppName
ORDER BY CONVERT(DateTime,LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),101) ASC, SUM(NetflowApplicationSummary.TotalBytes) DESC
)
AND
( DateTime BETWEEN 39857 AND 39947 )
AND
(
(Nodes.Caption LIKE '%HTRAND%')
)
GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.Caption, NetflowApplicationSummary.AppName, NetflowApplicationSummary.Port
ORDER BY SummaryMonth ASC, SUM_of_Bytes_Transferred
