Page 1 of 1

Join AFRU AFVC CRHD to get ARBPL from CHRD

PostPosted: Fri Mar 28, 2014 6:24 am
by surenekr
Hi

I have made query in the abap program to get arbpl field from CHRD by joining AFRU with AFVC fields AUFPL and APLZL. and AFRU join with CRHD ARBID = OBJID WHERE ltxa1 = 'Confirmation Text' AND ktsch = Std text key and STOKZ eq space.


Even I tried the same join made as a view and fetching the details with the where condition in the abap program.

Both the cases the data correctly fetches. But it takes huge time to execute this query.

Any one has any suggestion to improve the performance of this query or view.

CREATE VIEW "ZVIEW_OPRN_DET" ("RUECK", "RMZHL", "BUDAT", "ARBID", "LTXA1", "AUFNR", "VORNR", "KTSCH", "OBJID", "ARBPL",
"WERKS", "STEUS", "AUFPL", "APLZL", "STOKZ", "STZHL") AS SELECT T1."RUECK", T1."RMZHL", T1."BUDAT", T1."ARBID", T1."LTXA1",
T1."AUFNR", T1."VORNR", T2."KTSCH", T3."OBJID", T3."ARBPL", T3."WERKS", T3."STEUS", T2."AUFPL", T2."APLZL", T1."STOKZ",
T1."STZHL" FROM "AFRU" T1, "AFVC" T2, "CRHD" T3 WHERE T1."MANDT" = T2."MANDT" AND T1."AUFPL" = T2."AUFPL" AND T1."APLZL" = T2."APLZL" AND T1."MANDT" = T3."MANDT" AND T1."ARBID" = T3."OBJID".

Regards,
Suren

Re: Join AFRU AFVC CRHD to get ARBPL from CHRD

PostPosted: Fri Mar 28, 2014 7:57 am
by Gothmog
Neither AFRU-LTXA1, AFRU-STOKZ nor CRHD-KTSCH are part of an index on these tables.
You will always do a full table scan with this selection.
Considering the usual size of these tables, no wonder it's very slow.

Re: Join AFRU AFVC CRHD to get ARBPL from CHRD

PostPosted: Sat Mar 29, 2014 12:23 am
by surenekr
Hi,

Agree with you. Is there any alternative way to improve the performance. AFRU-LTXA1 caries a barcode number as reference to reach the crhd to find out the arbpl field.

Regards,
Suren

Re: Join AFRU AFVC CRHD to get ARBPL from CHRD

PostPosted: Sun Mar 30, 2014 8:46 pm
by Sharpshooter
Sounds like a bad process - at confirmation, a workcenter value is input into a text field for later processing? Why not input the workcenter into the workcenter field?

Sometimes even the best code cannot fix a bad process!

Dave