performance issue with data retrieval with inner join of 4 tables

[naimkhans_babi]

Hi friends..

I need your suggestion.. I m having 4 tables with inner join.. I m afraid that it may caused performance related issues. can u suggest me how i deal with it and what could be the possible steps to be taken to avoid performace related issues.. these tables have more than 6 million records.

please help me to make my application faster...any suggestion or artical will be great help of mine...

thanking you

regards,

Naim

[Former Member]

Avoid inner join and go for 'for all entries' in select statement

[Former Member]

Hii

use <b>for all entries</b>

Data : Begin of I_vbak occurs 0,

Vbeln like vbak-vbeln,

End of I_vbak.

Data : Begin I_vbap occurs 0,

Vbeln like vbap-vbeln,

Posnr like vbap-posnr,

Matnr like vbap-matnr,

Kwmeng like vbap-kwmeng,

End of I_vbap.

Data : Begin of I_makt occurs 0,

Matnr like makt-matnr,

Maktx like makt-maktx,

End of I_makt.

Data : d_lines like sy-subrc.

Start-of-selection.

Refresh : I_vbak , I_vbap , I_makt.

Select vbeln from VBAK into table I_vbak

Where erdat = p_erdat And

vkorg = p_vkorg And

vtweg = p_vtweg And

spart = p_spart.

Clear d_lines.

Describe table I_vbak lines d_lines.

Check d_lines <> 0.

Select vbeln posnr matnr kwmeng from VBAP into table I_vbap

For all entries in table I_vbak

Where vbeln = I_vbak-vbeln.

Clear d_lines.

Describe table I_vbap lines d_lines.

Check d_lines <> 0.

End-of-selection.

Sort I_vbap by vbeln matnr.

Select matnr maktx from makt into I_makt

For all entries in I_vbap

Where spras = sy-langu And

matnr = I_vbap-matnr.

Sort I_makt by matnr.

Loop at I_vbap.

Clear I_makt.

Read I_makt with key matnr = I_vbap-matnr binary search.

Write 😕 I_vbnap-vbeln,

I_vbap-psonr,

I_vbap-matnr,

I_Makt-matnr,

I_vbap-kwmeng.

Endloop.

FOR ALL ENTRIES retrieves all the table entries depending on another table.

Inner Join retrieves table entries which match the specified condition between two tables.

Do not use JOIN if the number of tables is greater than 3.

chk this link

<b></b>

<b>http://www.sap-img.com/abap/performance-tuning-for-data-selection-statement.htm</b>

Regards

Naresh

[rahulkavuri]

Use for ALL ENTRIES if u want to cope with performance

<b>SAMPLE 1</b>

SELECT MATNR
         MTART
         MATKL
         MEINS
         NTGEW
         FROM MARA INTO TABLE IT_MARA
         WHERE MATNR IN S_MATNR AND MTART IN S_MTART .

**  ITAB_FINAL[] = IT_MARA[].

  SELECT MATNR
         MAKTX
         FROM MAKT INTO TABLE IT_MAKT
         FOR ALL ENTRIES IN IT_MARA
         WHERE MATNR = IT_MARA-MATNR.

  SELECT MATNR
         WERKS
         LADGR
         MTVFP
         DISPR
         DISMM
         DISPO FROM MARC INTO TABLE IT_MARC
         FOR ALL ENTRIES IN IT_MARA
         WHERE MATNR = IT_MARA-MATNR.

SAMPLE 2

<b> SELECT VBELN

AUDAT

AUART

NETWR FROM VBAK

INTO TABLE IT_VBAK

WHERE VBELN IN S_VBELN AND AUART IN S_AUART.

SORT IT_VBAK BY VBELN.

SELECT VBELN

POSNR

MATNR

PSTYV

CHARG FROM VBAP

INTO TABLE IT_VBAP

FOR ALL ENTRIES IN IT_VBAK

WHERE VBELN = IT_VBAK-VBELN.</b>

[Former Member]

hi naim,

check this thread

hope this helps,

priya.

[Former Member]

hi

chk this out

General Performance hints for Open SQL Programming

Keep the hit list small

Wherever possible, you should include all selection conditions in the WHERE clause, using AND and checking for equality. Do not select a large dataset and then check it with CHECK. If you want to read the whole table, you do not have to specify a WHERE condition at all.

Transfer small amounts of data

If you only want to transfer a few fields, use SELECT with a structure, not SELECT *. Alternatively, you can use one of the views in the ABAP Dictionary to select data. If you do use a view, the SAP buffering is switched off.

You should use the aggregate functions rather than selecting data and grouping it yourself. SAP buffering is switched off when you use aggregate functions.

When you UPDATE a database record, you should only update those columns that have been changed.

Use a small number of database accesses

When you INSERT, UPDATE or DELETE, you should use sets of data instead of individual table entries. This ensures that the index only has to be maintained once, which relieves the load on the database.

You should only use nested SELECT loops when the hit list in the outermost level is very small. There are various ways of avoiding nested SELECT loops:

Building a JOIN in the FROM clause

Joins as views defined in the ABAP Dictionary.

SELECT ... FOR ALL ENTRIES

In the outermost loop, the database table (PACKAGE SIZE) is read section-by-section into an internal table, sorted by its primary key (SORT on the internal table, or read in using ORDER BY PRIMARY KEY). For each data record in the internal table, all associated, dependent records are read into a further internal table (using SELECT ... FOR ALL ENTRIES). This is also sorted. You can then carry on processing using a nested LOOP.

The advantage of SELECT ... FOR ALL ENTRIES is that it provides good performance regardless of the selectivity of the condition on the outermost table, since, in contrast to the nested SELECT, it works in a data-oriented way in the database, but still only picks out the relevant database entries (different to parallel cursor processing).

You should use the addition FOR ALL ENTRIES if a JOIN is not possible for syntactical reasons or if the JOIN would result in high redundancy due to the constantly repeated fields from the left table.

Explicit cursor handling (OPEN CURSOR [WITH HOLD]...)

In this processing type, a separate cursor is opened for each table involved. These are processed in parallel. In order for the system to recognize control breaks, the tables must be sorted ( ORDER BY PRIMARY KEY) before being read. You should only use parallel cursor processing when you want to process the outermost table completely or to a large extent, since WHERE conditions for the outermost table cannot be passed on to other tables (in other words, you might read more data than is necessary).

Caution: RANGES tables

You should use explicit cursor handling for large quantities of data and logical databases.

Search through small amounts of data

In WHERE conditions, you should use EQ comparisons linked with AND as often as possible. This means that the system can use indexes in the search.

NOT, OR and IN are not supported by indexes unless all of the fields in the SELECT clause and WHERE condition are also contained in the index.

Reduce the database load where possible

SAP table buffering

The SAP buffering is switched off:

When you use SELECT FOR UPDATE or SELECT DISTINCT in the SELECT clause,

When you use BYPASSING BUFFER in the FROM clause,

When you use JOINs and subqueries

When you use ORDER BY f1 ... fn in the ORDER-BY clause.

When you use aggregate functions in the SELECT clause.

When you use IS [NOT] NULL in the WHERE condition.

You cannot process a query in the SAP buffer if the generic key section is not specified in the WHERE condition

Avoid re-reading the same data.

Before you change a table using DELETE, INSERT or UPDATE, you should check whether you need to read entries using SELECT.

If you wannt to sort data, it is more efficient to read them into the internal table and sort them using SORT than to use the ORDER-BY clause, where the sort is not supported by an index.

You should check whether you can delete duplicates using the DELETE ADJACENT DUPLICATES FROM itab. instead of using SELECT DISTINCT.

You should use logical databases if possible.

Further Help

Remarks on Performance

[Former Member]

Hi,

the solution for this performance problem is use FOR ALL ENTRIES.

Imp note: chk if ITAB is not initial before using for all entries.it will further improve performance.

rgds,

latheesh

[Former Member]

You might want to check my answer to In my testing, I found not much difference between 'for all entries' and a join. If anything, the join ran faster.

Rob

Message was edited by: Rob Burbank

[naimkhans_babi]

Thank you,,

Rob...

even i am feeling the same..I m sorry i could nt allot you more points as i have already alloted 10 points.

regards,

Naim

[former_member480923]

This is just a inch better...

Try creating a database view with ur join condition, or use 4 all entries ........ the first one is better

Hope its helps

Anirban