topic Re: Inner Join. in Application Development and Automation Discussions

Inner Join.

Former Member — Mon, 20 May 2013 18:21:04 GMT

Hi ,

I did a inner join on table MSEG and MKPF but it is taking longer time to execute the query eventhough I have mentioned all the primary keys .Why is that both the tables are also transparent tables right ?

I did a SQL trace to check it takes a longer time to execute the query .

Please suggest .

Thanks

Re: Inner Join.

matt — Mon, 20 May 2013 19:49:36 GMT

Why not share the exact SQL that you're using? Also, the results of the "explain" in the trace.

btw - someone (or possibly several someones) are likely to come along shortly and tell you to use For All Entries instead of Inner Join. It might work, but the vast majority of the time, it is better to use an inner join that For All Entries. Best the exhaust other options first.

Re: Inner Join.

Former Member — Tue, 21 May 2013 04:37:46 GMT

Hi Suganth,

1. you can try View WB2_V_MKPF_MSEG2 instead of join on table MSEG and MKPF.

2. If your where clause is without primary key/index, try create secondary index including your where clause fields.

Regards,

Mordhwaj

Re: Inner Join.

RaymondGiuseppi — Tue, 21 May 2013 06:02:18 GMT

Can you elaborate on your exact requirement, I suppose you usethe correct join fields (MJAHR, MBLNR) from foreign relation, but which criteria (select-option) do you use, and which fields of the header are required ?

Did you look for performance notes like Note 1516684 - MKPF fields added to MSEG - Performance optimization, did you use ST05 to analyze the execution plan, which index were used ?

Regards,

Raymond

Re: Inner Join.

Former Member — Wed, 05 Jun 2013 07:06:42 GMT

Hi,

Below is the SQL statement i use ..

    SELECT ms~mblnr ms~zeile ms~matnr mk~budat ms~menge ms~shkzg ms~meins
         ms~bwart ms~werks ms~lgort ms~wempf ms~kunnr ms~dmbtr ms~bualt
         mk~xblnr
    INTO CORRESPONDING FIELDS OF TABLE lit_salestab
    FROM mseg AS ms
         INNER JOIN mkpf AS mk
            ON ms~mblnr = mk~mblnr
           AND ms~mjahr = mk~mjahr
     FOR ALL ENTRIES IN it_mat
   WHERE matnr = it_mat-matnr
     AND ms~bwart IN rg_bwart
     AND ms~werks IN rg_werks
     AND ms~lgort IN rg_lgort
     AND mk~mjahr IN rg_mjahr
     AND ms~kunnr IN so_kunr3
     AND mk~budat IN rg_budat.

Thanks

Suganth Kumar.

Re: Inner Join.

matt — Wed, 05 Jun 2013 08:55:03 GMT

MATNR is probably not part of any of your indexes. What indexes do you have on MSEG that include MATNR? If you haven't got any, then you need to create one. MANDT MATNR could work.

You need to do an SQL trace in ST05 on your program, to find out precisely the sql issued on the db server. Select the statement in the trace, and click on explain, and post the results here.

This is what you should do.

1. Tell me what indexes you have on MSEG - i.e. their name and what fields they contain

2. Do an SQL trace in ST05 on your program, and post the Explain SQL results for the select here