Performance issue

[Former Member]

the query that is present in the custom program is

   SELECT a~vbeln posnr matnr werks  INTO TABLE gi_vbrp

                       FROM vbrk AS a INNER JOIN vbrp AS b

                       ON a~vbeln = b~vbeln

                       FOR ALL ENTRIES IN gi_valuetab

                       WHERE a~fkdat BETWEEN p_vdat AND p_bdat

                       AND   a~fkart IN li_fkart

                       AND   b~matnr EQ gi_valuetab-matnr

                       AND   b~werks = p_werks.

all the selected data is from vbrp table but the conditons are on vbrk table.

i have tried to use VRPMA table but that does not contain the field WERKS.

Please suggest any alternatives. For all entiries is fetching huge amount of data so that is even slower than the innerjoin.

[former_member214709]

Dear Friend,

You can separate the two select statements using FOR ALL ENTRIES or you can create a database view and use the same in your program or can opt for Function Module or BAPI.

Regards

Dinesh

[Former Member]

hi Dinesh,

Can you suggest some function module to use in this case?

[Former Member]

Go to VRPMA first as it is indexed for Material using gi_valuetab-matnr, and use FKDAT and FKART also there, Then using VBELN and posnr do a select on VBRP using For all entries where you can use WERKS in the where condition.

[Former Member]

hi Rudra,

I have tried that but that reduces the performance(increases the time) as the no of records present in VRPMA is huge in the scale of nearly ten million entries for the given date range, usually the date range is for an year.

[Former Member]

In addition to date range you have to specify the material number when selecting from VRPMA. SAP also suggest that. See the Note 185530. If you do not specify MATNR in the where clause while selecting from VRPMA then it is not going to help you. I think you can use MATNR here while selecting from VRPMA as I can see from your code. Also make sure you do not have any duplicate MATNR in gi_valuetab while using it the driver table of the for all entries.

P.S : If you use material in the where clause while selecting from VRPMA and using For all entries, I am pretty sure that you wont be having 10 million records fetched. It will much less. Cause you would be getting the dump already while filling up gi_valuetab.

[Former Member]

hi Rudra,

I am using all the first 5 key feilds in where condition for VRPMA and then the no of selected records is near to ten million.

but i am not able to use the rest of the key fields since i dont have values for them.

i am in search for a function module or an alternative table in this case to avoid this select.

[Former Member]

Ok Bilal. Best of luck.

[Former Member]

I think that you will need to take out the BETWEEN statment, make with a select options parameter.

[Former Member]

the alternative query that i have tried is

SELECT matnr vbeln posnr INTO TABLE gi_vrpma
FROM vrpma
FOR ALL ENTRIES IN gi_valuetab
WHERE matnr = gi_valuetab-matnr
AND vkorg = l_vkorg
AND fkdat BETWEEN p_vdat AND p_bdat
AND vtweg = '00'
AND fkart IN li_fkart.

IF gi_vrpma[] IS NOT INITIAL.
SELECT vbeln posnr matnr werks INTO TABLE gi_vbrp
FROM vbrp
FOR ALL ENTRIES IN gi_vrpma
WHERE vbeln = gi_vrpma-vbeln
AND posnr = gi_vrpma-posnr
AND matnr = gi_vrpma-matnr
AND werks = p_werks.
ENDIF

.

[former_member214709]

Dear Friend,

Its perfectly fine , go ahead with it.

Whereas You can remove the statement WHERE vtweg = '00' and use DELETE gi_vrpma IS INITIAL.

Regards

Dinesh

[yuri_ziryukin]

One of the rules to follow: filter as soon as possible!

This means never remove filter criteria from WHERE clause and delete in ABAP!

[volker_borowski2]

Hi,

couple of questions:

How many keys in the gi_valuetab table (was that 10 mio, or did I get that wrong?)

What is the average result of the selects below?

SELECT count(*) FROM vbrk AS a
WHERE a~fkdat BETWEEN p_vdat AND p_bdat  

AND   a~fkart IN li_fkart                      

SELECT count(*)
FROM vbrp AS b
AND   b~werks = p_werks.

SELECT count(distinct MATNR)
FROM vbrp AS b
AND   b~werks = p_werks.

What is the DB type of this system?

If Oracle, how many blocks does each of the tables have.

Volker

[Former Member]

hi volker,

gi_valuetab is an internal table and it has no keys.

but i am using sort and delete adjacent delete duplicates.

for thew first select the average result is 3 million

for second its morethan 10 million

for third select it is closer to 2nd select.

i dont have much knowledge about the db systems.

[volker_borowski2]

OK,

my question was not precise. How many lines (MATNRs) do you have in GI_VALUETAB?

Obviously it is the numer of rows in this table that cuts down the 10 mio hits per WERKS by selected MATNRs, so this is an important number.

To see the DB type, simply select Menu SYSTEM->STATUS.

What is the statement that fills GI_VALUETAB.

Reason for this question is, that when filling an internal table, the only accesspath in a FAE select is usually indexdriven, which i.g. results in a so called nesteed loop join (of ABAP to DB).

This is architecture, it is not possible to do other joins with FAEs, beause the internal table is not part of the DB. So the join is split into multiple selects, passing keys from the internal table to the WHERE clause.

Now if you have a big number of rows in the internal table, the nested loop is a bad join type. So an option to change that would be to put the internal table into a DB sub-select. This gives the DB the oportunity to go for a SORT or a HASH join, esp. on Oracle DB, and if it is still bad, you might be able to go parallel on DB level.

If you have many rows in the internal table, it might be better to try this approach.

If it is just a few thousend rows it will probably not help.

To decide about this, you need to know the number of rows and the number of blocks.

Volker

[former_member206439]

Try to use the FOR ALL ENTRIES with BYPASSING BUFFER

Select * FROM XXXX BYPASSING BUFFER

   FOR ALL ENTRIES IN YYYY

Where Condition.

Let me know if there are any issues.

[volker_borowski2]

Hi,

It is a FAE which triggers a DB join (on VBRK and VBRP),

so how should SAP buffering be involved in this statement,

so that it could be of help to disable it????

Did I miss anything here?

Volker

[yuri_ziryukin]

Volker, I though you have already made some experience in SDN.

I don't even comment such "advices" because I'm still waiting for the "Dislike" button