SQL语句 Sum 效率的问题(有100万条数据)
语句如下,应如何优化以获得更高的执行效率
谢谢select sum(CJRec_Sub_Receive_SL) AS RSL from CJRec_Sub where CJRec_Sub_WL_BM like '30041'
and CJRec_Sub_CBZX_R like 'C11902'
and CJRec_Sub_IS_OK=1 and CJRec_Sub_Type like 'I'
and (CJRec_Sub_Month between '2009-11-1 0:00:01' and '2009-11-10 23:59:59')
语句如下,应如何优化以获得更高的执行效率
谢谢select sum(CJRec_Sub_Receive_SL) AS RSL from CJRec_Sub where CJRec_Sub_WL_BM like '30041'
and CJRec_Sub_CBZX_R like 'C11902'
and CJRec_Sub_IS_OK=1 and CJRec_Sub_Type like 'I'
and (CJRec_Sub_Month between '2009-11-1 0:00:01' and '2009-11-10 23:59:59')
sum(CJRec_Sub_Receive_SL) AS RSL
from
CJRec_Sub
where
CJRec_Sub_WL_BM ='30041'
and CJRec_Sub_CBZX_R ='C11902'
and CJRec_Sub_IS_OK=1
and CJRec_Sub_Type ='I'
and CJRec_Sub_Month >='2009-11-1' AND CJRec_Sub_Month<'2009-11-11'
在各条件上加索引
from CJRec_Sub
where charindex('30041',CJRec_Sub_WL_BM)>0
and charindex('C11902',CJRec_Sub_CBZX_R)>0
and CJRec_Sub_IS_OK=1
and charindex('I',CJRec_Sub_Type)>0
and
CJRec_Sub_Month between '2009-11-1 0:00:01' and '2009-11-10 23:59:59'
请参见全文索引,
sum(CJRec_Sub_Receive_SL) AS RSL
from
CJRec_Sub where CJRec_Sub_WL_BM like '30041'
and
CJRec_Sub_CBZX_R like 'C11902'
and
CJRec_Sub_IS_OK=1 and CJRec_Sub_Type like 'I'
and
(CJRec_Sub_Month between '2009-11-1 0:00:01' and '2009-11-10 23:59:59')
--------CJRec_Sub_WL_BM 、CJRec_Sub_CBZX_R、CJRec_Sub_IS_OK、CJRec_Sub_Type 字段 都可以根据需求加索引
这个要在你查询的where后面的查询条件上加上索引应该会好些
CJRec_Sub_Month between '2009-11-1 0:00:01' and '2009-11-10 23:59:59'
这个条件段内的数据不会是100万吧。如果这样的话1秒以内不会有问题。
CJRec_Sub_WL_BM ='30041'
and CJRec_Sub_CBZX_R ='C11902'
and CJRec_Sub_IS_OK=1
and CJRec_Sub_Type ='I'
and CJRec_Sub_Month between '2009-11-1' AND '2009-11-11'
再加上一定的索引,会提高一写的速度