How can I optimize my query?

Question

I wrote this code that find the down nodes and calculate the up and down hours. This code works but I want to know any other way or optimize of this code? what is the best way to calculate the duration of down time? and Is there any way(interactive way) that user can input the date and time interval?

SELECT  q1.nodeid, q1.VendorIcon, q1.Caption, q1.IP_Address, 
q1.OutageDurationInMinutes,
q2.TimeUp

FROM 
(SELECT 
    Nodes.NodeID AS NodeID, ltrim(rtrim(Nodes.Caption)) Caption, Nodes.VendorIcon,Nodes.IP_Address,
    sum(DATEDIFF(hh, StartTime.EventTime, EndTime.EventTime)) as OutageDurationInMinutes

FROM Events StartTime
Left join Events EndTime On
   EndTime.EventType = '5'  and
   EndTime.NetObjectType = 'N' and
   EndTime.NetworkNode = StartTime.NetworkNode and
   EndTime.EventTime = 
                    (
                       Select 
                          min(EventTime) 
                        from Events 
                        where 
                           EventTime>StartTime.EventTime and 
                           EventType = '5' and
                           NetObjectType = 'N' and
                           NetworkNode = StartTime.NetworkNode
                    )    
INNER JOIN Nodes ON 
   StartTime.NetworkNode = Nodes.NodeID 
WHERE 
   Nodes.Department = '4' AND
   StartTime.EventType = 1 AND 
   StartTime.NetObjectType = 'N'  AND
   StartTime.eventtime between dateadd(M, -1, getdate()) and getdate()

Group by
   Nodes.NodeID,Nodes.Caption, Nodes.VendorIcon,Nodes.IP_Address, Nodes.LastBoot
) q1

INNER JOIN

(SELECT 
      Nodes.NodeID AS NodeID
      ,ltrim(rtrim(Caption)) Caption
      ,VendorIcon
      ,Ip_Address
      ,DateDiff(hour,Nodes.LastBoot,GetDate()) AS HoursUp
      ,CONVERT(VARCHAR(40), DATEDIFF(minute, Nodes.LastBoot, GETDATE())/(24*60))
            + ' days, '
            + CONVERT(VARCHAR(40), DATEDIFF(minute, Nodes.LastBoot, GETDATE())%(24*60)/60)
   + ' hours, and '
   + CONVERT(VARCHAR(40), DATEDIFF(minute, Nodes.LastBoot, GETDATE())%60)
   + ' minutes.' AS TimeUp
FROM [Nodes]
Where 

LastBoot between dateadd(day, -30, getdate()) and getdate()) q2 on q1.NodeID=q2.NodeID

Order by Caption

openshac openshac · Accepted Answer · 2011-05-25T11:02:29

I want to know any other way or optimize of this code?

I would recommend having a look at the query execution plan for you query.

Is there any way(interactive way) that user can input the date and time interval?

You could just determine the values before you run the query and use these parameters in your query (I'm not sure what's calling your query though, is it a stored procedure?)

How can I optimize my query?

1 Answers