Problem with inner join

[koolsanjay83j]

SELECT mseg~mblnr

mseg~bwart

mseg~matnr

mseg~werks

mseg~lgort

mseg~charg

mseg~zeile

mkpf~budat

INTO TABLE P_T_LASTDT

FROM mseg INNER JOIN mkpf

ON mseg_{mandt = mkpf}mandt

AND mseg_{mblnr = mkpf}mblnr

AND mseg_{mjahr = mkpf}mjahr

FOR ALL ENTRIES IN P_T_MCHB

WHERE mseg~matnr = p_t_mchb-matnr

AND mseg~werks = p_t_mchb-werks

AND mseg~lgort = p_t_mchb-lgort

AND mseg~charg = p_t_mchb-charg.

Here P_T_MCHB has got 1900 records and all the records has same MATNR, WERKS and LGORT. Basically this query runs faster in system A where MSEG has 177,000 records in it for this MATNR. It takes just 590 Sec.

However, in another system B same query is taking a lot of time for the same MATNR for which MSEG has 145,000 records which is lesser than above case. This query takes 50,000 sec.

I checked the indexes in both the systems and has same indexes and fields. One of them has fields from the where clause. That is how it works faster in system A.

Not sure, what could be wrong in system B so that for the same set of data, indexs and code, it takes very long.

Can anyone help me on this?

Edited by: Rob Burbank on Oct 1, 2009 2:38 PM

[ThomasZloch]

Why not follow up on your previous problem first?

How did you like all those replies?

Thomas

[koolsanjay83j]

Hi Thomas,

Thanks for the follow up....previous replies were all helpfull..however did not solve the problem since we can not use the index tables and make the date range mandetory. Hence business has decided to fetch this report from BW...Still, thanks a lot for your help.

Any idea on current problem with inner join which is behaving defirrently in diffrent system.

[Former Member]

>

> Hi Thomas,

>

> Thanks for the follow up....previous replies were all helpfull..however did not solve the problem since we can not use the index tables and make the date range mandetory. Hence business has decided to fetch this report from BW...Still, thanks a lot for your help.

Yes - but you please assign p0ints to the helpful answers. (Remember, people took time out of their day to help you.) And mark the question as solved.

Rob

[koolsanjay83j]

yes Rob, you are right.

I m sorry, my bad. I have given the points to all useful options.

[former_member182114]

Hi Sanjay,

Your SQL don't represent a problem itself, but may become run slower if the statistics of database are not updated or the data used differs from both system.

Check on DB20 transaction (compare both systems):

- number of records (maybe what you see on your client differs from the whole system leading your "metrics" a wrong weight)

- check the method used on both system

- check if the statistics are update to dated

Run ST05 on each system and check the ACCESS PLAN chose for the database.

[https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/7205] [original link is broken] [original link is broken] [original link is broken];

Regards, Fernando Da Ru00F3

[Sandra_Rossi]

Frequent issues are :

- database statistics are not up to date (consequence : database will not use the right index for example); as said above, use ST05 to see the execution plan

- P_T_MCHB is empty, all records are extracted by default

- there are many lines in P_T_MCHB, the join will be executed many times

Edited by: Sandra Rossi on Oct 1, 2009 11:35 PM : I added the case "P_T_MCHB is empty..."

[koolsanjay83j]

P_T_MCHB has 1600 entries and only three coulumns from this table is used in the where clause of the query and the combination of these three fields is identical thru out the table, hence Join would be performed only once right?

Would it help improve the performance if we remove the CHARGE from the where clasue since its not the part of the index.

One more thing I would like tyo mention here is that, this query works awsome in other system where we have more data. So, I just wondering if its system issue and somehting else.

[Sandra_Rossi]

I don't think so, the join is executed several times, you must sort the internal table and delete duplicate entries.

The most frequent issue is database statistics not up to date, please check !

[former_member182114]

P_T_MCHB has 1600 entries and only three coulumns from this table is used in the where clause of the query and the combination of these three fields is identical thru out the table, hence Join would be performed only once right?

- I saw four columns for this table on where clause, also even if you don't have charg on the index do not remove it since will be a restrictive filter after the index access (less rows from database to application server)

- The characteristic of FAE is run many SQL statments for your internal table (1600 records will result 320 SQL executions), if you are using join will 320 joinned SQL's (join and FAE together doesn't represent a problem itself).

One more thing I would like tyo mention here is that, this query works awsome in other system where we have more data. So, I just wondering if its system issue and somehting else.

You will reach to a inquiry faster by checking DB20 / ST05, and bring new info the discussion.

Regards, Fernando Da Rós

Edited by: Fernando Ros on Oct 1, 2009 11:53 PM

[Former Member]

Hi Sanjay,

I do not see anything wrong with your join. Table MSEG has index M on fields (MATNR, WERKS, LGORT, BWART and SOBKZ). From looking at your where clause this is the index that should ideally get used.

Check 3 things:

1) Make sure that index M is being picked up in both systems. Use transaction ST05 to find out if that is the case.

2) Check if index M is exists in the database in both your systems. If you look at your index in transactions SE11, SE12 or SE14 it should tell you 'Index MSEG___M exists in database system ORACLE' (Our system uses Oracle.).

3) Make sure you delete adjacent duplicates before a select with for all entries.

DATA: p_t_mchb_tmp LIKE TABLE OF p_t_mchb.
 
IF NOT p_t_mchb[] IS INITIAL.

  p_t_mchb_tmp[] = p_t_mchb[].
 
  SORT p_t_mchb_tmp BY matnr werks lgort charg.
 
  DELETE ADJACENT DUPLICATES FROM p_t_mchb_tmp COMPARING matnr werks lgort charg.
 
  SELECT mseg~mblnr
         mseg~bwart
         mseg~matnr
         mseg~werks
         mseg~lgort
         mseg~charg
         mseg~zeile
         mkpf~budat
    FROM       mseg 
    INNER JOIN mkpf
    ON  mseg~mandt EQ mkpf~mandt
    AND mseg~mblnr EQ mkpf~mblnr
    AND mseg~mjahr EQ mkpf~mjahr
    INTO TABLE p_t_lastdt
    FOR ALL ENTRIES IN p_t_mchb_tmp
    WHERE mseg~matnr EQ p_t_mchb_tmp-matnr
    AND   mseg~werks EQ p_t_mchb_tmp-werks
    AND   mseg~lgort EQ p_t_mchb_tmp-lgort
    AND   mseg~charg EQ p_t_mchb_tmp-charg.

ENDIF.

[koolsanjay83j]

Issue resolved. It was system issue. Memory parameters were low for the system where i was testing the changes.

It works fine in other systems.