如何优化我的查询？

Question

我编写了这段代码来查找下行节点并计算上行和下行时间。该代码有效，但我想知道该代码的任何其他方式或优化？计算停机时间的最佳方法是什么？用户是否可以通过任何方式（交互方式）输入日期和时间间隔？

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

Answer 1

我想知道任何其他方式或
优化这段代码？

我建议您查看查询的查询执行计划。

有没有什么方法（交互方式）
用户可以输入日期和时间
间隔？

您可以在运行查询之前确定这些值并在查询中使用这些参数（但我不确定是什么在调用您的查询，它是存储过程吗？）