数据库语句查询慢优化

数据库语句查询慢优化[快了一半!]

最近遇到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

后续补充,还在充电中…!