topic Re: help in select plx in Application Development and Automation Discussions

help in select plx

Former Member — Fri, 16 Nov 2007 10:50:14 GMT

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

Re: help in select plx

Former Member — Fri, 16 Nov 2007 10:53:30 GMT

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

Re: help in select plx

Former Member — Fri, 16 Nov 2007 10:54:01 GMT

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

Re: help in select plx

Former Member — Fri, 16 Nov 2007 10:54:13 GMT

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.

Re: help in select plx

Former Member — Fri, 16 Nov 2007 11:57:28 GMT

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

Re: help in select plx

Former Member — Fri, 16 Nov 2007 12:04:12 GMT

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

Re: help in select plx

Former Member — Fri, 16 Nov 2007 12:06:35 GMT

ok,

I'll do

thx