靠优化索引不能解决 select mobile from tab1 where calltime between '2003-7-1' and '2003-07-31 23:59:59' group by mobile having sum(Sumfee) between 300 and 500 比较慢 用户比较多
我现在把 select mobile from tab1 where calltime between '2003-7-1' and '2003-07-31 23:59:59' group by mobile having sum(Sumfee) between 300 and 500 一段做成视图,结果一样没效率. 可能是数据太多. 我的要求简单的说,就是把满足300-500元之间的用户的漫游话单总数计算出来(roam_type<>0). 我也看过执行计划,,但不知道怎么解决...
1 视图上建索引 2 roam_type<>0效率较慢,是否改为 =? 3 mobile in (select mobile from tab1 where calltime between '2003-7-1' and '2003-07-31 23:59:59' group by mobile having sum(Sumfee) between 300 and 500)这个字查询将会导致效率低下
select mobile from tab1 where calltime between '2003-7-1' and '2003-07-31 23:59:59' group by mobile having sum(Sumfee) between 300 and 500 慢吗?将IN换成EXISTS效率会高点
roam_type<>0可以換成roam_type=x and roam_type=x .. 不等于最少需遍歷所有數據.
直接select mobile from tab1 where roam_type<>0 and calltime between '2003-7-1' and '2003-07-31 23:59:59' group by mobile having sum(Sumfee) between 300 and 500 应该快一些如果返回数据太多最好用top n
to: freecs(北狼) 1:怎样在视图上建索引? 2:roam_type<>0 改为=0,>0都差不多. to: caiyunxia(monkey) select mobile from tab1 where calltime between '2003-7-1' and '2003-07-31 23:59:59' group by mobile having sum(Sumfee) between 300 and 500 这个语句大概要5,6分钟的样子. 但是组合起来之后30分钟都不行. 将IN换成EXISTS效率还低一点点.. to: Rotaxe(程序员) 可能是我没说清楚,我不是要这样的结果. 我的条件是,先要满足总话费是300-500之间的用户, 然后再从这群用户中算出他们的漫游总话费(roam_type<>0). roam_type=0就是本地话费,我想过算出本地话费,然后用总 话费-本地话费=漫游总话费的方便,不过依然没效率,很久都没出结果...
select mobile from tab1 where calltime between '2003-7-1' and '2003-07-31 23:59:59' group by mobile having sum(Sumfee) between 300 and 500 大概有多少纪录? mobile上有没有聚集索引?
select mobile from tab1 where calltime between '2003-7-1' and '2003-07-31 23:59:59' group by mobile having sum(Sumfee) between 300 and 500 大概有四千多条记录..
如果mobile上有聚集索引,应该排好序了 可以这样 select mobile into #tmp from tab1 where calltime between '2003-7-1' and '2003-07-31 23:59:59' group by mobile having sum(Sumfee) between 300 and 500select sum(Sumfee) from tab1 where roam_type<>0 and (calltime between '2003-7-1' and '2003-07-31 23:59:59') and mobile in (select mobile from #tmp) option(merge join)
保存每个用户每天的总话费己总漫游费
可以用JOB,每天从tab1汇总上一天的,插入到此表
还建每月汇总表
select mobile from tab1
where calltime between '2003-7-1' and '2003-07-31 23:59:59'
group by mobile
having sum(Sumfee) between 300 and 500
比较慢
用户比较多
select mobile from tab1
where calltime between '2003-7-1' and '2003-07-31 23:59:59'
group by mobile
having sum(Sumfee) between 300 and 500
一段做成视图,结果一样没效率.
可能是数据太多.
我的要求简单的说,就是把满足300-500元之间的用户的漫游话单总数计算出来(roam_type<>0).
我也看过执行计划,,但不知道怎么解决...
2 roam_type<>0效率较慢,是否改为 =?
3 mobile in
(select mobile from tab1
where calltime between '2003-7-1' and '2003-07-31 23:59:59'
group by mobile
having sum(Sumfee) between 300 and 500)这个字查询将会导致效率低下
where calltime between '2003-7-1' and '2003-07-31 23:59:59'
group by mobile
having sum(Sumfee) between 300 and 500
慢吗?将IN换成EXISTS效率会高点
不等于最少需遍歷所有數據.
where roam_type<>0 and calltime between '2003-7-1' and '2003-07-31 23:59:59'
group by mobile
having sum(Sumfee) between 300 and 500 应该快一些如果返回数据太多最好用top n
1:怎样在视图上建索引?
2:roam_type<>0 改为=0,>0都差不多.
to: caiyunxia(monkey)
select mobile from tab1
where calltime between '2003-7-1' and '2003-07-31 23:59:59'
group by mobile
having sum(Sumfee) between 300 and 500
这个语句大概要5,6分钟的样子.
但是组合起来之后30分钟都不行.
将IN换成EXISTS效率还低一点点..
to: Rotaxe(程序员)
可能是我没说清楚,我不是要这样的结果.
我的条件是,先要满足总话费是300-500之间的用户,
然后再从这群用户中算出他们的漫游总话费(roam_type<>0).
roam_type=0就是本地话费,我想过算出本地话费,然后用总
话费-本地话费=漫游总话费的方便,不过依然没效率,很久都没出结果...
where calltime between '2003-7-1' and '2003-07-31 23:59:59'
group by mobile
having sum(Sumfee) between 300 and 500
大概有多少纪录?
mobile上有没有聚集索引?
where calltime between '2003-7-1' and '2003-07-31 23:59:59'
group by mobile
having sum(Sumfee) between 300 and 500
大概有四千多条记录..
可以这样
select mobile into #tmp from tab1
where calltime between '2003-7-1' and '2003-07-31 23:59:59'
group by mobile
having sum(Sumfee) between 300 and 500select sum(Sumfee) from tab1
where roam_type<>0 and (calltime between '2003-7-1' and '2003-07-31 23:59:59') and mobile in (select mobile from #tmp) option(merge join)
我在试你的方法,有结果马上回复...
用Rotaxe(程序员)的方法,一共23分钟左右可以出结果,,
比我之前的方法要有效率很多了,再次感谢!!!