topic ABAP & SQL performance in Application Development and Automation Discussions

ABAP & SQL performance

Former Member — Thu, 10 Jul 2008 08:07:59 GMT

Hi all.

I have the following sql wich performance isn't good enough because one of the tables used in the join as a lot of data.

So i Want to know how i can improve it ?


SELECT DISTINCT ordered_prod
  INTO TABLE ht_data-ordered_prod
  FROM crmd_orderadm_i as i
  JOIN crmd_link as l ON i~header = l~guid_hi
  JOIN crmd_partner as p on l~guid_set = p~guid
  WHERE l~objtype_set = '07'
  AND p~partner_no = wa_but000-partner_guid.

Will it be more efficient if i make separe select from the 3 table and then process it with loops for example ?

Thanks for your suggestions.

Re: ABAP & SQL performance

Former Member — Thu, 10 Jul 2008 08:10:07 GMT

use select for all entries it will improve ur perfromance

for an beeter result try to get the relation in tables and flow of data lets take and Example of three table

i_tab1 with key A

i_tab2 with key A B

i_tab3 with key A B C

now frist

apply only select query

to it

like select * from SDR into table ZXS

where ......

now apply for all entries in this manner

select * from RET in SDER

for all entries in ZXS

where

...= ZXS-A

similarly for last table and this time use for all entry for table SDER.

Cheerts

Snehi

Cheers

Snehi

Edited by: snehi chouhan on Jul 10, 2008 10:10 AM

Re: ABAP & SQL performance

Former Member — Thu, 10 Jul 2008 08:11:03 GMT

You can use secondary indexes for the table which has a lot of data.

Re: ABAP & SQL performance

Former Member — Thu, 10 Jul 2008 08:12:26 GMT

Hi Friend,

Use FOR ALL ENTRIES in the select statement.

Regards

Hemant Khemani

Re: ABAP & SQL performance

venkatasriram_mygapula — Thu, 10 Jul 2008 08:21:08 GMT

try this code.

select ordered_prod into table ht_data_ordered_prod

from crmd_orderamd_i as i inner join crmd_link as l

ON i_{header = l}guid_hi where l~objtype-set = '07' and your selection screen condition.

if not ht_data[] is initial.

select distinct ordered_prod into table <int table>

from crmd_partner for all entries in ht_data

where partner_no = wa_but000-partner_guid.

endif.

regards.

sriram.

Re: ABAP & SQL performance

Former Member — Thu, 10 Jul 2008 08:25:55 GMT

HI,

~go ahead with the select,

~ remove that distinct from the select.

~after the data fetch sort the internal table and remove the duplicates.

SELECT ordered_prod

INTO TABLE ht_data-ordered_prod

FROM crmd_orderadm_i as i

JOIN crmd_link as l ON i_{header = l}guid_hi

JOIN crmd_partner as p on l_{guid_set = p}guid

WHERE l~objtype_set = '07'

AND p~partner_no = wa_but000-partner_guid.

~are u using select-endselect, if no then why ht_data-ordered_prod????