I had one of our SQL DBAs write a slick SQL query to grab the interface errors for the past month and give me the average and peak. (It fulfils a customer reporting requirement). However, when I past the SQL query into a new advanced SQL report the query just runs and run, never returning data. If I run it directly against the database using the SQL Server management tools, it runs in under 30 seconds.
Any ideas? I've posted the SQL query below.
declare @FirstDay datetime
declare @LastDay datetime
declare @customer varchar(30)
--Set the customer who want.
set @customer = 'customer name removed'
set @FirstDay = convert(varchar,DATEADD(MONTH,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,getdate()),0)),101)
set @LastDay = convert(varchar,DATEADD(MONTH,DATEDIFF(MONTH,0,getdate()),-1),101)
SELECT TOP 10000 Nodes.NodeID as NodeId,
Nodes.SysName AS BoxName,
Nodes.CustomerName as Customer,
InterfaceErrors_Detail.InterfaceID AS InterfaceID,
Nodes.Caption AS NodeName,
Nodes.VendorIcon AS Vendor_Icon,
ROUND(AVG(InterfaceErrors_detail.Out_Discards),2) AS AVERAGE_of_OutDiscardsMonth,
MAX(InterfaceErrors_detail.Out_Discards) AS MAX_of_OutDiscardsMonth,
ROUND(AVG(InterfaceErrors_detail.in_Discards),2) AS AVERAGE_of_InDiscardsMonth,
MAX(InterfaceErrors_detail.in_Discards) AS MAX_of_InDiscardsMonth
INTO #Nodes_table
FROM
(Nodes INNER JOIN InterfaceErrors_detail ON (Nodes.NodeID = InterfaceErrors_detail.NodeID))
INNER JOIN InterfaceTraffic ON (InterfaceErrors_Detail.InterfaceID = InterfaceTraffic.InterfaceID
AND InterfaceTraffic.NodeID = Nodes.NodeID)
where InterfaceErrors_detail.DateTime >= @FirstDay
and InterfaceErrors_detail.DateTime <= @LastDay
and Nodes.CustomerName = @customer
GROUP BY
Nodes.NodeID, InterfaceErrors_Detail.InterfaceID, Nodes.Caption, Nodes.VendorIcon, Nodes.SysName, Nodes.CustomerName
select
#Nodes_table.NodeID, #Nodes_table.Customer, #Nodes_table.BoxName, #Nodes_table.InterfaceID, #Nodes_table.Vendor_Icon,
Interfaces.Caption, #Nodes_table.AVERAGE_of_OutDiscardsMonth,#Nodes_table.MAX_of_OutDiscardsMonth,
#Nodes_table.AVERAGE_of_InDiscardsMonth,#Nodes_table.MAX_of_InDiscardsMonth
from
#Nodes_table, Interfaces
where
Interfaces.interfaceid = #Nodes_table.InterfaceID
order by
NodeID, InterfaceID
drop table #Nodes_table