查询超时问题~

[a05]

select top 150 Send_MessageID,ID_From,ID_To,Body,Date_Send,Free_Type,SendTimes,Send_State,sServiceType,byFeeUserType,sFeeCode,SRR from Send_Message where Send_State=0 or (Send_State=2 and SendTimes<"+linktimes+") or(Send_State=1 and (dateadd(minute,30,Date_Send)<getdate()) ) order by sendTimes ASC, SendLevel DESC

我的Send_Message表有800多万条记录，每次查询都超时，请问有什么好的方法解决？？？？？

[lzymagi]

你这样的情况应该是数据库资源紧张的原因.(系统资源不够或多人同时使用等等)
试从这两方面着手

[dnvodcwan]

用我的经验来说，　你不用 order by ASC DESC ,如果一定要用的话，就得想办法代替

[a05]

楼上的意思是800多万条记录，用上面的select语句应该没问题？

[hisi]

在你的 sendTimes 字段上建立索引

[a05]

to dnvodcwan(萬萬)
我如果我一定要排序的话，请问有什么好方法代替呢

[hisi]

我建议你建立一个自增字段，估计你后加入表的sendTimes应该是递增的，这样再把这个自增字段设为主键。你在查询时用 order by id 代替 order by sendTimes ，这样会快很多。

[dnvodcwan]

select top 150 Send_MessageID,ID_From,ID_To,Body,Date_Send,Free_Type,SendTimes,Send_State,sServiceType,byFeeUserType,sFeeCode,SRR
from Send_Message a
where Send_State=0 or (Send_State=2 and SendTimes<"+linktimes+")
or(Send_State=1 and (dateadd(minute,30,Date_Send)<getdate()) )
and a.uid=(select uid from Send_Message where uid=a.uid order by sendTimes ASC) and a.uid=(select uid from Send_Message where uid=a.uid order by SendLevel DESC)

[dnvodcwan]

其中的UID是个自增字段, 这样也可以
Send_MessageID,ID_From,ID_To,Body,Date_Send,Free_Type,SendTimes,Send_State,sServiceType,byFeeUserType,sFeeCode,SRR
from Send_Message a
where Send_State=0 or (Send_State=2 and SendTimes<"+linktimes+")
or(Send_State=1 and (dateadd(minute,30,Date_Send)<getdate()) ) and uid in
(select top 150 uid from Send_Message order by order by sendTimes ASC, SendLevel DESC)

[a05]

可是我的sendtimes不是自动，是代表次数，一共就1，2，3，三个数值，所以好象不能那样做

[a05]

没人再来看下么

[a05]

再up