Improving Select Query Performance

[ashwin_bhat]

Good Day!

I have a custom table with following columns

MANDT  - Primary Key

Column2 - Primary Key

Column3 - Primary Key

Column4 - Primary Key

Column5 - Primary Key

Column6

Column7

Column8

Column9

Column10

I have a select query which takes long time to process.

If not  itab_select is initial

Select Column2

           Column3

           Column4

           Column5

           Column6

           Column7

           Column8

           Column9

           Column10

  into itab

for all entries in itab_select

where column2 = itab_select-col2

   and  column3 = itab_select-col3.

endif.

This table has 12 million records.

250000 records keep getting insterted into this table via a Modify DTAB statement each day.

1. To overcome the performance issue in the select query, Is it a good idea to create a secondary index on column2, column3 only.

Note I am not including MANDT in the secondary index.

2. Will there be performance issues in record insertion as the table is updated frequently (every 30 mins) and upto 250000 records are instered per day.

3. Is there any other recommendation to improve query performance.

Thanks and Regards,

Ashwin Bhat

[former_member197425]

Hi,

If Select query is the issue of performance then i suggest to follow below strategy;

1. Use PACKAGE SIZE in select query i.e fetching of data in set of packages .

2. Use of OPEN & FETCH CURSOR techniques for data selection.

3. Even if above method doesn't lead to improvement then create Secondary index for the table..

Thanks,

Nandi.

[Former Member]

Hi Ashwin,

By any chance, can you add those three missed primary key fields in the where clause?

Column3 - Primary Key

Column4 - Primary Key

Column5 - Primary Key

Thanks,

Venkatesh.

[sudipDas]

1. Create a secondary index with Col2 and Col3.

2. Sort itab_select by col2 col3.

    Delete adjacent duplicate rows from itab_select compairing col2 col3. -- Before select .

If this doesn't help then try the package size in select.

This might help u..

Reards

Sudip.