performance issue with data retrieval with inner join of 4 tables
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...
Ganesh Mynampati replied
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.
Remarks on Performance