topic Re: Performance Impovement for Join in Application Development and Automation Discussions

Performance Impovement for Join

Former Member — Thu, 18 Oct 2007 23:35:12 GMT

I have the following join statement. I have 220,000 records ... when I run this program, it times out. Can any one suggest performance impovement?

SELECT vbap_{vbeln vbap}posnr vbap_{kwmeng vbap}matnr

vbap_{werks vbap}kdmat vbap_{posex vbak}auart

vbak_{kunnr vbak}vdatu vbep_{edatu lips}vbeln

lips_{posnr lips}lfimg marc_{dispo marc}beskz "--> PV

makt_{maktx mara}meins vbkd_{ihrez vbkd}empst

vbkd_{bstkd vbkd}bstkd_e vbpa~ablad

INTO TABLE lt_orders_t

FROM vbak INNER JOIN vbap

ON ( vbak_{vbeln = vbap}vbeln )

INNER JOIN vbep

ON ( vbap_{posnr = vbep}posnr AND vbap_{vbeln = vbep}vbeln )

LEFT OUTER JOIN lips

ON ( vbap_{vbeln = lips}vgbel AND vbap_{posnr = lips}vgpos )

LEFT OUTER JOIN marc

ON ( vbap_{matnr = marc}matnr AND vbap_{werks = marc}werks )

LEFT OUTER JOIN makt

ON ( vbap_{matnr = makt}matnr AND makt~spras = 'E' )

LEFT OUTER JOIN mara

ON ( vbap_{matnr = mara}matnr )

LEFT OUTER JOIN vbkd

ON ( vbap_{vbeln = vbkd}vbeln AND vbap_{posnr = vbkd}posnr )

LEFT OUTER JOIN vbpa

ON ( vbap_{vbeln = vbpa}vbeln AND vbpa~posnr = '000010' AND

vbpa~parvw = 'WE' )

WHERE vbak~vbeln IN s_vbeln AND

vbep~etenr = '0001' AND

vbap~posnr IN s_posnr AND

vbap~werks IN s_werks AND

vbak~auart IN s_auart AND

vbak~kunnr IN s_kunnr AND

vbak~vbtyp IN s_vbtyp AND

vbap~matnr IN s_matnr AND

vbep~ettyp IN s_ettyp AND

vbep~edatu IN s_edatu.

Re: Performance Impovement for Join

Former Member — Thu, 18 Oct 2007 23:48:31 GMT

Hi,

You should use JOIN only if you want to join 3-5 tables. Here you are joining many tables that too with OUTER JOIN.

Try to break up your SELECT into 2 3 SELECT statements using FOR ALL ENTRIES.

Regards,

Atish

Re: Performance Impovement for Join

Former Member — Fri, 19 Oct 2007 06:39:45 GMT

Hi,

Split the join statement into 2.

1. Modify your join . Use inner join for VBAK VBAP VBEP VBKD VBPA. Use always VBELN and POSNR as join condition. Make sure you join VBELN first and then POSNR. Here try to restrict no of records selected. If the user is allowed to run without any selection parameter make some fields mandatory on sel-screen.

2. In the second join statement link LIPS MARA MAKT. When joining VPAP and LIPS use VBFA inbetween, find VBELN and POSNR for LIPS from VBFA.

3. Use inner join when joining LIPS and MARC and left outer join for MAKT.

4. Avoid left outer join unless necessary, this will fetch empty records.

5. After combining JOIN 1 internal table and JOIN2 internal table, remember to refresh both these internal tables and you can also use FREE: itab1 itab2 after refresh. This will improve the performance when you have fetched many records

Finally dont forget to award points

Re: Performance Impovement for Join

Former Member — Fri, 19 Oct 2007 13:24:40 GMT

Before you do the SELECT:

CHECK NOT s_vbeln[] IS INITIAL,

Rob

Re: Performance Impovement for Join

Former Member — Fri, 19 Oct 2007 15:16:26 GMT

CHECK NOT s_vbeln[] IS INITIAL,

Rob, your kidding right? Which SD functional consultant would agree to this? I agree that the cause of his problem is that there doesn't seem to be any index that his select can use. As tempting as it may be, asking the user to enter the sales order number does not give him any flexibility.

Re: Performance Impovement for Join

Former Member — Fri, 19 Oct 2007 15:20:32 GMT

Hi P_V,

Can you give us a listing of all the indexes in tables VBAK, VBAP and VBEP in your system. Hopefully we could find a Z index that you can exploit. To give you a code with the for all entries option I would need to start with the table whose index we can use.

Re: Performance Impovement for Join

Former Member — Fri, 19 Oct 2007 15:25:37 GMT

Nope - not kidding. This is an ABAP technical forum. If the programmer wants to speed up the SELECT that's how to do it. The functional consultants will have to live with either that restriction or a slow program.

Having said that, there may be other solutions. The most promising might be to force the user to enter something that could be used in and index. The material number would be helpful.

Rob

Re: Performance Impovement for Join

Former Member — Fri, 19 Oct 2007 16:12:30 GMT

Let me try to explain a little better. This sort of question has been asked many many times before in this forum. Forcing the user to enter a document or range of documents may not be reasonable, but I wanted to point out that was where the problem lay.

Ultimately, responsibility for identifying the problem and fixing it rests with the original poster of the thread. I'm willing to help, but I'm not going to try to give a detailed response based on a short code snippet and not knowing the requirements.

But I think the programmer will have to ensure that some combination of entries is made in the SELECT-OPTIONS. Based on what is entered, he or she may want to construct an entirely different SELECT statement.

For example, if nothing is entered but a material number, SELECT against VAPMA instead.

Rob

Re: Performance Impovement for Join

Former Member — Sat, 20 Oct 2007 02:59:50 GMT

Hello Mark,

How to find z index?

Thanks,

Phani

Re: Performance Impovement for Join

Former Member — Sat, 20 Oct 2007 05:07:50 GMT

Hi PV,

As others have mentioned you really need to break the statement down. 3 joins is usually pushing it (depending on the tables). By the way, you can go to SE12 and look up each table. On the main screen you will see a pushbutton saying Indexes I think. Click on it and it will show you the secondary indexes for that table. There may be some or none defined. They may be SAP defined or customer defined (Z).

Keep your inner joins on vbak, vbap and vbep. The resulting table will be used as your driver for the other tables.

You can create another inner join of mara, marc and makt.

Use your driver table as For All Entries for the other tables.

If you keep your driver table to be as shown, create another table with the first 5 fields.( vbeln, posnr, kwmeng, matnr, werks). The reason for this is so you can load the table with:

It_newtab[] = It_orders_t[].

All you want from the new table is matnr werks.

Sort it_newtab by matnr werks.

Delete adjacent duplicates from i_newtab comparing matnr werks.

Now you can use it_newtab as your driver for marc-mara-makt join. The reason is that if you have 220,000 records, when you narrow down the material/plant combinations you will probably have only a few hundred materials. So your For All entries will be a table of a few hundred instead of 200 thousand.

The result will be in a new table it_materials. When you read this table, don't forget to sort it by matnr and plant first. And READ with Binary Search.

Once you have all your other smaller tables created. As with it_materials, SORT and READ with BINARY SEARCH, as you loop through your driver table and populate the missing data from the smaller tables.

I think you will end up with 5 Select statements.

Indexes are important. Once you have split out the Select statements, if the code is still running unacceptably slow, you can use ST05 to see what indexes are being used and see if new indexes need to be created to help your cause.

You'll probably have to involve the Basis team to help you with your new indexes and they can help with the analysis of your ST05 results as well. Indexes must be created correctly in the proper order, or they may not be effective.

Hope this helps.

Filler