topic Re: JOIN optimize PROJ - AFRU in Application Development and Automation Discussions

JOIN optimize PROJ - AFRU

former_member378419 — Mon, 10 Aug 2009 07:30:37 GMT

Hi,

Has anybody idea if it is possible to optimize this query?

I suppose that join should be the best, what I can get... but it is still very very slow. I need data from AFRU, AFVC, AFVV per project num (PROJ), so there are thousands of lines in result. Now I am able to get data only per one AUFNR(AFPO) - few minutes, per POSID(PRPS) or PSPID(PROJ) it falls down after 10 minutes.

Is it better to divide so difficult query or use another techniques? Or change order of tables in query?

Thanks for help in advance.

SELECT proj_{pspid prps}posid afru_{aufnr afpo}matnr makt_{maktx afpo}aufnr

afvc_{vornr afvc}ltxa1 afvv_{mgvrg crhd}arbpl crtx_{ktext afvv}vgw01

afvv_{vgw02 afru}lmnga afru_{ism01 afru}ism02 afru_{ersda afru}ernam

afru_{pernr afru}rueck afru_{ile01 afvv}vge01 afvv~meinh

INTO CORRESPONDING FIELDS OF TABLE it_zppprodtime

FROM afru

INNER JOIN afpo

ON afru_{aufnr = afpo}aufnr

INNER JOIN prps

ON afpo_{projn = prps}pspnr

INNER JOIN proj

ON prps_{psphi = proj}pspnr

INNER JOIN crhd

ON crhd_{objid = afru}arbid

INNER JOIN afvc

ON afvc_{aufpl = afru}aufpl

AND afvc_{aplzl = afru}aplzl

INNER JOIN afvv

ON afvv_{aufpl = afru}aufpl

AND afvv_{aplzl = afru}aplzl

INNER JOIN makt

ON makt_{matnr = afpo}matnr

INNER JOIN crtx

ON crtx_{objid = crhd}objid

WHERE

proj~pspid IN s_pspid

AND prps~posid IN s_projn

AND crhd~arbpl IN s_arbpl

AND afru~aufnr IN s_aufnr

AND makt~spras = 'CS'

and crtx~spras = 'CS'.

Re: JOIN optimize PROJ - AFRU

HermannGahm — Mon, 10 Aug 2009 09:12:18 GMT

Hi,


> Is it better to divide so difficult query or use another techniques? Or change order of tables in query?

no, it should not be necessary to divide the query. The table order is (and normally should be) decided

by the optimizer based on the statistics.

What matters is the access to the individual tables. The AUFNR is probaly supported by an index (primary key of AFPO)

while the others are probably not supported.

For your most selective WHERE condition: check if there is an index support and if the optimzer chooses the index and the

table to be processed first.

If you need further help please post details from ST05 (execution plan, available indexes, statement like it is sent to the database (which variables are filled with what values), time per execution, how much records are returned, ...)

Kind regards,

Hermann

Re: JOIN optimize PROJ - AFRU

Former Member — Mon, 10 Aug 2009 15:18:48 GMT

Hi Martin,

I had a few concerns.

1) In real life it is unlikely that that you will get users to make queries on all the orders. It appears more likely to me that users would enter a project number/s in the selection screen. Given that it makes more sense constructing your query to begin with project number rather than order number. I have taken the liberty of modifying the query to give you an example.

2) I noticed that your query reads table CRHD using field OBJID. The primary key is based on fields OBJTY and OBJID. If you know the OBJTY you can use it in the where clause. This will let you use the primary key. Remember CRHD is a huge table.

3) You can use the OBJID from CRHD table to join table CRTX. (Look at the query below).

SELECT proj~pspid
       prps~posid
       afru~aufnr
       afpo~matnr
       makt~maktx
       afpo~aufnr
       afvc~vornr
       afvc~ltxa1
       afvv~mgvrg
       crhd~arbpl
       crtx~ktext
       afvv~vgw01
       afvv~vgw02
       afru~lmnga
       afru~ism01
       afru~ism02
       afru~ersda
       afru~ernam
       afru~pernr
       afru~rueck
       afru~ile01
       afvv~vge01
       afvv~meinh
INTO CORRESPONDING FIELDS OF TABLE it_zppprodtime
FROM       proj
inner join prps
on  prps~psphi = proj~pspnr
inner join afpo
ON  afpo~projn = prps~pspnr
inner join afru
on  afru~aufnr = afpo~aufnr
INNER JOIN afvc
ON  afvc~aufpl  = afru~aufpl
AND afvc~aplzl = afru~aplzl
INNER JOIN afvv
ON  afvv~aufpl = afru~aufpl
AND afvv~aplzl = afru~aplzl
INNER JOIN makt
ON  makt~matnr = afpo~matnr
INNER JOIN crhd
ON  crhd~objid = afru~arbid
INNER JOIN crtx
ON  crtx~objTY = crhd~objty
AND crtx~objid = crhd~objid
WHERE proj~pspid IN s_pspid
AND   prps~posid IN s_projn
AND   crhd~arbpl IN s_arbpl
AND   afru~aufnr IN s_aufnr
AND   makt~spras = 'CS'
AND   crhd~objty = ????
AND   crtx~spras = 'CS'.

Re: JOIN optimize PROJ - AFRU

former_member378419 — Mon, 17 Aug 2009 11:41:49 GMT

Thanks for answers. Finally I changed few things in program and surprisingly it works OK, now:-)

Worse is that I don't know what exactly made this program running so slow, but I didn't change query very much. I think it was something connected to sorting in ALV grid. I have switched off sorting in program at all.

Re: JOIN optimize PROJ - AFRU

Former Member — Thu, 27 Aug 2009 14:36:53 GMT

This message was moderated.