最近做关键字排名，当数据量到20万时候速度很慢，求高手帮忙优化，谢谢

[evafly920]

最近做关键字排名，用下面的过程，当数据量到20万时候速度很慢，求高手帮忙优化，谢谢
Select * from A_Info as A
Inner Join B_Info As B On A.CorpID=B.CorpID

Where A.ParentID=0 and A.IsOpen=1 and A.IsAllow=1
and A.CorpID In (Select CorpID from B_Info where isAllow=1
and keyword=@Keyword and DateDiff(d,GetDate(),AddDate)<=ValidDay )

and A.AID In (Select Max(AID) from A_Info Group By CorpID)
Order By B.OrderRows Desc
效果如下
http://www.cn5135.com/Product/QueryResult.aspx?Keyword=%E6%89%8B%E6%9C%BA

[evafly920]

谢谢老大，我试下看

[bugchen888]

create index idx_B_Info_001 on B_Info(keyword,AddDate,ValidDay,CorpID)
create index idx_A_Info_001 on A_Info(CorpID,AID)

[zjcxc]

Select * from A_Info as A
Inner Join B_Info As B On A.CorpID=B.CorpID
Inner Join (
Select DISTINCT CorpID from B_Info
where isAllow=1
and keyword=@Keyword
and DateDiff(d,GetDate(),AddDate)<=ValidDay
)C ON A.CorpID=C.CorpID
Inner Join (
Select AID=Max(AID) from A_Info Group By CorpID
)D ON A.AID=D.AID
Where A.ParentID=0 and A.IsOpen=1 and A.IsAllow=1
Order By B.OrderRows Desc

[zjcxc]

1. 试试上面的方式
2. 去掉order by ,看看是否order by 导致

[张海霖]

能不order by就别order by