java 优化查询_如何优化查询？

我编写了这段代码，找到了down节点并计算了上下时间 . 这段代码有效，但我想知道其他任何方式或优化此代码？计算停机时间的最佳方法是什么？有没有办法(交互方式)用户可以输入日期和时间间隔？

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