0

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
  • 4
    How long time does it take if you remove the calls to UDF's? – Mikael Eriksson Jan 27 '14 at 11:50
  • 1
    Do you have enough (without having to do auto-growth) pre-allocated space in the DB to hold the new table? – Mikael Eriksson Jan 27 '14 at 11:53
  • 1
    That was @MikaelEriksson's point, if you have enough space pre-allocated for the mdf/ldf files and don't have to endure auto-growth operations (which are IO expensive and occur at worse time possible - during your load). – Marian Jan 27 '14 at 12:15
  • 2
    Why are you assuming there is enough space when you could check (e.g. monitor for growth events)? Did you check the log file settings and what it's doing during this time? Did you check dm exec requests, dm os waiting tasks etc. to see what the insert is spending time on? – Aaron Bertrand Jan 27 '14 at 12:33
  • 1
    You have a performance problem and so analyze it like a performance problem: measure. Collect wait stats, performance counters, identity the bottleneck. Read How to analyse SQL Server performance – Remus Rusanu Apr 2 '14 at 16:31
1

You are asking SQL Server to perform 19.5 Billion string manipulations (300,000,000 rows X 65 columns), all in one giant transaction. That is assuming vbep_Full is a regular table with simple column definitions (not a view with joins to other tables and/or with computed comlumns).

If your CPU is not maxed out, you could try breaking the work into multiple parts, process each part in parallel.

I would also recommend working in small batches with implicit transactions. That way if one of the batches fails, you could start that batch over again without having to start the other batches over.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.