零件批次查询优化

对于以下查询:

select t_operat.et_stocpf,t_operat.operat_nscomp,t_operat.nommodif,t_operat.datemodif,LEFT(t_operat.heuremodif,6) AS timeHms,t_operat.operat_comp_no_passage,et_refcmp,t_lotuse.ET_REF_COMP,t_lotuse.ET_LOTCMP_NOLOT,t_lotuse.et_lotcmp_no_etiquette,t_modeop.et_prod_poste,modeop_libelle,et_refakf_comp,qualpf_libelle,CNTNER_NUMCONT
from t_operat,t_stocpf,t_simo,t_modeop,t_nomen,t_qualpf,t_cntner,t_packpf,t_lotuse
where t_operat.et_stocpf in(select ET_STOCPF from t_lotuse where ET_REF_COMP='626724300D' and ET_LOTCMP_NOLOT='24121302')
and t_stocpf.id_stocpf=t_operat.et_stocpf
and t_operat.et_simo=t_simo.id_simo
and t_simo.et_modeop=t_modeop.id_modeop
and t_simo.et_nomen=t_nomen.id_nomen
and t_stocpf.et_qualpf=t_qualpf.id_qualpf
and t_packpf.et_stocpf=t_operat.et_stocpf
and t_packpf.et_cntner=t_cntner.id_cntner
and t_stocpf.id_stocpf=t_lotuse.et_stocpf
order by datemodif,timeHms

　　

需要第一步执行,
select ET_STOCPF from t_lotuse where ET_REF_COMP='626724300D' and ET_LOTCMP_NOLOT='24121302'
然后进行总的SQL,但是由于第一步查出来的数据比较多，这样一次执行整个查询时间比较长，这是个SQL Server的数据库，需要改善改查询语句，减少服务器负担，加快查询。

使用 JOIN 替代子查询 

这是最推荐和最有效的优化方式。通过将子查询转换为一个 JOIN（通常是 INNER JOIN），数据库查询优化器可以更有效地处理数据，避免对子查询结果集的临时存储和多次查找。

优化后的 SQL 语句：

select t_operat.et_stocpf, t_operat.operat_nscomp, t_operat.nommodif, t_operat.datemodif, LEFT(t_operat.heuremodif, 6) AS timeHms, t_operat.operat_comp_no_passage, et_refcmp, t_lotuse.ET_REF_COMP, t_lotuse.ET_LOTCMP_NOLOT, t_lotuse.et_lotcmp_no_etiquette, t_modeop.et_prod_poste, modeop_libelle, et_refakf_comp, qualpf_libelle, CNTNER_NUMCONT
from t_operat
-- 使用 INNER JOIN 替代 IN 子查询
INNER JOIN t_lotuse ON t_operat.et_stocpf = t_lotuse.ET_STOCPF
-- 添加原始查询中的所有其他 JOIN 条件
INNER JOIN t_stocpf ON t_stocpf.id_stocpf = t_operat.et_stocpf
INNER JOIN t_simo ON t_operat.et_simo = t_simo.id_simo
INNER JOIN t_modeop ON t_simo.et_modeop = t_modeop.id_modeop
INNER JOIN t_nomen ON t_simo.et_nomen = t_nomen.id_nomen
INNER JOIN t_qualpf ON t_stocpf.et_qualpf = t_qualpf.id_qualpf
INNER JOIN t_packpf ON t_packpf.et_stocpf = t_operat.et_stocpf
INNER JOIN t_cntner ON t_packpf.et_cntner = t_cntner.id_cntner
where t_lotuse.ET_REF_COMP = '626724300D' 
  and t_lotuse.ET_LOTCMP_NOLOT = '24121302'
order by datemodif, timeHms

为什么这种方法更优？

• 执行计划优化： 当使用 JOIN 时，SQL Server 的查询优化器可以一次性构建一个更高效的执行计划。它可以先对 t_lotuse 表进行过滤，然后利用过滤后的较小结果集与其他表进行连接，这比先执行一个独立的子查询再对大量结果集进行 IN 匹配要快得多。

• 避免临时表/哈希操作： IN 子查询在某些情况下可能导致数据库创建临时表或进行哈希操作来存储子查询的结果，这会消耗额外的内存和 CPU 资源。JOIN 操作则通常更直接地利用索引进行数据匹配。

与此相似，对于以下查询

select * from t_operat where et_stocpf in
(select id_stocpf from t_stocpf where et_refcmp like 'TEST%' and et_qualpf='O')

　改为:

select * from t_operat inner join t_stocpf
on t_operat.et_stocpf=t_stocpf.id_stocpf
where t_stocpf.et_refcmp like 'TEST%' and t_stocpf.et_qualpf='O'

　也可以大幅度优化查询时间。