SAP 程序性能分析及提升实例（CDS View+ST05)

最新推荐文章于 2023-03-23 22:16:10 发布

ZevYang

最新推荐文章于 2023-03-23 22:16:10 发布

阅读量943

点赞数

分类专栏： PP 文章标签：程序人生

本文链接：https://blog.csdn.net/ZevYang/article/details/129430256

版权

PP 专栏收录该内容

8 篇文章 1 订阅

订阅专栏

程序性能提升实例

实例介绍
- 检查MES 批次是否有库存

实例介绍

系统要检查批次属性中的MES ID,在SAP中所对应的批次是否有库存，使用的是下面的代码做检查，大概要6秒左右的时间。

检查MES 批次是否有库存

```
 SELECT ausp~objek,
```
```
        ausp~atinn,
```
```
        ausp~atzhl,
```
```
        ausp~mafid,
```
```
        ausp~klart,
```
```
        ausp~adzhl,
```
```
        ausp~atwrt,
```
```
        mcha~matnr,
```
```
        mcha~werks,
```
```
        mcha~charg,
```
```
        SUM( mchb~clabs ) AS clabs
```
```
   FROM ausp
```
```
   LEFT OUTER JOIN mcha
```
```
     ON mcha~cuobj_bm EQ ausp~objek
```
```
   LEFT OUTER JOIN mchb
```
```
     ON mcha~matnr EQ mchb~matnr
```
```
    AND mcha~werks EQ mchb~werks
```
```
    AND mcha~charg EQ mchb~charg
```
```
  WHERE ausp~atinn EQ @l_atinn
```
```
    AND ausp~klart EQ @c_classty
```

    AND ausp~atwrt EQ @x_prodconf-lotid ##SELECT_NULL_VALUES[ATWRT]

   GROUP BY ausp~objek, ausp~atinn, ausp~atzhl,

            ausp~mafid, ausp~klart, ausp~adzhl,

            ausp~atwrt, mcha~matnr, mcha~werks,

```
            mcha~charg
```

   INTO TABLE @DATA(it_mcha_batch)     ##TOO_MANY_ITAB_FIELDS.

纯属看这段代码，以为是没多大提升空间，开发也坚称整个程序没有循环等，没办法提升性能。就这段代码加下101收货和309转仓，一共就要使用10秒种的时间。

最后使用ST05,分析整个程序中，性能最差的地方，就是上文这段代码。
在我建议下，尝试了CDS view的方式去做检查，而不是直接使用AUSP/MCHA等表去查询。

程序员先开发了一个CDS view ZPP_SQL_INSPLOT, 然后使用下面的代码去检查MES批次是否在SAP中所对应的批次有无库存，再做后续的操作。

  SELECT ORACLEBATCH,
         MATERIAL,
         PLANT,
         batch,
         unresqty
    FROM ZPP_CDS_ORACLE_INPLOT "ZPP_SQL_INSPLOT
   WHERE oraclebatch EQ @x_prodconf-lotid
     AND plant eq @x_prodconf-werks_d
    INTO TABLE @DATA(it_mcha_batch).

结论是整体性能提升了70%左右。原来需要10秒左右，现在只需要3.5秒。

CDS view确实是个好东西，尤其是在做一些报表时，使用CDS view可极大的提升效率。之前做过一个BOM批量下载的报表，使用标准的FM,展开一个BOM就快1秒，几万套BOM，想打开一个BOM报表基本是不太现实的，虽然转为后台下载，但其实并不理想。

最后直接了取表，没有使用CDS view，只需要20分钟左右。若使用CDS view，那效率更是快得惊人。
CALL FUNCTION ‘CS_BOM_EXPL_MAT_V2’
EXPORTING
ftrel = ’ ’
alekz = ’ ’
altvo = ’ ’
aufsw = ’ ’
aumgb = ’ ’
aumng = c_aumng "0
auskz = ’ ’
amind = ’ ’
bagrp = ’ ’
beikz = ’ ’
bessl = ’ ’
bgixo = ’ ’
brems = ’ ’
capid = c_pp01 " ‘PP01’
chlst = ’ ’
cospr = ’ ’
cuobj = c_cuobj "000000000000000
cuovs = c_cuovs " 0
cuols = ’ ’
datuv = x_header-start_date " sy-datum "00000000
delnl = ’ ’
drldt = ’ ’
ehndl = ’ ’
emeng = 1
erskz = ’ ’
erssl = ’ ’
fbstp = ’ ’
knfba = ’ ’
ksbvo = ’ ’
mbwls = ’ ’
mktls = c_x
mdmps = ’ ’
mehrs = c_x
mkmat = ’ ’
mmaps = ’ ’
salww = ’ ’
splww = ’ ’
mmory = ’ ’
mtnrv = l_material
nlink = ’ ’
postp = ’ ’
rndkz = ’ ’
rvrel = ’ ’
sanfr = ’ ’
sanin = ’ ’
sanka = ’ ’
sanko = ’ ’
sanvs = ’ ’
schgt = ’ ’
stkkz = ’ ’
stlal = x_afko-stlal
stlan = x_afko-stlan
stpst = c_stpst " 0
svwvo = c_x
werks = l_plant "’ ’
norvl = ’ ’
mdnot = ’ ’
panot = ’ ’
qverw = ’ ’
verid = ’ ’
vrsvo = c_x
sgt_scat = ’ ’
sgt_rel = ’ ’
caller_app = ’ ’
bom_versn = ’ ’
TABLES
stb = it_stb
EXCEPTIONS ##FM_SUBRC_OK
alt_not_found = 1
call_invalid = 2
material_not_found = 3
missing_authorization = 4
no_bom_found = 5
no_plant_data = 6
no_suitable_bom_found = 7
conversion_error = 8
OTHERS = 9.

由于BOM报表中要展示挺多的信息，建了另外两个CDS view基础表，然后再把这两个表加上其它信息，制作出最终的CDS view表，虽然最终没有使用这个报表（要求不断变化，没时间修改），但这个思路非常的好。用户说oracle报表10分钟可以下载出所有的几万套BOM，使用CDS view,两三分钟内可以实现这个目的。

@AbapCatalog.sqlViewName: ‘ZPP_VIEW_BOML2’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: ‘Level 2 BOM explode’
define view ZPP_BOM_LEVEL2
with parameters
Plant :werks_d,
FYear :bdatj,
Period :poper
// Material :matnr,
// oldmat :atwrt,
// MatType :mtart,
// ProdFamily :atwrt,
// MRPControl :dispo,
// ProdType :atwrt,
// ExternalMG :extwg
as select from ZPP_BOM_LEVEL1
left outer join mast on ZPP_BOM_LEVEL1.MATL1 = mast.matnr
and ZPP_BOM_LEVEL1.werks = mast.werks
and ZPP_BOM_LEVEL1.fyear = $parameters.FYear
and ZPP_BOM_LEVEL1.period = $parameters.Period
left outer join stko on stko.stlnr = mast.stlnr
left outer join stpo on stpo.stlty = stko.stlty
and stpo.stlnr = stko.stlnr
left outer join mara on mara.matnr = stpo.idnrk
left outer join marc on marc.matnr = stpo.idnrk
and marc.werks = mast.werks
left outer join makt on mara.matnr = makt.matnr
and makt.spras = $session.system_language
left outer join mbew on mast.matnr = mbew.matnr
and mast.werks = mbew.bwkey
left outer join ckmlcr on mbew.kaln1 = ckmlcr.kalnr
and ckmlcr.bdatj = $parameters.FYear
and ckmlcr.poper = $parameters.Period
and ckmlcr.curtp = ‘30’
left outer join zleddwmaterials on zleddwmaterials.material = stpo.idnrk
left outer join ZPP_BOM_LEVEL3 on ZPP_BOM_LEVEL1.Topmat = ZPP_BOM_LEVEL3.Topmat
and ZPP_BOM_LEVEL1.MATL1 = ZPP_BOM_LEVEL3.matl1
and ZPP_BOM_LEVEL1.werks = ZPP_BOM_LEVEL3.werks
and ZPP_BOM_LEVEL3.fyear = $parameters.FYear
and ZPP_BOM_LEVEL3.period = $parameters.Period

{
@EndUserText.label: ‘Bom Item’
key ZPP_BOM_LEVEL1.Topmat as Topmat,
key ZPP_BOM_LEVEL1.matkll1 as matkll1,
key ZPP_BOM_LEVEL1.extwgl1 as extwg,
@EndUserText.label: ‘Phantom Item’
key ZPP_BOM_LEVEL1.MATL1 as matl1,
@EndUserText.label: ‘Component ID’
key stpo.posnr as compid,
@EndUserText.label: ‘Component’
key case when stpo.idnrk is null then ZPP_BOM_LEVEL1.MATL1 else stpo.idnrk end as comp,
@EndUserText.label: ‘Component Desc’
case when stpo.idnrk is null then ZPP_BOM_LEVEL1.maktxl1 else makt.maktx end as maktx,
@EndUserText.label: ‘Component UoM’
case when stpo.idnrk is null then ZPP_BOM_LEVEL1.meinsl1 else stpo.meins end as meins,
@EndUserText.label: ‘1:1 Qty’
case when stpo.idnrk is null then ZPP_BOM_LEVEL1.mengel2 else
cast( division( stpo.menge, stko.bmeng, 6 ) as abap.fltp ) * cast( ZPP_BOM_LEVEL1.mengel1 as abap.fltp )
end as menge,
@EndUserText.label: ‘Material Group’
case when stpo.idnrk is null then ZPP_BOM_LEVEL3.matkll2 else mara.matkl end as matkl,
@EndUserText.label: ‘SPK’
marc.sobsl as sobsl,
@EndUserText.label: ‘Material Status’
case when stpo.idnrk is null then ZPP_BOM_LEVEL3.mmstal2 else marc.mmsta end as mmsta,
@EndUserText.label: ‘AltItem Group’
stpo.alpos as alpos,
@EndUserText.label: ‘Usage Probability’
stpo.ewahr as ewahr,
@EndUserText.label: ‘Cost Relevancy’
case when stpo.idnrk is null then ZPP_BOM_LEVEL1.sankal1 else stpo.sanka end as sanka,
@EndUserText.label: ‘Component Price’
case when stpo.idnrk is null then ZPP_BOM_LEVEL3.stprsl3 else division( ckmlcr.stprs, ckmlcr.peinh, 6 ) end as stprsl2,
@EndUserText.label: ‘Master Std Cost’
ZPP_BOM_LEVEL1.stpl1 as stpl1,
@EndUserText.label: ‘BoM Cost’
cast( division( ckmlcr.stprs, ckmlcr.peinh, 6 ) as abap.fltp ) *
cast( division( stpo.menge, stko.bmeng, 6 ) as abap.fltp ) *
cast( ZPP_BOM_LEVEL1.mengel1 as abap.fltp ) as bomcost,
@EndUserText.label: ‘BoM Status’
ZPP_BOM_LEVEL1.stlstl1 as stlstl1,
@EndUserText.label: ‘FG Prod Line’
ZPP_BOM_LEVEL1.prodlinel1 as prodlinel1,
@EndUserText.label: ‘FG Prod Family’
ZPP_BOM_LEVEL1.prodfaml1 as prodfaml1,
@EndUserText.label: ‘Comp Cost Elmt’
case when stpo.idnrk is null then ZPP_BOM_LEVEL3.costeltl2 else zleddwmaterials.cost_element end as cost_element,
@EndUserText.label: ‘Change Number’
stpo.aennr as aennr,
@EndUserText.label: ‘Date Created On’
stpo.andat as andat,
@EndUserText.label: ‘User Who Creted’
stpo.annam as annam,
@EndUserText.label: ‘Last Changed Date’
stpo.aedat as aedat,
@EndUserText.label: ‘User Who Changed’
stpo.aenam as aenam
}
where
ZPP_BOM_LEVEL1.werks = $parameters.Plant
group by ZPP_BOM_LEVEL1.Topmat, ZPP_BOM_LEVEL1.matkll1, ZPP_BOM_LEVEL1.extwgl1,
ZPP_BOM_LEVEL1.MATL1, stpo.posnr, stpo.idnrk, ZPP_BOM_LEVEL1.maktxl1, makt.maktx,
ZPP_BOM_LEVEL1.meinsl1, stpo.meins, ZPP_BOM_LEVEL1.mengel2, stpo.menge, stko.bmeng,
ZPP_BOM_LEVEL1.mengel1, ZPP_BOM_LEVEL3.matkll2, mara.matkl, marc.sobsl, ZPP_BOM_LEVEL3.mmstal2,
marc.mmsta, stpo.alpos, stpo.ewahr, ZPP_BOM_LEVEL1.sankal1, stpo.sanka, ZPP_BOM_LEVEL3.stprsl3,
ckmlcr.stprs, ckmlcr.peinh, ZPP_BOM_LEVEL1.stpl1, ZPP_BOM_LEVEL1.stlstl1, ZPP_BOM_LEVEL1.prodlinel1,
ZPP_BOM_LEVEL1.prodfaml1, ZPP_BOM_LEVEL3.costeltl2, zleddwmaterials.cost_element, stpo.aennr, stpo.andat,
stpo.annam, stpo.aedat, stpo.aenam

// and ZPP_BOM_LEVEL1.MATL1 = :Material
// and ZPP_BOM_LEVEL1.oldmatl1 = :oldmat
// and ZPP_BOM_LEVEL1.mtartl1 = :MatType
// and ZPP_BOM_LEVEL1.prodfaml1 = :ProdFamily
// and ZPP_BOM_LEVEL1.dispol1 = :MRPControl
// and ZPP_BOM_LEVEL1.prodtypl1 = :ProdType
// and ZPP_BOM_LEVEL1.extwgl1 = :ExternalMG