topic Re: select disctinct query with effective performance in Application Development and Automation Discussions

select disctinct query with effective performance

former_member391265 — Thu, 10 Oct 2013 12:17:45 GMT

Hi all,

There is 1 query written which is executing fast in 1 system but same query when applying on other server...taking lot of time.

Though it is simple but i would like to understand the logic behind this,,.and is their any effective way to write the same query......

i guess for all entries we can use..and delete adjacent duplicates but my doubt is about this query?

yes in both server programs..i am not giving any data on selection screen , which has select option as ekpo-MATNR, ekpo-WERKS, and mdez-AUSSL

i think may be more records in the table. but what is the other best way to write the same

SELECT DISTINCT matnr werks INTO TABLE itab

      FROM ekpo AS p INNER JOIN ekko AS k
               ON p~ebeln = k~ebeln
      WHERE p~matnr GT ' '
        AND p~matnr IN s_matnr
        AND p~werks IN s_werks
        AND p~bstyp = c_bstyp
        AND p~loekz = ' '
        AND p~elikz = ' '
        AND k~memory = ' '.

Please suggest, points will given accordingly.

thanks

Moderator message - Offering points is against the forum rules of engagement.

Message was edited by: Suhas Saha

Re: select disctinct query with effective performance

Former Member — Thu, 10 Oct 2013 12:36:31 GMT

You should check which index are used while selecting, probably the index for material should be used for EKPO, but no (standard) index is based on field memory for EKKO.

So your query (I suppose all systems are technically similar) is faster in the system with few records, if you can't create an index on field memory, probably it's better filtered it after selecting.

SELECT DISTINCT p~matnr p~werks k~memry INTO TABLE itab

      FROM ekpo AS p INNER JOIN ekko AS k
               ON p~ebeln = k~ebeln
      WHERE p~matnr GT ' '
        AND p~matnr IN s_matnr
        AND p~werks IN s_werks
        AND p~bstyp = c_bstyp
        AND p~loekz = ' '
        AND p~elikz = ' '.

delete itab where memory <> space.

sort itab.

delete

delete adjaecent duplicate from itab comparing all fields.

Max

Re: select disctinct query with effective performance

ThomasZloch — Thu, 10 Oct 2013 12:39:28 GMT

You guessed it already, most likely you'll have (many) more entries in EKKO/EKPO in the system with the long response time.

As a very simple rule for self-help, study the available indexes (primary and secondary) of the involved tables, and try to come up with WHERE-conditions that can make effective use of at least one of these indexes. Effective as in having EQ-conditions, or IN-conditions with small ranges, for as many top-down fields of an index as possible.

Thomas