質問: The following query takes over an hour on the table to insert. There are no indexes. There was one which was deleted but still takes an hour after deletion. Any pointers on improving this appreciated. ``` INSERT INTO SAPECC_Full.dbo.[vbep] ( [MANDT] , [VBELN] , [VBELN_oco] , [POSNR] , [POSNR_oco] , [ETENR] , [ETTYP] , [LFREL] , [EDATU] , [EZEIT] , [WMENG] , [WMENG_oco] , [BMENG] , [BMENG_oco] , [VRKME] , [LMENG] , [MEINS] , [BDDAT] , [BDART] , [PLART] , [VBELE] , [POSNE] , [ETENE] , [RSDAT] , [IDNNR] , [BANFN] , [BANFN_oco] , [BSART] , [BSTYP] , [WEPOS] , [REPOS] , [LRGDT] , [PRGRS] , [TDDAT] , [MBDAT] , [LDDAT] , [LDDAT_oco] , [WADAT] , [CMENG] , [LIFSP] , [GRSTR] , [ABART] , [ABRUF] , [ROMS1] , [ROMS2] , [ROMS3] , [ROMEI] , [RFORM] , [UMVKZ] , [UMVKN] , [VERFP] , [BWART] , [BNFPO] , [ETART] , [AUFNR] , [PLNUM] , [SERNR] , [AESKD] , [ABGES] , [MBUHR] , [TDUHR] , [LDUHR] , [WAUHR] , [AULWE] , OcoDateChanged ) SELECT RTRIM([MANDT]) , RTRIM([VBELN]) , dbo.SAPLeading0s(VBELN) , RTRIM([POSNR]) , dbo.SAPLeading0s(POSNR) , RTRIM([ETENR]) , RTRIM([ETTYP]) , RTRIM([LFREL]) , RTRIM([EDATU]) , RTRIM([EZEIT]) , RTRIM([WMENG]) , dbo.SAPNumeric(WMENG) , RTRIM([BMENG]) , dbo.SAPNumeric(BMENG) , RTRIM([VRKME]) , RTRIM([LMENG]) , RTRIM([MEINS]) , RTRIM([BDDAT]) , RTRIM([BDART]) , RTRIM([PLART]) , RTRIM([VBELE]) , RTRIM([POSNE]) , RTRIM([ETENE]) , RTRIM([RSDAT]) , RTRIM([IDNNR]) , RTRIM([BANFN]) , dbo.SAPLeading0s(BANFN) , RTRIM([BSART]) , RTRIM([BSTYP]) , RTRIM([WEPOS]) , RTRIM([REPOS]) , RTRIM([LRGDT]) , RTRIM([PRGRS]) , RTRIM([TDDAT]) , RTRIM([MBDAT]) , RTRIM([LDDAT]) , dbo.SAPDate(LDDAT) , RTRIM([WADAT]) , RTRIM([CMENG]) , RTRIM([LIFSP]) , RTRIM([GRSTR]) , RTRIM([ABART]) , RTRIM([ABRUF]) , RTRIM([ROMS1]) , RTRIM([ROMS2]) , RTRIM([ROMS3]) , RTRIM([ROMEI]) , RTRIM([RFORM]) , RTRIM([UMVKZ]) , RTRIM([UMVKN]) , RTRIM([VERFP]) , RTRIM([BWART]) , RTRIM([BNFPO]) , RTRIM([ETART]) , RTRIM([AUFNR]) , RTRIM([PLNUM]) , RTRIM([SERNR]) , RTRIM([AESKD]) , RTRIM([ABGES]) , RTRIM([MBUHR]) , RTRIM([TDUHR]) , RTRIM([LDUHR]) , RTRIM([WAUHR]) , RTRIM([AULWE]) , GETDATE() FROM vbep_full ```
回答: Thats a whole lot of aggregation. I'd start tweeking the MAXDOP option for the query. If you can see the wait types, and you notice a CXPACKET alot for this query, I would set it for 1, else start messing with it to have it scale out the query.