help in select plx

[Former Member]

hi Abapers,

I need to improve performance in this select.

• H I H S G <- I H S G

SELECT * FROM ihsg INTO TABLE hihsg

FOR ALL ENTRIES IN ht357g

WHERE ( objnr LIKE 'OR%' )

AND ( k_skip EQ imprm_k_skip-yes )

AND ( pmsog EQ ht357g-sogen )

AND ( erdat IN s_erdat )

AND ( ernam IN s_ernam )

AND ( aedat IN s_aedat )

AND ( aenam IN s_aenam )

AND ( aufnr IN s_aufnr )

ORDER BY PRIMARY KEY.

• ihsg have 113237 records;

• ht357g have 16 records;

• s_erdat have no records;

• s_ernam have no records;

• s_aedat have no records;

• s_aenam have no records;

• s_aufnr have 3000 records;

Anyone can plx try to help me?

Regards

Allan Cristian

[Former Member]

Allan,

1. Specify field names instead of using * in select query.

2. No Need to use ( ).

3. Maintain the same order in where clause as they appear in IHSG table, even in where clause also.

4. Use Seconday indexes if available.

Reward if it helps,

Satish

[Former Member]

Try to avoid * after select.

write all the field names what ever u want to retrieve from the database in the place of * then it will imprrove the performance of ur select statment

[Former Member]

first specifed all primary key with field u want.

sort itab.

if ht357g is not initial.

select field1 .2.3..4primary key into corresponding fields of table ihsg from ht357g

FOR ALL ENTRIES IN ht357g

WHERE ( objnr LIKE 'OR%' )

AND ( k_skip EQ imprm_k_skip-yes )

AND ( pmsog EQ ht357g-sogen )

AND ( erdat IN s_erdat )

AND ( ernam IN s_ernam )

AND ( aedat IN s_aedat )

AND ( aenam IN s_aenam )

AND ( aufnr IN s_aufnr )

ORDER BY PRIMARY KEY.

endif.

[Former Member]

hi,

I do it, but the table ihsg have 24 Fields, and the final time is the same...

I use a range hs_aufnr(using low and high) to help at s_aufnr(3000 records) and filtering...

DELETE hihsg

WHERE NOT ( aufnr IN s_aufnr ).

CLEAR hs_aufnr.

but I need a better time.

anyone have outher idea??

Regards

Allan Cristian

Message was edited by:

Allan Cristian

[Former Member]

instead of order by primary key , select recors first and try to use sort statement.

this will improve performance a bit.

Also try to provide the maximum possible key fields in select

The order of fields in where clause make it same as order in the table

[Former Member]

ok,

I'll do

thx