SQL 語句優化

[才子鸣]

SELECT b.wo_no, b.part_no, c.process, e.route_code input,
e1.route_code output, SUM (a.qty) inputqty,d.outputqty,
TO_CHAR (NVL (TRUNC (d.outputqty / SUM (a.qty), 2) * 100, 0) || '%') output_rate,
SUM (a.qty) - d.outputqty wip,
g.meaning org
FROM sfcs.sfcs_io_statistics a,
sfcs.wip_wo b,
sfcs.inv_pn_process c,
sfcs.sh_route_code e,
sfcs.sh_route_code e1,
sfcs.inv_pn f,
sfcs.sys_parameters g,
(SELECT SUM (a1.qty) outputqty, b1.wo_no, b1.part_no, c1.process
FROM sfcs.sfcs_io_statistics a1,
sfcs.wip_wo b1,
sfcs.inv_pn_process c1
WHERE a1.route_code = c1.o_ws_id
AND a1.TYPE = 'O'
AND a1.wo_key = b1.wo_key
AND c1.part_no = b1.part_no
AND a1.part_no = c1.part_no
GROUP BY b1.wo_no, b1.part_no, c1.process) d
WHERE a.route_code = c.i_ws_id
AND c.i_ws_id = e.routecode_key
AND c.o_ws_id = e1.routecode_key
AND a.TYPE = 'I'
AND a.wo_key = b.wo_key
AND c.part_no = b.part_no
AND a.part_no = c.part_no
AND c.part_no = f.part_no
AND g.lookup_type = 'ERP_ORG_TYPE'
AND f.attribute05 IN g.lookup_code
AND a.org_id = 4
GROUP BY b.wo_no,
b.part_no,
c.process,
e.route_code,
e1.route_code,
g.meaning,
d.outputqty




sfcs_io_statistics這里面有200W的記錄
wip_wo記錄為15000
inv_pn_process記錄4000
sh_route_code記錄150
inv_pn記錄1000
sys_parameters記錄2000

現在這段代碼執行時間太長了。大概15分鐘。而且執行到最後總是把表空間TEMP用暴掉。我也知道這段代碼寫的相當不合理，但我就是改不好！研究了半個月了！

希望有人能幫我優化一下！謝謝各位了！

[hongqi162]

too long................

[hongqi162]

索引什么的都建了吗？

[才子鸣]

索引都已經建好！沒有問題~

[hongqi162]

200W的数据量说大不大说小也不小啊,不知道你的服务器性能,如果普通的服务器,速度上肯定有影响的,你可以采用以下并行查询试试,看看速度有没有改观

[WuChenCan]

group by 项目太多(建立相关索引),把g.lookup_type = 'ERP_ORG_TYPE' a.用(select fieldlist from sfcs.sfcs_io_statistics where TYPE = 'I' and org_id = 4)代替from 后面的sfcs.sfcs_io_statistics

[WuChenCan]

SELECT b.wo_no, b.part_no, c.process, e.route_code input,
e1.route_code output, SUM (a.qty) inputqty,d.outputqty,
TO_CHAR (NVL (TRUNC (d.outputqty / SUM (a.qty), 2) * 100, 0) || '%') output_rate,
SUM (a.qty) - d.outputqty wip,
g.meaning org
FROM select fieldlist from sfcs.sfcs_io_statistics where TYPE = 'I' and org_id = 4) a,
sfcs.wip_wo b,
sfcs.inv_pn_process c,
sfcs.sh_route_code e,
sfcs.sh_route_code e1,
sfcs.inv_pn f,
(select * from sfcs.sys_parameters where lookup_type = 'ERP_ORG_TYPE' ) g,
(SELECT SUM (a1.qty) outputqty, b1.wo_no, b1.part_no, c1.process
FROM sfcs.sfcs_io_statistics a1,
sfcs.wip_wo b1,
sfcs.inv_pn_process c1
WHERE a1.route_code = c1.o_ws_id
AND a1.TYPE = 'O'
AND a1.wo_key = b1.wo_key
AND c1.part_no = b1.part_no
AND a1.part_no = c1.part_no
GROUP BY b1.wo_no, b1.part_no, c1.process) d
WHERE a.route_code = c.i_ws_id
AND c.i_ws_id = e.routecode_key
AND c.o_ws_id = e1.routecode_key
AND a.wo_key = b.wo_key
AND c.part_no = b.part_no
AND a.part_no = c.part_no
AND c.part_no = f.part_no
AND f.attribute05 IN g.lookup_code
GROUP BY b.wo_no,
b.part_no,
c.process,
e.route_code,
e1.route_code,
g.meaning,
d.outputqty

[hongqi162]

先局部查询在关联汇总

[hsmserver]

语句过于复杂
可以分成几个视图
然后关联查询

[comanche]

语句不算复杂啦, 不过 inner join 都用 where 去写是不好的

该用临时表的用

以上慢的问题是多方面的, 数据库本身优化也是其中之一, 只能说说而已, 实际还是靠自已

确定关联是主键外键关系很重要, 没建关系那就没得说了,

那种不用于条件的主键(自动id比方)就不要建成簇式索引, 簇式索引应该是那种最常用于关联的

有时100%用于关联用的多级索引反而更应该是簇式索引, 这时写语句时就要注意inner join条件的顺序(不是所有数据库都会识别顺序的), 这样就要求整个程序的 sql 作者在写代码时对设计是熟悉的, 这样看区分编码员和设计师似乎是不可能的, 质量是每个参与的人

200w 算是记录很少了, 总得说 20亿(8亿mssql可能是极限, oracle也多不少哪去)记录跟 200w 在关联建得完整的数据库中查询时间不应大于 2-3 s 的区别

[vfork]

建议：别用嵌套查询，这样语句太长，不好维护。

可以用临时表，并给临时表建立索引，这样速度提高不少的。

[jackietdcn]

同意WuChenCan(雨中男孩)

用并行查询会好些.
把g.lookup_type = 'ERP_ORG_TYPE' a.用(select fieldlist from sfcs.sfcs_io_statistics where TYPE = 'I' and org_id = 4)代替from 后面的sfcs.sfcs_io_statistics

[ERP_Youngor_Knits]

建议使用临时表,例利维护也有利于速度加快.