topic Re: Performance issue in Application Development and Automation Discussions

Performance issue

Former Member — Thu, 08 Nov 2012 09:55:12 GMT

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.

Re: Performance issue

former_member214709 — Thu, 08 Nov 2012 10:04:36 GMT

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

Re: Performance issue

Former Member — Thu, 08 Nov 2012 10:11:57 GMT

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.

Re: Performance issue

Former Member — Thu, 08 Nov 2012 10:20:29 GMT

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.

Re: Performance issue

Former Member — Thu, 08 Nov 2012 10:21:16 GMT

hi Dinesh,

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

Re: Performance issue

Former Member — Thu, 08 Nov 2012 10:26:12 GMT

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.

Re: Performance issue

Former Member — Thu, 08 Nov 2012 10:50:03 GMT

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.

Re: Performance issue

Former Member — Thu, 08 Nov 2012 10:56:14 GMT

Ok Bilal. Best of luck.

Re: Performance issue

Former Member — Thu, 08 Nov 2012 11:56:13 GMT

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

Re: Performance issue

Former Member — Thu, 08 Nov 2012 12:06:37 GMT

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

Re: Performance issue

former_member214709 — Fri, 09 Nov 2012 07:26:28 GMT

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

Re: Performance issue

yuri_ziryukin — Fri, 09 Nov 2012 09:10:12 GMT

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

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

Re: Performance issue

volker_borowski2 — Fri, 09 Nov 2012 09:53:18 GMT

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

Re: Performance issue

Former Member — Mon, 12 Nov 2012 10:31:39 GMT

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.

Re: Performance issue

volker_borowski2 — Mon, 12 Nov 2012 11:01:09 GMT

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

Re: Performance issue

former_member206439 — Mon, 12 Nov 2012 16:47:56 GMT

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.

Re: Performance issue

volker_borowski2 — Tue, 13 Nov 2012 07:43:30 GMT

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

Re: Performance issue

yuri_ziryukin — Wed, 14 Nov 2012 16:00:20 GMT

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