34,576
社区成员
发帖
与我相关
我的任务
分享Sql语句优化,在线等待！
请问以下Sql语句该怎么优化才好？
select OServerProcessUID,ServerProcessName,
ServerProcessID,PublicIP,PublicPort,PrivateIP,PrivatePort,OServerProcessBusinessTypeID,
ServerProcessTypeID,DeviceOnlineCount,DeviceConnectionCount,SoftwareVerNo,IsEnabled,DelFlag,
Description,CreateIP,CreateTime,ModifyIp,ModifyTime
FROM OServerProcess
where 1=1 and IsEnabled=1 and DelFlag=0
and OServerProcessUID in
(select OServerProcessUID from OServerProcessVSProcessGroup where IsEnabled=1 and
DelFlag=0 and OServerProcessGroupUID
in (select OServerProcessGroupUID from ODeviceGroupVSProcessGroup where IsEnabled=1 and
DelFlag=0 and DeviceGroupUID in (select DeviceGroupUID from dbo.ODeviceVSGroup
where 1=1 and IsEnabled=1 and DelFlag=0 and
DeviceUID in ( '82cf5852-45cf-e211-8c19-00163e0071c8'))))
还有,那个sql执行计划怎么看？
select OServerProcessUID,ServerProcessName,
ServerProcessID,PublicIP,PublicPort,PrivateIP,PrivatePort,OServerProcessBusinessTypeID,
ServerProcessTypeID,DeviceOnlineCount,DeviceConnectionCount,SoftwareVerNo,IsEnabled,DelFlag,
Description,CreateIP,CreateTime,ModifyIp,ModifyTime
FROM OServerProcess
where 1=1 and IsEnabled=1 and DelFlag=0
and OServerProcessUID in
(select OServerProcessUID from OServerProcessVSProcessGroup where IsEnabled=1 and
DelFlag=0 and OServerProcessGroupUID
in (select OServerProcessGroupUID from ODeviceGroupVSProcessGroup where IsEnabled=1 and
DelFlag=0 and DeviceGroupUID in (select DeviceGroupUID from dbo.ODeviceVSGroup
where 1=1 and IsEnabled=1 and DelFlag=0 and
DeviceUID in ( '82cf5852-45cf-e211-8c19-00163e0071c8'))))
还有,那个sql执行计划怎么看？
...全文
请发表友善的回复…
发表回复
carlebear 2013-06-08
- 打赏
- 举报
@hdhai9451
这个我已经想出来了！
继续等待更优化的
Andy__Huang 2013-06-08
- 打赏
- 举报
select OServerProcessUID,ServerProcessName,
ServerProcessID,PublicIP,PublicPort,PrivateIP,PrivatePort,OServerProcessBusinessTypeID,
ServerProcessTypeID,DeviceOnlineCount,DeviceConnectionCount,SoftwareVerNo,IsEnabled,DelFlag,
Description,CreateIP,CreateTime,ModifyIp,ModifyTime
FROM OServerProcess a
inner join OServerProcessVSProcessGroup b on a.OServerProcessUID=b.OServerProcessUID and b. IsEnabled=1 and b.DelFlag=0
inner join ODeviceVSGroup c on b.DeviceGroupUID=c.DeviceGroupUID and c.IsEnabled=1 and c.DelFlag=0 and c.DeviceUID='82cf5852-45cf-e211-8c19-00163e0071c8'
where a.IsEnabled=1 and a.DelFlag=0
carlebear 2013-06-08
- 打赏
- 举报
继续等吧,看出否有最优答案
黄_瓜 2013-06-08
- 打赏
- 举报
感觉in的嵌套有点深
可以分步，把in的查询先放到临时表里面，试试下面的
看执行计划ctrl+L
不知道怎么看的百度一下
SELECT OServerProcessGroupUID
INTO #t1
FROM ODeviceGroupVSProcessGroup
WHERE IsEnabled = 1
AND DelFlag = 0
AND DeviceGroupUID IN (SELECT DeviceGroupUID
FROM dbo.ODeviceVSGroup
WHERE 1 = 1
AND IsEnabled = 1
AND DelFlag = 0
AND DeviceUID IN ('82cf5852-45cf-e211-8c19-00163e0071c8'))
SELECT OServerProcessUID
INTO #t2
FROM OServerProcessVSProcessGroup
WHERE IsEnabled = 1
AND DelFlag = 0
AND OServerProcessGroupUID IN (SELECT OServerProcessGroupUID
FROM #t1)
SELECT OServerProcessUID , ServerProcessName , ServerProcessID , PublicIP , PublicPort , PrivateIP , PrivatePort , OServerProcessBusinessTypeID ,
ServerProcessTypeID , DeviceOnlineCount , DeviceConnectionCount , SoftwareVerNo , IsEnabled , DelFlag , Description , CreateIP , CreateTime , ModifyIp ,
ModifyTime
FROM OServerProcess
WHERE 1 = 1
AND IsEnabled = 1
AND DelFlag = 0
AND OServerProcessUID IN ( SELECT OServerProcessUID FROM #t2 )
