topic Re: Improving Select Query Performance in Application Development and Automation Discussions

Improving Select Query Performance

ashwin_bhat — Tue, 16 Apr 2013 07:46:29 GMT

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

Re: Improving Select Query Performance

former_member197425 — Tue, 16 Apr 2013 11:50:57 GMT

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.

Re: Improving Select Query Performance

Former Member — Thu, 18 Apr 2013 13:04:08 GMT

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.

Re: Improving Select Query Performance

sudipDas — Thu, 18 Apr 2013 14:03:55 GMT

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.