topic Re: SELECT Performance in Application Development and Automation Discussions

SELECT Performance

Former Member — Tue, 12 Jan 2010 12:39:16 GMT

Hi experts,

I only one to select the first line:

select *

from /BI0/PHC_PATCASE UP TO 1 ROWS

into corresponding fields of it_case

where HC_INSTITU = lv_patcase-hc_institu

AND HC_PATCASE < lv_patcase-hc_patcase

AND HC_CASETP = lv_patcase-hc_casetp

AND HC_PATIENT = lv_patcase-hc_patient

AND OBJVERS = 'A'

AND hc_disdate > lv_admdate30

ORDER BY hc_disdate DESCENDING.

endselect.

But for one month, is spending a lot of time.

SOmeone knows some way to performance it? Is not posible to create an index in the table.

thanks a lot!

Re: SELECT Performance

Former Member — Tue, 12 Jan 2010 13:13:12 GMT

Hi,

This code can be enhanced in following ways:

1. Define internal table with only desired field.

2. Select only the desired field from table /BI0/PHC_PATCASE.

3. Select the fields in a sequence and hence remove 'into corresponding fields of it_case' to 'into it_case'.

4. If only one record have to be selected, then remove ORDER BY syntax.

4. If you are selecting one row from table, then select the data into work area instead of internal table.

5. Create indexes on table.

I hope this will help.

Vinod.

<REMOVED BY MODERATOR - REQUEST OR OFFER POINTS ARE FORBIDDEN>

Edited by: Alvaro Tejada Galindo on Jan 12, 2010 10:53 AM

Re: SELECT Performance

Former Member — Tue, 12 Jan 2010 13:18:51 GMT

Hi Markus

Before we actually create a secondary index on a table we should consider the overhead involved in maintaining the index. Hence i would suggest you to consider the following options,

1) Retrieve the information from the database table so that it uses the existing index, basically i mean to say re-phrase your where clause. That means remove the date criteria in the select, but once you have the data then filter it on the date criteria.

2) Remove into Corresponding, Order By clauses.

3) Instead of Up to 1 rows, use into table itab and then filter the records in this internal table applying the date logic.

Regards

Ranganath

Re: SELECT Performance

Former Member — Tue, 12 Jan 2010 13:46:12 GMT

use as many key fields as possible in WHERE clauses of SELECT statements. Sometimes you are not sure about the value of some key fields. They seem to be empty (Initial value)

Using the option GE (greater equal) in your coding can improve your performance considerably without the risk of table lines not being selected.

code.

TABLES: S001.

CONSTANTS: SSOUR_INI LIKE S001-SSOUR VALUE IS INITIAL,

VRSIO_INI LIKE S001-VRSIO VALUE IS INITIAL,

SPMON_INI LIKE S001-SPMON VALUE IS INITIAL,

SPTAG_INI LIKE S001-SPTAG VALUE IS INITIAL,

SPWOC_INI LIKE S001-SPWOC VALUE IS INITIAL,

SPBUP_INI LIKE S001-SPBUP VALUE IS INITIAL.

SELECT-OPTIONS:

SO_SPTAG FOR S001-SPTAG OBLIGATORY,

SO_KUNNR FOR S001-KUNNR OBLIGATORY,

SO_VKORG FOR S001-VKORG OBLIGATORY,

SO_VTWEG FOR S001-VTWEG OBLIGATORY,

SO_SPART FOR S001-SPART OBLIGATORY,

SO_MATNR FOR S001-MATNR OBLIGATORY.

SELECT * FROM S001

INTO TABLE TA_S001

WHERE SSOUR GE SSOUR_INI "Greater Equal initial value

AND VRSIO GE VRSIO_INI " idem

AND SPMON GE SPMON_INI " idem

AND SPTAG IN SO_SPTAG

AND SPWOC GE SPWOC_INI " idem

AND SPBUP GE SPBUP_INI " idem

AND KUNNR IN SO_KUNNR

AND VKORG IN SO_VKORG

AND VTWEG IN SO_VTWEG

AND SPART IN SO_SPART

AND MATNR IN SO_MATNR.

Re: SELECT Performance

Former Member — Tue, 12 Jan 2010 15:56:07 GMT

Simply put...don't use SELECT-ENDSELECT, don't use SELECT *, don't use INTO CORRESPONDING FIELDS...and look for the table keys...

Greetings,

Blag.

Re: SELECT Performance

Former Member — Tue, 12 Jan 2010 16:14:13 GMT

Hi,

Before you start with the performance improvement program, first measure the time of the select query. You can use the SQL trace (ST05) to start with the measure.

For the ABAP Performance Guidelines you can use the following things:

1. Don't use the SELECT *. Mention as many fields in the select options.

2. Create an internal table with the only needed fields.

3. See whether the Table is buffered or not.

4. Use any existing index with the most of the Primary key fields.

Then after all the changes you have to measure the performance to really see the percentage improvement.

Hope this helps.

Thanks,

Samantak.