最近做关键字排名，当数据量到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

[MorningTea]

1.写在sp,这样只要传入少量变量，在服务器端执行，速度效率都好很多

2.用in速度很慢，尤其资料多，因为必须先把Select CorpID from B_Info where isAllow=1
and keyword=@Keyword and DateDiff(d,GetDate(),AddDate)<=ValidDay 的所有结果集查询出来后再去判断A.CorpID 是否在结果集里面，所以推荐使用exists


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 exists (Select B.CorpID from B_Info where isAllow=1
and keyword=@Keyword and DateDiff(d,GetDate(),AddDate)<=ValidDay
and A.CorpID = B.CorpID )

and not exists (Select A1.AID from A_Info A1 where A.CorpID = A1.CorpID and A1.AID > A.AID )
Order By B.OrderRows Desc

[evafly920]

效果如下
http://www.cn5135.com/Product/QueryResult.aspx?Keyword=%E6%89%8B%E6%9C%BA

[soaringsouth]

把有可能排除大多数记录的条件放在前面