第一个sql如下:select d.dealer_name as 销售部门,
a.dealer_id as 直销员编号,
c.dealer_name as 直销员姓名,
e.name as 职务,
sum(a.fee) as 出账金额
from bd_bill_source_t a,
bdm_drt_direct_dealer_info_t b,
bd_dealer_t c,
bd_dealer_t d,
bd_drt_dic_list_t e
where a.fee_date = b.accountperiod
and a.dealer_id = c.dealer_id
and c.belongs_part = d.dealer_id
and a.dealer_id = b.dealer_id
and e.id = b.duty_id
and a.fee_date = '201004'
and a.city_code = '750'
and c.belongs_part in ('5750042',
'5750604',
'5750904',
'5750166',
'5750165',
'A075750042',
'A075750041')
and a.service_kind = 9
and a.fee_kind not in (100, 31, 32)
and a.fee > 0
group by d.dealer_name, a.dealer_id, c.dealer_name, e.name
order by d.dealer_name
第二个sql如下:select d.dealer_name as 销售部门,
a.dealer_id as 直销员编号,
c.dealer_name as 直销员姓名,
e.name as 职务,
sum(a.fee) as 出账金额
from bd_bill_source_t a
inner join bdm_drt_direct_dealer_info_t b
on a.fee_date = b.accountperiod
inner join bd_dealer_t c
on a.dealer_id = c.dealer_id
inner join bd_dealer_t d
on c.belongs_part = d.dealer_id
inner join bd_drt_dic_list_t e
on e.id = b.duty_id
where c.belongs_part in ('5750042',
'5750604',
'5750904',
'5750166',
'5750165',
'A075750042',
'A075750041')
and a.service_kind = 9
and a.fee_kind not in (100, 31, 32)
and a.fee > 0
group by d.dealer_name, a.dealer_id, c.dealer_name, e.name
order by d.dealer_name
这2条sql条件一模一样,只是写法不一样,在同一个环境里面为什么,第一条sql执行只花了45秒钟,而第二条sql执行了15分钟还没有出来结果呢?
a.dealer_id as 直销员编号,
c.dealer_name as 直销员姓名,
e.name as 职务,
sum(a.fee) as 出账金额
from bd_bill_source_t a,
bdm_drt_direct_dealer_info_t b,
bd_dealer_t c,
bd_dealer_t d,
bd_drt_dic_list_t e
where a.fee_date = b.accountperiod
and a.dealer_id = c.dealer_id
and c.belongs_part = d.dealer_id
and a.dealer_id = b.dealer_id
and e.id = b.duty_id
and a.fee_date = '201004'
and a.city_code = '750'
and c.belongs_part in ('5750042',
'5750604',
'5750904',
'5750166',
'5750165',
'A075750042',
'A075750041')
and a.service_kind = 9
and a.fee_kind not in (100, 31, 32)
and a.fee > 0
group by d.dealer_name, a.dealer_id, c.dealer_name, e.name
order by d.dealer_name
第二个sql如下:select d.dealer_name as 销售部门,
a.dealer_id as 直销员编号,
c.dealer_name as 直销员姓名,
e.name as 职务,
sum(a.fee) as 出账金额
from bd_bill_source_t a
inner join bdm_drt_direct_dealer_info_t b
on a.fee_date = b.accountperiod
inner join bd_dealer_t c
on a.dealer_id = c.dealer_id
inner join bd_dealer_t d
on c.belongs_part = d.dealer_id
inner join bd_drt_dic_list_t e
on e.id = b.duty_id
where c.belongs_part in ('5750042',
'5750604',
'5750904',
'5750166',
'5750165',
'A075750042',
'A075750041')
and a.service_kind = 9
and a.fee_kind not in (100, 31, 32)
and a.fee > 0
group by d.dealer_name, a.dealer_id, c.dealer_name, e.name
order by d.dealer_name
这2条sql条件一模一样,只是写法不一样,在同一个环境里面为什么,第一条sql执行只花了45秒钟,而第二条sql执行了15分钟还没有出来结果呢?
and a.fee_date = '201004'
and a.city_code = '750'
第二条语句里面也有
and a.fee_date = '201004'
and a.city_code = '750'
的
我忘记贴了,不好意思啊?
2个sql条件完全是一模一样的。
性能问题首先就要看执行计划啊,收集下表的统计信息先
比join连接更接近核心SQL.
所以用where 连接执行速度很快.
INNER JOIN 虽然结果同WHERE一样,但在执行上要多走几步弯路.
exists 查询效果更好