总共12秒， 第一部分单独执行5s，改成exists 也是，第二部分< 1s, 第3部分4s， comment,trade, position表几百万行，其他user表几十万行。
select c.* from Comment c where
c.visibilityTypeId <> 4 and
isSysGenerated = 0
and c.userid in
(
select userId from
(
select top 10 u.id as userId, u.thDisplayName, count(t.id) as tradeCount from [User] u, Portfolio po, Position ps, trade t where
U.ID = po.UserId and po.id = ps.portfolio_Id and t.positionId = ps.id
group by u.id, u.thDisplayName
order by tradeCount desc) abc
)
union
select * from comment where inReplytoEventId in
(select top 10 inReplytoEventId as score from Comment where inReplyToEventId is not null
group by inReplytoEventId, inReplyToDiscussionTypeId order by score)
and visibilityTypeId <> 4
union
select * from comment
where inReplyToDiscussiontypeId < 5
order by perf_commentCount desc, voteCount desc, id desc
------解决方案--------------------
第一个图，hash match有个叹号，鼠标移过去看看，另外把右下角20%的那个Index scan的具体信息也贴出来
------解决方案--------------------
select c.* from
Comment c (NOLOCK)
WHERE EXISTS
(select userId from
(select top 10 u.id as userId
from [User] u (NOLOCK), Portfolio po (NOLOCK), Position ps (NOLOCK), trade t (NOLOCK)
where U.ID = po.UserId and po.id = ps.portfolio_Id and t.positionId = ps.id
group by u.id, u.thDisplayName
order by tradeCount desc) abc
WHERE abc.userId=c.userid)
and isSysGenerated = 0
and c.visibilityTypeId <> 4
union ALL --此处用union all速度更快，将不用排除重复的记录
select * from comment t (NOLOCK)
where EXISTS(SELECT 1 FROM
(select top 10 inReplytoEventId as score
from Comment
where inReplyToEventId is not NULL --以后表设计时尽量不要让此字段为null这样查将会行扫瞄
group by inReplytoEventId, inReplyToDiscussionTypeId
order by score) t1 ON T.inReplytoEventId=t1.inReplytoEventId
and t.visibilityTypeId <> 4
union ALL --此处用union all速度更快，将不用排除重复的记录
select * from comment (NOLOCK)
where inReplyToDiscussiontypeId < 5
order by perf_commentCount desc, voteCount desc, id DESC
------解决方案--------------------
这个问题最好的方式就是改写SQL语句，另外就是看索引是否合理。之前你提供的执行计划看到了一个部分：预估返回行数和实际返回行数相差很大，你的统计信息是否最新？这个有待确定
------解决方案--------------------
看看10楼的脚本有没有用