Performance issue with ANLA / ANLZ selection

[former_member184504]

Dear Experts,

There was a query which was  working and taking time and fetching unnecessary data, I have now optimized it and now its fetching exact data required

but it is taking much more time than the older one despite of optimizing it. Please suggest if there are any changes required in the query optimized..

Old Query which was taking time..

{Code}

SELECT ANLN1 into (IT_ASSET-anln1)
from ANLA.
if sy-subrc = 0.
select single CAUFN into it_asset-CAUFN
from anlz
where anln1 = it_asset-anln1
and   werks = 'T401'.
append it_asset.
clear  it_asset.
endif.
ENDSELECT.

{Code}

New Query which is optimized by me..

{Code}

SELECT bukrs anln1 anln2 FROM anla
INTO TABLE it_anla.

IF it_anla[] IS NOT INITIAL.
SORT it_anla BY anln1.
DELETE ADJACENT DUPLICATES FROM it_anla COMPARING anln1.
SELECT bukrs anln1 anln2 bdatu caufn FROM anlz
INTO TABLE it_anlz FOR ALL ENTRIES IN it_anla
WHERE anln1 = it_anla-anln1
AND werks = 'T401'.
ENDIF.
SORT it_anlz BY anln1.
LOOP AT it_anla ASSIGNING <fs_wa>.
ASSIGN COMPONENT 'ANLN1' OF STRUCTURE <fs_wa> TO <field1>.

READ TABLE it_anlz INTO wa_anlz WITH KEY anln1 = <field1> BINARY SEARCH.
IF sy-subrc = 0.
wa_asset-anln1 = wa_anlz-anln1.
wa_asset-caufn = wa_anlz-caufn.
APPEND wa_asset TO it_asset.
ENDIF.
UNASSIGN: <field1>.
CLEAR: wa_anlz.
ENDLOOP.

{Code}

Regards,

Paavan

[Former Member]

Hello,

I guess your query is taking time in extracting records from ANLA as there is no where condition to extract Asset number. You are exracting all asset number from table. This query might give dump in future because of the size of data.

Convince your user and try to use atleast company code or any of the secondary index (which suites your requirement) to filter out the values.

Regards,

Deepti

[former_member184504]

Hi Deepti,

Thanks for the reply.. The query fetching data from table ANLZ is taking time and the

query fetching data from ANLA is not taking time at all..

Regards,

Paavan

[Former Member]

ah..ok because there is no where condition in ANLA so each asset number is fetched and in ANLZ neither Primary nor Secondary index is used hence it is giving performance issue.

Try to pass BUKRS  ANLN1 ANLn2 in select query of ANLZ so that primary index is properly utilized.

SORT it_anla BY bukrs anln1 anln2.
DELETE ADJACENT DUPLICATES FROM it_anla COMPARING bukrs anln1 anln2.

SELECT bukrs anln1 anln2 bdatu caufn FROM anlz
INTO TABLE it_anlz FOR ALL ENTRIES IN it_anla
WHERE bukrs = it_anla-bukrs

  and anln1 = it_anla-anln1

  and anln2 = it_anla-anln2
AND werks = 'T401'.

Regards,

Deepti

[former_member184504]

Hi Deepti,

The query is still taking time.

as per the requirement I need to apply select single query on anlz table (you can see the previous query) and I am going for For all entries in and we can not go for select single with for all entries in. Is there any way to achieve the requirement?

[Sijin_Chandran]

Hi Paavan ,

Set FOR ALL ENTRIES link between ANLA and  ANLZ on all the common primary keys between them

i.e

BUKRS

ANLN1

ANLN2

And do sort IT_ANLA for the fields BUKRS ANLN1 and ANLN2 before fetching from ANLZ.

This may help you out.

[former_member184504]

Hi Sijin,

I have tried the solution that you have given but the query is still taking same time.

and Join would not be appropriate solution to it.

[Sijin_Chandran]

Hi Paaavan ,

Strange!!

Not able to  guess where the actual problem is.

[Former Member]

Hi Paavan,

You could try using a join to improve the performance.

Regards

Nandini

[raymond_giuseppi]

Can you test performance and result with a simpler code like

SELECT DISTINCT anla~anln1 anlz~caufn
  INTO [corresponding fields of] table it_asset
  FROM anla
  JOIN anlz
    ON  anlz~bukrs = anla~bukrs
    AND anlz~anln1 = anla~anln1
    AND anlz~anln2 = anla~anln2
  WHERE anlz~werks = 'T401'.

or even (as you don't seem to test or keep any value from ANLA)

SELECT DISTINCT anln1 caufn
  INTO [corresponding fields of] table it_asset
  FROM anlz
  WHERE anlz~werks = 'T401'

Regards,

Raymond

[former_member184504]

Hi Raymond I have tried the Query you have mentioned. But there in no data in the internal table at all after the execution of the query..

Regards..

[yuri_ziryukin]

Could you please retry with the following code:

• SELECT DISTINCT anla~anln1 anlz~caufn 
•   INTO corresponding fields of table it_asset 
•   FROM anla 
•   LEFT OUTER JOIN anlz 
•     ON  anlz~bukrs = anla~bukrs 
•     AND anlz~anln1 = anla~anln1 
•     AND anlz~anln2 = anla~anln2 
•   WHERE anlz~werks = 'T401'. 

Raymond advised an inner join, however in your first example it was an outer join.

[former_member184504]

Hi Yuri,

But there will be an error while activating that query saying that

No fields from the right-hand table of a LEFT OUTER JOIN may appear in

the WHERE condition: "ANLZ~WERKS".

Regards,

Paavan

[yuri_ziryukin]

Arrr. Ok, then make sure that the field WERKS is also selected into internal table it_asset (add it to the table definition) and remove this WHERE condition.

SELECT DISTINCT anla~anln1 anlz~caufn  anlz~werks

  INTO corresponding fields of table it_asset  

  FROM anla 

  LEFT OUTER JOIN anlz 

    ON  anlz~bukrs = anla~bukrs 

    AND anlz~anln1 = anla~anln1 

    AND anlz~anln2 = anla~anln2.

And then add after the select statement one more loop:

field-symbols: <fs_asset> like line of it_asset.

loop at it_asset assigning <fs_asset> where werks <> 'T401'.

  clear <fs_asset>-caufn.

endloop.

I hope it works.

[raymond_giuseppi]

Yuri,

I read again the first post and he only insert a record in the internal table when there is a record in ANLZ which respects the selection criteria. So I coded an implicit INNER JOIN.

Paavan,

Are there some records in ANLZ with this value of WERKS (SE16) and what is the exact purpose of your selection, as I can read my extract should give the same result as what you wrote ?

Regards,

Raymond

[yuri_ziryukin]

Hello Raymond,

no, you are mistaken.

SELECT ANLN1 into (IT_ASSET-anln1)
from ANLA.
  if sy-subrc = 0.
    select single CAUFN into it_asset-CAUFN
      from anlz
      where anln1 = it_asset-anln1
      and   werks = 'T401'.
    append it_asset.
    clear  it_asset.
  endif.
ENDSELECT.

Read again and check. Append happens in all cases.

Yuri

[former_member184504]

Hi Yuri,

With the query that you have mentioned above inappropriate data is fetched into internal table..

As per the database Internal table should fetch 39 records.But as per the query that you have mentioned it is fetching 288 records and that too it's taking time.

Thanks,

Paavan

[yuri_ziryukin]

Well, check for the number of records in ANLA. This number is the right one. It should be the same in your original code and my code.

[raymond_giuseppi]

In ihis first code, yes, but in second code, the APPEND is in a IF/ENDIF which is not executed if no ANLZ record was found. So i think Paaavan, you should explain what the code is supposed to do, as your two versions don't have same result ?

Else look at

SELECT DISTINCT anln1 caufn
  INTO  TABLE it_asset
  FROM anlz
  WHERE anlz~werks = 'T401'.
SELECT DISTINCT anln1
  FROM anla
 APPENDING CORRESPONDING FIELDS OF TABLE it_asset
  WHERE NOT EXISTS
    ( select * from anlz where anlz~anln1 = anla~anln1 and anlz~werks = 'T401' ).

Regards,

Raymond