数据库语句查询慢优化[快了一半!]
最近遇到SQL语句的操作时,当表进行group by联查,表中语句又很多的情况下,出现查询时间过长的状况!!!()
操作人员总是在投诉我们的查询慢,迫不得已,自己做了下DB维护的优化操作记录下–(菜鸡的我:只能将时间(10s)缩短到1s~2s,平均时间检测快了一半)
===>欢迎各位
大佬支招,上来碾压我的优化方法,给我火箭般的执行速度
1.主要思想:
运用建临时表的的思想
if object_id('tempdb..#temp') is not null drop table #temp
select value1
,value2
,value3
...
from A --你要查询的表
where 1=1 and ...
into #temp --将此结果插入到临时表
select value1
,value2
,value3
...
from #temp
2.优化前的语句:(可以不瞅,哈哈!自己弄得业务代码)
SELECT
WMOD.MOVETYPE,
MAX(WMOD.MOVEORDID) AS MOVEORDID,
MAX(WMOD.ERP_SNDSEQNO) AS ERP_SNDSEQNO ,
MAX(WMOD.MOVEORDSTAT) AS MOVEORDSTAT,
MAX(WMOD.MOVESTAT) AS MOVESTAT,
WMOD.MOVESEQNO,
IR.SHIPTOID AS CUSTOMERID,
B.PRODID,
SUM(WMOD.WIPQTY) AS WIPQTY,
B.OUTER_BOXID,
B.PALLETID,
B.PALLETNO,
SUM(ISNULL(B.BOXQTY_SPLT, 0)) AS BOXQTY_SPLT,
MAX(ISNULL(B.BOXID_PV,'NA')) AS BOXID_PV,
WMOD.SHOPID_FROM,
WMOD.AREAID_FROM,
WMOD.EQSGID_FROM,
WMOD.PROCID_FROM,
WMOD.SLOCID_FROM,
WMOD.WHID_FROM,
WMOD.SHOPID_TO,
WMOD.AREAID_TO,
WMOD.EQSGID_TO,
WMOD.PROCID_TO,
WMOD.SLOCID_TO,
WMOD.WHID_TO,
PA.PDMTITEMVALUE AS UNIT
,ezmes.ezMES_fn_GET_MMD_PRODITEMNAME('ZH-CN','ITPL012',B.PRODID) AS PARTNO
,EZMES.EZMES_FN_GET_NAME('ZH-CN',PD.PRODNAME) AS PRODNAME
,WMOD.INSUSER AS ISSUSER
,SUBSTRING(B.PALLETID,1,8) AS ISSDATE
,TB.PDMTITEMVALUE AS TOP_BOTTOM
,EZMES.EZMES_FN_GET_NAME('ZH-CN', BC.BIZCUSTGRNAME) AS CUSTOMERNAME
,WMOD.ERP_SNDSTAT AS ERPSTAT
,WMOD.MOVENOTE_FROM AS MOVEUSER
,MAX(SUBSTRING(B.PALLETID,1,4) + '-' + SUBSTRING(B.PALLETID,5,2) + '-' + SUBSTRING(B.PALLETID,7,2) +' '+ SUBSTRING(B.PALLETID,9,2) +':'+ SUBSTRING(B.PALLETID,11,2) +':'+ SUBSTRING(B.PALLETID,13,2)) AS ISSDTTM
,MAX(EZMES.EZMES_FN_GET_NAME('ZH-CN', TS.SLOCNAME)) AS SLOCID_FROM_NAME
,MAX(EZMES.EZMES_FN_GET_NAME('ZH-CN', ES.EQSGNAME)) AS EQSGID_FROM_NAME
,MAX(EZMES.EZMES_FN_GET_NAME('ZH-CN', PC.PROCNAME)) AS PROCID_FROM_NAME
,MAX(BOX_PV.PALLETID) AS PALLETID_PV
,MAX(WMOD_PV.ERP_SNDSEQNO) AS ERP_SNDSEQNO_PV
--,EZMES.EZMES_FN_GET_NAME('ZH-CN',MAX(CC.CMCDNAME)) AS MOVEORDSTAT_NAME
,IIF(EZMES.EZMES_FN_GET_NAME('ZH-CN',MAX(CC.CMCDNAME)) IS NULL,
(SELECT EZMES.EZMES_FN_GET_NAME('ZH-CN',MAX(CC.CMCDNAME)) FROM ezmes.CommonCode CC WITH(NOLOCK) WHERE 'H' = CC.CMCODE AND CC.CMCDTYPE = 'MOVEORDSTAT'), EZMES.EZMES_FN_GET_NAME('ZH-CN',MAX(CC.CMCDNAME))) AS MOVEORDSTAT_NAME
,CONVERT(VARCHAR, MAX(WOH.ACTDTTM),112) AS OUTDTTM
FROM
TB_SFC_WIPMOVEORDERDETAIL_T WMOD WITH(NOLOCK)
LEFT OUTER JOIN BOX B WITH(NOLOCK) ON B.BOXID = WMOD.BOXID
LEFT OUTER JOIN BOX BOX_PV WITH(NOLOCK) ON BOX_PV.BOXID = B.BOXID_PV
LEFT OUTER JOIN TB_SFC_WIPMOVEORDERDETAIL_T WMOD_PV WITH(NOLOCK) ON WMOD_PV.BOXID = B.BOXID_PV AND WMOD_PV.MOVEORDSTAT = 'A1'
LEFT OUTER JOIN TB_SFC_IssueResult IR WITH(NOLOCK) ON IR.BOXID = WMOD.BOXID
LEFT OUTER JOIN TB_MMD_BusinessCustomerGroup BC WITH(NOLOCK) ON IR.SHIPTOID = BC.CUSTOMERGRID
LEFT OUTER JOIN TB_MMD_ProductAttr PA WITH(NOLOCK) ON PA.PRODID = B.PRODID AND PA.PDMTITEMID = 'TM003'
LEFT OUTER JOIN PRODUCT PD WITH(NOLOCK) ON B.PRODID = PD.PRODID
LEFT OUTER JOIN TB_MMD_ProductAttr TB WITH(NOLOCK) ON (TB.PRODID = B.PRODID AND TB.PDMTITEMID = 'ITPL009' AND TB.USEFLAG = 'Y')
LEFT OUTER JOIN ezmes.TB_MMD_StorageLocation TS WITH(NOLOCK) ON TS.SLOCID = WMOD.SLOCID_FROM
LEFT OUTER JOIN ezmes.EquipmentSegment ES WITH(NOLOCK) ON ES.EQSGID = WMOD.EQSGID_FROM
LEFT OUTER JOIN ezmes.Process PC WITH(NOLOCK) ON PC.PROCID = WMOD.PROCID_FROM
LEFT OUTER JOIN ezmes.CommonCode CC WITH(NOLOCK) ON WMOD.MOVEORDSTAT = CC.CMCODE AND CC.CMCDTYPE = 'MOVEORDSTAT'
--LEFT OUTER JOIN TB_SFC_WipMoveOrderDetailHistory WOH WITH(NOLOCK) ON WMOD.MOVEORDID = WOH.MOVEORDID AND WOH.MOVEORDSTAT = 'E1'
LEFT OUTER JOIN (SELECT MOVEORDID, MAX(ACTDTTM) AS ACTDTTM FROM TB_SFC_WipMoveOrderDetailHistory WITH(NOLOCK) WHERE MOVEORDSTAT = 'E1' GROUP BY MOVEORDID) WOH ON WMOD.MOVEORDID = WOH.MOVEORDID
WHERE WMOD.MOVETYPE = 'MOVE_WAREHOUSE'
AND WMOD.MOVEORDSTAT <> 'C'
AND B.PALLETID BETWEEN '20220424' + '000000' AND '20220425' + '230000'
AND B.PALLETID IS NOT NULL AND IR.SHIPTOID IS NOT NULL
AND B.BOXSTAT <> 'UNPACK'
AND PA.PDMTITEMVALUE = 'C'
AND TS.SHOPID = 'G181'
AND WMOD.SHOPID_FROM = 'G181'
GROUP BY
WMOD.MOVETYPE,
WMOD.MOVESEQNO,
IR.SHIPTOID,
B.PRODID,
B.OUTER_BOXID,
B.PALLETID,
B.PALLETNO,
WMOD.SHOPID_FROM,
WMOD.AREAID_FROM,
WMOD.EQSGID_FROM,
WMOD.PROCID_FROM,
WMOD.SLOCID_FROM,
WMOD.WHID_FROM,
WMOD.SHOPID_TO,
WMOD.AREAID_TO,
WMOD.EQSGID_TO,
WMOD.PROCID_TO,
WMOD.SLOCID_TO,
WMOD.WHID_TO,
PA.PDMTITEMVALUE
,ezmes.ezMES_fn_GET_MMD_PRODITEMNAME('ZH-CN','ITPL012',B.PRODID)
,EZMES.EZMES_FN_GET_NAME('ZH-CN',PD.PRODNAME)
,WMOD.INSUSER
,SUBSTRING(B.PALLETID,1,8)
,TB.PDMTITEMVALUE
,EZMES.EZMES_FN_GET_NAME('ZH-CN', BC.BIZCUSTGRNAME)
,WMOD.ERP_SNDSTAT
,WMOD.MOVENOTE_FROM
ORDER BY B.PALLETID DESC
3.优化后语句
if object_id('tempdb..#temp') is not null drop table #temp
SELECT
WMOD.MOVETYPE,
WMOD.MOVEORDID,
WMOD.ERP_SNDSEQNO,
WMOD_PV.ERP_SNDSEQNO AS ERP_SNDSEQNO_PV,
WMOD.MOVEORDSTAT,
WMOD.MOVESTAT,
WMOD.MOVESEQNO,
IR.SHIPTOID,
B.PRODID,
WMOD.WIPQTY,
B.OUTER_BOXID,
B.PALLETID,
B.PALLETNO,
B.BOXQTY_SPLT,
B.BOXID_PV,
WMOD.SHOPID_FROM,
WMOD.AREAID_FROM,
WMOD.EQSGID_FROM,
WMOD.PROCID_FROM,
WMOD.SLOCID_FROM,
WMOD.WHID_FROM,
WMOD.SHOPID_TO,
WMOD.AREAID_TO,
WMOD.EQSGID_TO,
WMOD.PROCID_TO,
WMOD.SLOCID_TO,
WMOD.WHID_TO,
PA.PDMTITEMVALUE AS UNIT,
TB.PDMTITEMVALUE AS TOP_BOTTOM,
BOX_PV.PALLETID AS PALLETID_PV,
WMOD.INSUSER,
WMOD.ERP_SNDSTAT,
WMOD.MOVENOTE_FROM,
PD.PRODNAME,
BC.BIZCUSTGRNAME,
TS.SLOCNAME,
ES.EQSGNAME,
PC.PROCNAME,
WOH.ACTDTTM,
B.BOXSTAT,
CC.CMCDNAME
into #temp
FROM
TB_SFC_WIPMOVEORDERDETAIL_T WMOD WITH(NOLOCK)
LEFT OUTER JOIN BOX B WITH(NOLOCK) ON B.BOXID = WMOD.BOXID
LEFT OUTER JOIN BOX BOX_PV WITH(NOLOCK) ON BOX_PV.BOXID = B.BOXID_PV
LEFT OUTER JOIN TB_SFC_WIPMOVEORDERDETAIL_T WMOD_PV WITH(NOLOCK) ON WMOD_PV.BOXID = B.BOXID_PV AND WMOD_PV.MOVEORDSTAT = 'A1'
LEFT OUTER JOIN TB_SFC_IssueResult IR WITH(NOLOCK) ON IR.BOXID = WMOD.BOXID
LEFT OUTER JOIN TB_MMD_BusinessCustomerGroup BC WITH(NOLOCK) ON IR.SHIPTOID = BC.CUSTOMERGRID
LEFT OUTER JOIN TB_MMD_ProductAttr PA WITH(NOLOCK) ON PA.PRODID = B.PRODID AND PA.PDMTITEMID = 'TM003'
LEFT OUTER JOIN PRODUCT PD WITH(NOLOCK) ON B.PRODID = PD.PRODID
LEFT OUTER JOIN TB_MMD_ProductAttr TB WITH(NOLOCK) ON (TB.PRODID = B.PRODID AND TB.PDMTITEMID = 'ITPL009' AND TB.USEFLAG = 'Y')
LEFT OUTER JOIN TB_MMD_StorageLocation TS WITH(NOLOCK) ON TS.SLOCID = WMOD.SLOCID_FROM
LEFT OUTER JOIN EquipmentSegment ES WITH(NOLOCK) ON ES.EQSGID = WMOD.EQSGID_FROM
LEFT OUTER JOIN Process PC WITH(NOLOCK) ON PC.PROCID = WMOD.PROCID_FROM
LEFT OUTER JOIN CommonCode CC WITH(NOLOCK) ON WMOD.MOVEORDSTAT = CC.CMCODE AND CC.CMCDTYPE = 'MOVEORDSTAT'
LEFT OUTER JOIN (SELECT MOVEORDID, MAX(ACTDTTM) AS ACTDTTM FROM TB_SFC_WipMoveOrderDetailHistory WITH(NOLOCK) WHERE MOVEORDSTAT = 'E1' GROUP BY MOVEORDID) WOH ON WMOD.MOVEORDID = WOH.MOVEORDID
WHERE WMOD.MOVETYPE = 'MOVE_WAREHOUSE'
AND WMOD.MOVEORDSTAT <> 'C'
AND B.PALLETID BETWEEN '20220424' + '000000' AND '20220425' + '230000'
AND B.PALLETID IS NOT NULL AND IR.SHIPTOID IS NOT NULL
AND B.BOXSTAT <> 'UNPACK'
AND PA.PDMTITEMVALUE = 'C'
AND TS.SHOPID = 'G181'
AND WMOD.SHOPID_FROM = 'G181'
AND 1=1
--查询入库CHIP语句
SELECT
MOVETYPE,
MAX(MOVEORDID) AS MOVEORDID,
MAX(ERP_SNDSEQNO) AS ERP_SNDSEQNO,
MAX(MOVEORDSTAT) as MOVEORDSTAT,
MAX(MOVESTAT) as MOVESTAT,
MOVESEQNO,
SHIPTOID AS CUSTOMERID,
PRODID,
SUM(WIPQTY) as WIPQTY,
OUTER_BOXID,
PALLETID,
PALLETNO,
SUM(ISNULL(BOXQTY_SPLT, 0)) as BOXQTY_SPLT,
MAX(ISNULL(BOXID_PV,'NA')) as BOXID_PV,
SHOPID_FROM,
AREAID_FROM,
EQSGID_FROM,
PROCID_FROM,
SLOCID_FROM,
WHID_FROM,
SHOPID_TO,
AREAID_TO,
EQSGID_TO,
PROCID_TO,
SLOCID_TO,
WHID_TO,
UNIT,
ezmes.ezMES_fn_GET_MMD_PRODITEMNAME('ZH-CN','ITPL012',PRODID) AS PARTNO,
EZMES.EZMES_FN_GET_NAME('ZH-CN',PRODNAME) AS PRODNAME,
INSUSER AS ISSUSER,
SUBSTRING(PALLETID,1,8) AS ISSDATE,
TOP_BOTTOM,
EZMES.EZMES_FN_GET_NAME('ZH-CN', BIZCUSTGRNAME) AS CUSTOMERNAME,
ERP_SNDSTAT AS ERPSTAT,
MOVENOTE_FROM AS MOVEUSER,
MAX(SUBSTRING(PALLETID,1,4) + '-' + SUBSTRING(PALLETID,5,2) + '-' + SUBSTRING(PALLETID,7,2) +' '+ SUBSTRING(PALLETID,9,2) +':'+ SUBSTRING(PALLETID,11,2) +':'+ SUBSTRING(PALLETID,13,2)) AS ISSDTTM,
MAX(EZMES.EZMES_FN_GET_NAME('ZH-CN', SLOCNAME)) AS SLOCID_FROM_NAME,
MAX(EZMES.EZMES_FN_GET_NAME('ZH-CN', EQSGNAME)) AS EQSGID_FROM_NAME,
MAX(EZMES.EZMES_FN_GET_NAME('ZH-CN', PROCNAME)) AS PROCID_FROM_NAME,
MAX(PALLETID_PV) as PALLETID_PV ,
MAX(ERP_SNDSEQNO_PV) as ERP_SNDSEQNO_PV,
IIF(EZMES.EZMES_FN_GET_NAME('ZH-CN',MAX(CMCDNAME)) IS NULL,(SELECT EZMES.EZMES_FN_GET_NAME('ZH-CN',MAX(CC.CMCDNAME)) FROM ezmes.CommonCode CC WITH(NOLOCK) WHERE 'H' = CC.CMCODE AND CC.CMCDTYPE = 'MOVEORDSTAT'), EZMES.EZMES_FN_GET_NAME('ZH-CN',MAX(CMCDNAME))) AS MOVEORDSTAT_NAME,
CONVERT(VARCHAR, MAX(ACTDTTM),112) AS OUTDTTM
FROM #temp
GROUP BY
MOVETYPE,
MOVESEQNO,
SHIPTOID,
PRODID,
OUTER_BOXID,
PALLETID,
PALLETNO,
SHOPID_FROM,
AREAID_FROM,
EQSGID_FROM,
PROCID_FROM,
SLOCID_FROM,
WHID_FROM,
SHOPID_TO,
AREAID_TO,
EQSGID_TO,
PROCID_TO,
SLOCID_TO,
WHID_TO,
UNIT,
ezmes.ezMES_fn_GET_MMD_PRODITEMNAME('ZH-CN','ITPL012',PRODID),
EZMES.EZMES_FN_GET_NAME('ZH-CN',PRODNAME),
INSUSER,
SUBSTRING(PALLETID,1,8),
TOP_BOTTOM,
EZMES.EZMES_FN_GET_NAME('ZH-CN', BIZCUSTGRNAME),
ERP_SNDSTAT,
MOVENOTE_FROM
ORDER BY PALLETID DESC
