05-19-2006 2:49 PM
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
05-19-2006 2:55 PM
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
05-19-2006 2:51 PM
Avoid inner join and go for 'for all entries' in select statement
05-19-2006 2:53 PM
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>http://www.sap-img.com/abap/performance-tuning-for-data-selection-statement.htm</b>
Regards
Naresh
05-19-2006 2:53 PM
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>
05-19-2006 2:54 PM
05-19-2006 2:55 PM
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
05-19-2006 3:07 PM
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
05-22-2006 4:27 AM
05-22-2006 8:59 AM
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
05-22-2006 9:03 AM
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