topic Efficiency problem in Application Development and Automation Discussions

Efficiency problem

Former Member — Tue, 25 Jul 2006 13:39:48 GMT

I need to make the best efficient selection, i have to catch the data in 2 tables, the registers have to be in the first table, but not in the second, can i make a join?,

example:

select * from ekpo where LOEKZ <> 'X'.

select single * from lips where VGBEL = ekpo-vbeln.

if sy-subrc <> 0.

append gt_result.

endif.

endselect.

Thanks in advance,

regards

Re: Efficiency problem

RichHeilman — Tue, 25 Jul 2006 13:51:33 GMT

Maybe you can do a subquery in an fetch. VBELN does not exist in EKPO in my system. Even with this there will be some issues, you are not selecting from EKPO with a valid key. It will do an entire table scan.



select ebeln into table gt_result
           from ekpo as e
                 where loekz = space
                    and not exists ( Select * from lips
                                        <b>where vgbel = e~ebeln</b> ).

Regards,

Rich Heilman

Re: Efficiency problem

naimesh_patel — Tue, 25 Jul 2006 13:52:41 GMT

hello,

First go to EKKO.. use any index based on the Date. and then go to EKPO to search for the LOEKZ = 'X' for the PO number.

Regards,

Naimesh

Re: Efficiency problem

christian_wohlfahrt — Tue, 25 Jul 2006 13:52:46 GMT

Hi Carl!

Of course you should make your EKPO select more efficient (have a look into SE11 - Indizes), but for LIPS is an easy way possible:

  SELECT * INTO TABLE gt_result
          FROM ekpo AS e
         WHERE ebeln IN s_ebeln
          AND  loekz EQ space
          AND NOT EXISTS ( SELECT * FROM lips
                                WHERE vgbel = e~ebeln ).

LIPS has an index on VGBEL, so this should be fine. Maybe you make a range for ebeln, than you can start several jobs in parallel... or re-use already select lists.

Regards,

Christian