Solved: Performance problem code ABAP

anibal_alonso · ‎10-14-2015

Hi, I have a program, which has performance problems. Ztmm_0208 table contains millions of rows, to which I have access to all. I need to find a way to improve performance. I don't know many tecnicals (parallel cursor, etc),I'm new to this, can you help with this code? thanks in advance

FORM f_repro_ser.

* recovery series that have not yet guarantee date

SELECT *

FROM ztmm_0208

INTO TABLE i_reproc.

LOOP AT i_reproc INTO w_reproc.

* Recover Object Number

SELECT SINGLE objnr FROM equi

INTO w_equi_reproc

WHERE equnr EQ w_reproc-equnr.

IF sy-subrc EQ 0.

* Recovery of warranty start date

SELECT SINGLE gwldt

FROM bgmkobj

INTO w_bgmkobj

WHERE j_objnr EQ w_equi_reproc-objnr AND

gaart EQ dc_4 AND

gwldt NE space.

IF sy-subrc EQ 0.

CLEAR w_envio.

w_envio-sernr = p_w_objk_sernr.

w_envio-gwldt = p_w_bgmkobj_gwldt.

APPEND w_envio TO i_envio.

CLEAR w_noserie.

w_noserie-sernr = p_w_objk_sernr.

w_noserie-equnr = p_w_objk_equnr.

APPEND w_noserie TO i_noserie.

ENDIF.

ENDLOOP.

former_member210008 · ‎10-14-2015

It's very similar to your start post code.

At first test next query:


select required_fields
     from ztmm_0208 as a join equi as b on a~equnr = b~equnr
                                        join bgmkobj as c on b~objnr = c~j_objnr
     where c~gaart = 4
         and c~gwldt <> space

raphael_almeida · ‎10-14-2015

Hi Aníbal...

First to all, remove the selects from loop...

How do you have declare the itabs? My suggestion is (If you don't have do it) is declare them TYPE HASHED TABLE.

Warm regards,

Raphael Pacheco.

roberto_vacca2 · ‎10-14-2015

Hi

You should use INNER JOIN between

ztmm_0208 & equi & bgmkobj

In second advice, FOR ALL ENTRIES in EQUI and BGMKOBJ. But it's not sure you'll have a performance gain.

Hope to help

Bye

former_member210008 · ‎10-14-2015

2 ways:

1) replace all selects with join. Optionally add indexes for joinable fields.

2) select equi and bgmkobj into internal tables and then use binary search.

Solution depends on records amount and join performance (so you need to test both variants). Also limit select from z-table if possible.

anibal_alonso · ‎10-14-2015

And using this??

OPEN CURSOR l_cursor FOR

SELECT sernr equnr
FROM ztmm_0208
BYPASSING BUFFER
ORDER BY PRIMARY KEY.

WHILE NOT l_cursor IS INITIAL.

IF NOT l_cursor IS INITIAL.

FETCH NEXT CURSOR l_cursor INTO TABLE i_reproc.

IF sy-subrc EQ 0.

   * Recupero Número de objeto
       SELECT SINGLE objnr FROM equi
          INTO w_equi_reproc
          WHERE equnr EQ w_reproc-equnr.

IF sy-subrc EQ 0.

    * Recupero de fecha inicio garantía
          SELECT SINGLE gwldt
            FROM bgmkobj
            INTO w_bgmkobj
            WHERE j_objnr EQ w_equi_reproc-objnr AND
                        gaart EQ dc_4 AND
                        gwldt NE space.

IF sy-subrc EQ 0.

CLEAR w_envio.

w_envio-sernr = p_w_objk_sernr.

w_envio-gwldt = p_w_bgmkobj_gwldt.

APPEND w_envio TO i_envio.

CLEAR w_noserie.

w_noserie-sernr = p_w_objk_sernr.

w_noserie-equnr = p_w_objk_equnr.

APPEND w_noserie TO i_noserie.

         ENDIF.

ELSE
   CLOSE CURSOR l_cursor.
ENDIF.

    ENDIF.

ENDWHILE.

Thanks for your answers, can you put the code of your solutions? so I can compare it with the ST05, regards

roberto_vacca2 · ‎10-14-2015

Hi

Open CURSOR on a modern database only decouple the process from the SELECT but really doesn't give any more performance gain.

You can prove it with a simple read code example in abap. You will obtain low changes on performance time from ST05.

Instead you should use JOIN and internal table, with a binary search if necessary, and decide, with customer support, a kind of filter on data selection. You can work on a solution of a job in background.

Hope to help

Bye

anibal_alonso · ‎10-14-2015

thanks Roberto, instead, the program is in background

former_member226239 · ‎10-14-2015

Can you try the below code and let me know the performance? If you can put a where clause for the z-table then the performance will be improved even more.

types: begin of ty_equnr,

equnr type equnr,

gwldt type gwldt,

end of ty_equnr,

ty_t_equnr type hashed table of ty_equnr with unique key equnr.

data: ls_equi type ty_equnr,

lt_equi type ty_t_equnr.

* recovery series that have not yet guarantee date

select *

from ztmm_0208

into table i_reproc.

" Assuming i_reproc is a standard table

lt_reproc[] = i_reproc[].

delete lt_reproc where equnr is initial.

sort lt_reproc by equnr.

delete adjacent duplicates from lt_reproc comparing equnr.

" Get the required Equipments

select a~equnr b~gwldt

from equi as a

inner join bgmkobj as b

on a~objnr = b~j_objnr

into table lt_equi

for all entries in lt_reproc

where a~equnr = lt_reproc-equnr

and b~gaart = '4'

and b~gwldt <> space. " Do you need this condition?? "gwldt ne space"

loop at i_reproc into w_reproc.

read table lt_equi into ls_equi

with table key equnr = w_reproc-equnr.

if sy-subrc eq 0.

clear w_envio.

w_envio-sernr = p_w_objk_sernr.

w_envio-gwldt = p_w_bgmkobj_gwldt.

append w_envio to i_envio.

clear w_noserie.

w_noserie-sernr = p_w_objk_sernr.

w_noserie-equnr = p_w_objk_equnr.

append w_noserie to i_noserie.

endif.

endloop.

- Chandra

anibal_alonso · ‎10-15-2015

Thanks for your answer, the performance is better. The main problem is the where the ztmm_0208.

former_member378318 · ‎10-15-2015

Hi,

Have a play with the following and see if it helps with speeding up the initial select. You can tweak the package size to whatever gives the best performance.

OPEN CURSOR WITH HOLD s_cursor FOR

SELECT * FROM ztmm_0208.

DO.

FETCH NEXT CURSOR s_cursor APPENDING TABLE i_reproc PACKAGE SIZE 1000.

IF sy-subrc <> 0.

EXIT. "<<<<< could try loop over i_reproc here

ENDIF.

ENDDO.

You could even try moving the loop over i_reproc into the place indicated above, but replace "APPENDING TABLE" with "INTO TABLE".

former_member210008 · ‎10-14-2015

It's very similar to your start post code.

At first test next query:


select required_fields
     from ztmm_0208 as a join equi as b on a~equnr = b~equnr
                                        join bgmkobj as c on b~objnr = c~j_objnr
     where c~gaart = 4
         and c~gwldt <> space

anibal_alonso · ‎10-14-2015

I obtain with the st05 a bad performance (01:36 minuts and 6.000.000 rows)

SQL Statement

----------------------------------------------------------------------------------------------------------------------

SELECT

a.sernr, a.equnr

FROM

ztmm_0208 a join equi b on a.equnr = b.equnr and b.mandt =: a2 and a.mandt =: a3 join bgmkobj c

on b.objnr = c.j_objnr

WHERE

c.gaart =: a0 and c.gwldt <>: " " and c.mandt =: a1

Execution Plan

----------------------------------------------------------------------------------------------------------------------

System: ET0

Plan hash value: 2870109393

------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 5943K| 691M| | 471K (2)| 01:36:16 | | | |

| 1 | PX COORDINATOR | | | | | | | | | |

| 2 | PX SEND QC (RANDOM) | :TQ10004 | 5943K| 691M| | 471K (2)| 01:36:16 | Q1,04 | P->S | QC (RAND) |

|* 3 | HASH JOIN BUFFERED | | 5943K| 691M| 61M| 471K (2)| 01:36:16 | Q1,04 | PCWP | |

| 4 | JOIN FILTER CREATE | :BF0000 | 5268K| 432M| | 393K (2)| 01:20:13 | Q1,04 | PCWP | |

| 5 | PX RECEIVE | | 5268K| 432M| | 393K (2)| 01:20:13 | Q1,04 | PCWP | |

| 6 | PX SEND HASH | :TQ10002 | 5268K| 432M| | 393K (2)| 01:20:13 | Q1,02 | P->P | HASH |

|* 7 | HASH JOIN BUFFERED | | 5268K| 432M| | 393K (2)| 01:20:13 | Q1,02 | PCWP | |

| 8 | BUFFER SORT | | | | | | | Q1,02 | PCWC | |

| 9 | PX RECEIVE | | 5268K| 211M| | 5033 (3)| 00:01:02 | Q1,02 | PCWP | |

| 10 | PX SEND HASH | :TQ10000 | 5268K| 211M| | 5033 (3)| 00:01:02 | | S->P | HASH |

|* 11 | TABLE ACCESS FULL| ZTMM_0208 | 5268K| 211M| | 5033 (3)| 00:01:02 | | | |

| 12 | PX RECEIVE | | 354M| 14G| | 387K (1)| 01:19:07 | Q1,02 | PCWP | |

| 13 | PX SEND HASH | :TQ10001 | 354M| 14G| | 387K (1)| 01:19:07 | Q1,01 | P->P | HASH |

| 14 | PX BLOCK ITERATOR | | 354M| 14G| | 387K (1)| 01:19:07 | Q1,01 | PCWC | |

|* 15 | TABLE ACCESS FULL| EQUI | 354M| 14G| | 387K (1)| 01:19:07 | Q1,01 | PCWP | |

| 16 | PX RECEIVE | | 23M| 803M| | 76052 (2)| 00:15:32 | Q1,04 | PCWP | |

| 17 | PX SEND HASH | :TQ10003 | 23M| 803M| | 76052 (2)| 00:15:32 | Q1,03 | P->P | HASH |

| 18 | JOIN FILTER USE | :BF0000 | 23M| 803M| | 76052 (2)| 00:15:32 | Q1,03 | PCWP | |

| 19 | PX BLOCK ITERATOR | | 23M| 803M| | 76052 (2)| 00:15:32 | Q1,03 | PCWC | |

|* 20 | TABLE ACCESS FULL | BGMKOBJ | 23M| 803M| | 76052 (2)| 00:15:32 | Q1,03 | PCWP | |

------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$9E43CB6E

11 - SEL$9E43CB6E / A@SEL$1

15 - SEL$9E43CB6E / B@SEL$1

20 - SEL$9E43CB6E / C@SEL$2

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("B"."OBJNR"="C"."J_OBJNR")

7 - access("A"."EQUNR"="B"."EQUNR")

11 - filter("A"."MANDT"=:A3)

15 - filter("B"."MANDT"=:A2)

20 - filter("C"."GAART"=:A0 AND "C"."MANDT"=:A1 AND "C"."GWLDT"<>: AND SYS_OP_BLOOM_FILTER(:BF0000,"C"."J_OBJNR"))

former_member210008 · ‎10-14-2015

And how long it was in your original code with select single?

How much records returns from select * from bgmkobj where gaart = 4 and gwldt <> space ? And in EQUI?

Maybe it will faster with something like this:


select *
  from bgmkobj as a join equi as b on a~j_objnr = b~objnr
                                                and a~gaart = 4
                                                and a~gwldt <> space
                                join ztmm_0208 as c on b~equnr = c~equnr.

If you will not add where clause for ztmm_0208 table you wont be able to select data faster than just select * from ztmm_0208

Typo is sql fixed

Typo in sql

anibal_alonso · ‎10-14-2015

With the original code:

SQL Statement

----------------------------------------------------------------------------------------------------------------------

SELECT

*

FROM

ztmm_0208

WHERE

mandt =: a0

Execution Plan

----------------------------------------------------------------------------------------------------------------------

System: ET0

Plan hash value: 909955081

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 5268K| 211M| 5033 (3)| 00:01:02 |

|* 1 | TABLE ACCESS FULL| ZTMM_0208 | 5268K| 211M| 5033 (3)| 00:01:02 |

-------------------------------------------------------------------------------

With select * from bgmkobj where gaart = 4 and gwldt <> space:

SQL Statement

----------------------------------------------------------------------------------------------------------------------

SELECT

*

FROM

bgmkobj

WHERE

gaart =: a0 and gwldt <>: " " and mandt =: a1

Execution Plan

----------------------------------------------------------------------------------------------------------------------

System: ET0

Plan hash value: 563672998

--------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 23M| 2052M| 76089 (2)| 00:15:32 | | | |

| 1 | PX COORDINATOR | | | | | | | | |

| 2 | PX SEND QC (RANDOM)| :TQ10000 | 23M| 2052M| 76088 (2)| 00:15:32 | Q1,00 | P->S | QC (RAND) |

| 3 | PX BLOCK ITERATOR | | 23M| 2052M| 76088 (2)| 00:15:32 | Q1,00 | PCWC | |

|* 4 | TABLE ACCESS FULL| BGMKOBJ | 23M| 2052M| 76088 (2)| 00:15:32 | Q1,00 | PCWP | |

--------------------------------------------------------------------------------------------------------------

I thinks that the problem is that, the table ztmm_0208 without where.... ¿any solutions? thanks

former_member210008 · ‎10-14-2015

Please use CAT tcode for your original code to get full time including loop... endloop statement.

If you cant add where clause for z-table there is no good solution.

Other ways you can try:

select from ztmm_0208 into table T1

sort it by equnr and delete duplucates if equnr is not keyfield

select from equi for all entries in T1 into table T2

sort and delete duplicates if necessary

select from bgmkobj for all entries in T2 into table T3

you got 23M records from bgmkobj? It's too much so it wont be faster to fetch all tables into internal and read them with binary search.

Is 90-100 seconds for join bad for you?