topic Re: Efficient way to retrieve data with multiple selection criteria in Application Development and Automation Discussions

Efficient way to retrieve data with multiple selection criteria

former_member264257 — Fri, 03 Dec 2010 06:48:52 GMT

Hi all,

I have requirement where a sales org data need to be fetched from a custom table based on 3 fields: country / Region / Mastercode:


    SELECT *
         FROM zitab        
         INTO TABLE lt_tab.

    SORT lt_deforg BY country_key region zz_sap_master.
    READ TABLE lt_itab INTO ls_tab
                         WITH KEY country_key   = lv_country
                                  region        = lv_region
                                  zz_sap_master = iv_mcode
                                  BINARY SEARCH.
    IF sy-subrc NE 0.
      READ TABLE lt_tab INTO ls_tab
                         WITH KEY country_key   = lv_country
                                  zz_sap_master = iv_mcode
                                  BINARY SEARCH.
      IF sy-subrc NE 0.
        READ TABLE lt_tab INTO ls_tab
                      WITH KEY country_key   = lv_country
                                region        = lv_region
                                BINARY SEARCH.
        IF sy-subrc NE 0.
          READ TABLE lt_tab INTO ls_tab
                    WITH KEY region        = lv_region
                             zz_sap_master = iv_mcode
                             BINARY SEARCH.
          IF sy-subrc NE 0.
            READ TABLE lt_tab INTO ls_tab
                         WITH KEY country_key   = lv_country
                         BINARY SEARCH.
            IF sy-subrc NE 0.
              READ TABLE lt_tab INTO ls_tab
                     WITH KEY zz_sap_master = iv_mcode
                     BINARY SEARCH.
              IF sy-subrc NE 0.
                READ TABLE lt_tab INTO ls_tab
                         WITH KEY region  = lv_region
                         BINARY SEARCH.
              ENDIF.
            ENDIF.
          ENDIF.
        ENDIF.
      ENDIF.
    ENDIF.
  ENDIF.

Is there a way to eliminate the need for this multiple read statements?. If my selection criteria increase from 3 fields to 5 fields, the number of read statements for various combinations I need to check would be very high. Please share your ideas.

Thanks and Regards

Deepika

Re: Efficient way to retrieve data with multiple selection criteria

Former Member — Fri, 03 Dec 2010 09:18:58 GMT

You code is WRONG! The BINARY SEARCH can only be used in the sort-order, which is not always fulfilled!

It will run but it will not find the correct results.

It has also very very poor performance, A SORT for one READ does not make sense!

Overall the whole approach is wrong, you must rewirte the SELECTs and read only what is actually need. Reading whole database tables is not a valid approach.

You must take care that at least the main SELECT are supported by indexes.

And you should be aware that a READ finds only one records, if several fulfill the condition then a LOOP is necessary.

Do not use BINARY SEARCH at all ..... use sorted tables if possible!

Siegfried

Re: Efficient way to retrieve data with multiple selection criteria

Former Member — Fri, 03 Dec 2010 11:02:14 GMT

Hi,

SORT lt_deforg BY country_key region zz_sap_master.

SELECT *

FROM zitab

INTO TABLE lt_tab.

IF NOT IT_TAB IS INITIAL.

sort lt_itab by lv_country.

READ TABLE lt_itab INTO ls_tab

WITH KEY country_key = lv_country

region = lv_region

zz_sap_master = iv_mcode

***************SEND TO FINAL INTERNAL TABLE

ENDIF.

Not required every tiem to read the statem other wise wecah write the where conditon and selec only few fields.

SELECT *

FROM zitab

INTO TABLE lt_tab

where country_key = lv_country

region = lv_region

zz_sap_master = iv_mcode.

then we can get only few records based on the conditon only we r getting the records.

IF NOT IT_TAB IS INITIAL.

sort lt_itab by lv_country.

READ TABLE lt_itab INTO ls_tab

WITH KEY country_key = lv_country

region = lv_region

zz_sap_master = iv_mcode

***************SEND TO FINAL INTERNAL TABLE

ENDIF.

regards,

muralii