这条语句怎么优化

iliuriew 2009-10-21 10:47:49

SELECT sale_info.t_bank,dep.dep_name, dep1.dep_name AS X, product.product_name, SUM （sale_info.product_num) AS product_num_cnt
FROM sale_info
INNER JOIN dep as dep1 ON sale_info.t_bank = dep1.dep_id
INNER JOIN dep ON sale_info.dep_id = dep.dep_id
INNER JOIN product ON LEFT(sale_info.t_date, 7) = product.ny and sale_info.product_id = product.product_id
WHERE (sale_info.check_sym = '有效')
group by product.product_name,sale_info.t_bank ,dep.dep_name ,dep1.dep_name

union

select sale_info.t_bank,null,dep.dep_name, product.product_name,0
from sale_info
inner join dep on sale_info.t_bank=dep.dep_id
inner join product on product.ny>= '2009-09-01' AND product.ny<= '2009-10-30'
and (sale_info.t_bank+product.product_name) not in
(SELECT sale_info.t_bank+product.product_name FROM sale_info
INNER JOIN dep ON sale_info.t_bank = dep.dep_id
INNER JOIN product ON sale_info.product_id = product.product_id
AND LEFT(sale_info.t_date, 7) = product.ny WHERE (sale_info.check_sym = '有效'))

数据量比较大，页面出现“超时时间已到。在操作完成之前超时时间已过或服务器未响应”

...全文

71 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

wzy_love_sly 2009-10-22

打赏
举报

1用left就不行,索引就算会使用，扫描页数也会大大增加
2最少也给表加个with(nolock) 或set transaction isolation level read uncommitted，避免查询会阻塞或阻塞其他事务更新表

46539492 2009-10-22

打赏
举报

能把表结构，要求说一下就好了，大伙帮你分析分析

46539492 2009-10-22

打赏
举报

SELECT sale_info.t_bank,dep.dep_name, dep1.dep_name AS X, product.product_name, SUM （sale_info.product_num) AS product_num_cnt
FROM sale_info
INNER JOIN dep as dep1 ON sale_info.t_bank = dep1.dep_id
INNER JOIN dep ON sale_info.dep_id = dep.dep_id
INNER JOIN product ON LEFT(sale_info.t_date, 7) = product.ny and sale_info.product_id = product.product_id
WHERE (sale_info.check_sym = '有效')
group by product.product_name,sale_info.t_bank ,dep.dep_name ,dep1.dep_name

union

select sale_info.t_bank,null,dep.dep_name, product.product_name,0
from sale_info
inner join dep on sale_info.t_bank=dep.dep_id
inner join product on product.ny>= '2009-09-01' AND product.ny <= '2009-10-30'
and (sale_info.t_bank+product.product_name) not in
(SELECT sale_info.t_bank+product.product_name FROM sale_info
INNER JOIN dep ON sale_info.t_bank = dep.dep_id
INNER JOIN product ON sale_info.product_id = product.product_id
AND LEFT(sale_info.t_date, 7) = product.ny WHERE (sale_info.check_sym = '有效'))

iliuriew 2009-10-21