topic Re: Select Query optimisation in Application Development and Automation Discussions

Select Query optimisation

Former Member — Sat, 02 Dec 2006 10:45:00 GMT

Hi.

Is there any way to optimise the below code. or can i write a join query for this if yes then how.

SELECT land1

zone1

FROM tzone

INTO CORRESPONDING FIELDS OF TABLE t_land

FOR ALL ENTRIES IN int_delivery

WHERE zone1 = int_delivery-zone1.

IF sy-subrc = 0.

SELECT land1

landx

FROM t005t

INTO CORRESPONDING FIELDS OF TABLE t_landx

FOR ALL ENTRIES IN t_land

WHERE land1 = t_land-land1.

ENDIF.

Thanks

Any help will not go unappreciated..

Re: Select Query optimisation

Clemenss — Sat, 02 Dec 2006 13:24:10 GMT

Hi,


  SELECT DISTINCT t005t~land1 landx
  FROM t005t
  JOIN tzone ON tzone~land1 = t005t~land1
  INTO CORRESPONDING FIELDS OF TABLE t_landx
  FOR ALL ENTRIES IN int_delivery
  WHERE zone1 = int_delivery-zone1
    AND spras = sy-langu.
  SORT t_landx.
  DELETE ADJACENT DUPLICATES FROM t_landx.

Note: t005t is language-dependent tect table for T005, If you don't want the country names in all languages maintained in your system, add spras = sy-langu to your WHERE condition. If a country is not maintained in logon language in T005T, nothing is retrieved.

Regards,

Clemens

Re: Select Query optimisation

anversha_s — Sat, 02 Dec 2006 15:27:12 GMT

hi,

<i>how to optimize ?</i>

1. Avoid

INTO CORRESPONDING

, create an internal table with required fields only.

select tzone~land1 tzone~zone1 t005~landx
from tzone 
join t005t
on tzone~land1 = t005t~land1
into table t_landx
for all entries in int_delivery
where
tzone~zone1 = int_delivery-zone1
AND spras = sy-langu.

Regards

Anver

Re: Select Query optimisation

Former Member — Sat, 02 Dec 2006 19:32:09 GMT

T005T is a small table. If TZONE is also small in your system, you might try reading all entries (for your language in T005T) from both tables into an internal table and then doing a binary search to get entries when you need them. This also assumes that INT_DELIVERY is relatively large.

Rob

Re: Select Query optimisation

Former Member — Sun, 03 Dec 2006 05:35:09 GMT

Hi friends

As suggested by Mr Burbank, i could go for his suggestion

however just to kill my curiosity which is more better way for optimising in

the current situation.

in this case I have to write two select queries and as suggested by anver there is only one select but the join is there.

So which one is more efficient.

Anver : any suggestions.

Thanks & regards

Mohiuddin

Re: Select Query optimisation

Former Member — Sun, 03 Dec 2006 18:16:41 GMT

It depends on the number of entries in the tables - I still like mine. Why don't you try them all out and let us know?

Rob