Vbrk and Vbrp tables performance problem

[Former Member]

Hi all,

The below query is taking lot of time in QAS i need to decrease the time without affecting the logic.....

SELECT vbrk~kunag

vbkd~bstkd

vbkd~bstdk

INTO TABLE c_tab_po FROM vbrp

INNER JOIN vbrk ON vbrk_{mandt = vbrp}mandt AND vbrk~vbeln =

vbrp~vbeln

INNER JOIN vbkd ON vbkd_{mandt = vbrp}mandt AND vbkd~vbeln =

vbrp~aubel

AND vbkd~posnr = con_zposnr

WHERE vbrp~vbeln EQ nast-objky.

DELETE ADJACENT DUPLICATES FROM c_tab_po COMPARING ALL FIELDS.

*Getting the other Invoices based on the customer PO number

IF c_tab_po[] IS NOT INITIAL.

SELECT

vbrk~kunag

vbkd~bstkd

vbkd~bstdk

vbkd_{posex_e vbrk}vkorg

vbrp_{vbeln vbrp}posnr vbrp~erdat

vbpa_{kunnr vbrp}aubel vbrp_{aupos vbak}erdat

vbrp_{matnr vbrk}mwsbk vbrp~netwr

vbrk_{valdt vbrk}zterm vbrk~vbtyp

INTO TABLE g_tab_invoice FROM vbrp

INNER JOIN vbrk ON vbrk_{mandt = vbrp}mandt AND vbrk~vbeln =

vbrp~vbeln

INNER JOIN vbkd ON vbkd_{mandt = vbrp}mandt AND vbkd~vbeln =

vbrp~aubel

AND vbkd~posnr = con_zposnr

INNER JOIN vbpa ON vbpa_{mandt = vbrp}mandt AND vbpa~vbeln =

vbrp~aubel

AND vbpa_{posnr = con_zposnr AND vbpa}parvw =

con_parvw

INNER JOIN vbak ON vbak_{mandt = vbak}mandt AND vbak~vbeln =

vbrp~aubel

FOR ALL entries IN c_tab_po

WHERE vbrk~kunag EQ c_tab_po-kunag AND

vbkd~bstkd EQ c_tab_po-bstkd AND

vbkd~bstdk EQ c_tab_po-bstdk.

ENDIF.

[Former Member]

You can create a view to reduce the time becuase it is alwayss to create a view if you are joining more tables.

One more thing you are using for all entries statement but you should be careful while using this statement because for the large no of records this statement gives dump.

[Former Member]

Is it possbile for us to use the view in the logic???

[Former Member]

Hi Younus,

Try to look for a FM to fetch all the entries from the table & then later filter it with the condition .

-Bhumika

[former_member194613]

if you are working on these table, always check the SAP note 185530 first, it tells you where you must use index-tables.

Siegfried

[Former Member]

If all else fails, you can do the RFC method: https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/library/abap/performance%2...

The document does not go into a 'monitoring table', which is recommended.

I hope that this helps,

Dan P.

[Former Member]

Hi Younus,

First join seems to be oku2026

But you are querying Db tables multiple times to avoid this.

Fetch the fields

vbkd_{posex_e vbrk}vkorg

vbrp_{vbeln vbrp}posnr vbrp~erdat

vbrp_{aubel vbrp}aupos

vbrp_{matnr vbrk}mwsbk vbrp~netwr

vbrk_{valdt vbrk}zterm vbrk~vbtyp

in the first join itself.

SELECT vbrk~kunag
vbkd~bstkd
vbkd~bstdk
vbkd~posex_e vbrk~vkorg
vbrp~vbeln vbrp~posnr vbrp~erdat
vbrp~aubel vbrp~aupos 
vbrp~matnr vbrk~mwsbk vbrp~netwr
vbrk~valdt vbrk~zterm vbrk~vbtyp
INTO TABLE c_tab_po FROM vbrp
INNER JOIN vbrk ON vbrk~mandt = vbrp~mandt AND vbrk~vbeln =
vbrp~vbeln
INNER JOIN vbkd ON vbkd~mandt = vbrp~mandt AND vbkd~vbeln =
vbrp~aubel
AND vbkd~posnr = con_zposnr
WHERE vbrp~vbeln EQ nast-objky.

then Just join VBPA & VBAK and get required data.

Thanks,

Sudha

[aarif_baig]

Hi,

you should use view in this case ...

that will definitely improve the performance

Edited by: Aarif Baig on May 20, 2009 2:02 PM

[former_member182566]

>

>     you should use view in this case ...
> that will definitely improve the performance

before making suggestions like this you should sit back and think why on earth a view (which is not more than joined tables) would be better than having the joins in the actual select.

have you ever actually improved any query by changing it to a view?

(well, one advantage of using the view is if you are really screwing things up in the join, and at least the person that created the view might know better)

[aarif_baig]

view always has an advantage

may be you need to think again

[ThomasZloch]

Views are good for reusability, once you found an efficient way to link tables A, B and C you can "fix" the link inside a database view (inner joins only), but there is no difference in performance.

Thomas

[Former Member]

>

> view always has an advantage 

Can you provide an example - with run times and traces?

Rob

[Former Member]

create view for that tables and do it

[sujeet2918]

Dear Younus,

SAP itself never recommends to use Joins where heavy data is to be retrieved.

better you use seperate internal table for each table and use keyword FOR ALL ENTRIES to select data according to previous table entry. do this serially.

Have a Nice Day,

Cheers,

Sujeet

Edited by: Sujeet Mishra on May 21, 2009 8:44 AM

Edited by: Sujeet Mishra on May 21, 2009 8:47 AM

[Former Member]

Sujeet please see at the top of this forum before posting..

Rob

[Former Member]

i resolved it