Select statement with inner join is taking forever SAP ABAP INTERVIEW Questions

Following is the select stmt which is taking forever.

SELECT MKPF~BUDAT MKPF~CPUTM
MSEG~MATNR MSEG~WERKS MSEG~EBELN
MSEG~EBELP MSEG~ERFMG
INTO CORRESPONDING FIELDS OF TABLE
W_DTL_INVOICE
FROM MKPF INNER JOIN MSEG
ON MKPF~MBLNR = MSEG~MBLNR
AND MKPF~MJAHR = MSEG~MJAHR
WHERE MKPF~BUDAT > '20040721' AND
MSEG~BWART = '101' AND
MSEG~BUKRS = '1733'.

It does not have much records.
Can somebody let me know how to fine tune this statement.

I have gone through the same problem last year. This is the solution I made. My program spended more than an hour to select 5000 records from mkpf inner mseg(From a range of 100000 records) . Now it needs less than 15 seconds to perform the same task.

Try this changes

** in global data add these two hashed tables

data: begin of wa_mkpfmseg,
   mblnr like mkpf-mblnr,
   mjahr like mkpf-mjahr,
   zeile like mseg-zeile,
   bukrs like mseg-bukrs
     bwart like mseg-bwart,
     budat like mkpf-budat,
   cputm like mkpf-cputm,
     matnr like mseg-matnr,
  werks like mseg-werks,
     ebeln like mseg-ebeln,
     ebelp like mseg-ebelp,
     erfmg like mseg-erfmg,
     end of wa_mkpfmseg.

data ht_mkpfmseg like hashed table of wa_mkpfmseg
      with unique key mblnr mjahr zeile
    with header line.

data: begin of wa_mkpfmsegSel,
     budat like mkpf-budat,
   cputm like mkpf-cputm,
     matnr like mseg-matnr,
   werks like mseg-werks,
     ebeln like mseg-ebeln,
     ebelp like mseg-ebelp,
     erfmg like mseg-erfmg,
     end of wa_mkpfmseg.


data ht_mkpfmsegSel like hashed table of wa_mkpfmsegSel
      with unique key budat cputm matnr werks ebeln ebelp
    with header line.

** change your select sentence to look like this:
select mkpf~mblnr
           mkpf~mjahr
           mseg~zeile
         mseg~bukrs
        mseg~bwart
     MKPF~BUDAT MKPF~CPUTM
          MSEG~MATNR MSEG~WERKS MSEG~EBELN
          MSEG~EBELP  MSEG~ERFMG
               INTO  TABLE ht_mkpfmseg
               FROM MKPF INNER JOIN MSEG
                    ON   mkpf~mandt = mseg~mandt
               AND    MKPF~MBLNR = MSEG~MBLNR
               AND MKPF~MJAHR = MSEG~MJAHR
              where mkpf~budat > '20040721'.

loop at ht_mkpfmseg.
             check ht_mkpfmseg-bukrs = '1733' and ht_mkpfmseg-bwart = '101'      
             read table ht_mkpfmsegsel with table key
                  budat = ht_mkpfmseg-budat
                  cputm = ht_mkpfmseg-cputm                                                      

                  matnr  = ht_mkpfmseg-matnr      
                  werks  = ht_mkpfmseg-werks                                               

                  ebeln = ht_mkpfmseg-ebeln
                  ebelp  = ht_mkpfmseg-ebelp
             transporting erfmg.
             if sy-subrc <> 0.
                move-corresponding ht_mkpfmseg to ht_mkpfmsegsel.
                insert table ht_mkpfmsegsel.
             else.
                     ht_mkpfmsegSel-budat = ht_mkpfmseg-budat.
                     ht_mkpfmsegSel-cputm = ht_mkpfmseg-cputm.
                     ht_mkpfmsegSel-matnr = ht_mkpfmseg-matnr,
                     ht_mkpfmsegSel-werks = ht_mkpfmseg-werks.
                     ht_mkpfmsegSel-ebeln = ht_mkpfmseg-ebeln.
                     ht_mkpfmsegSel-ebelp = ht_mkpfmseg-ebelp.
                     add ht_mkpfmseg-erfmg to ht_mkpfmsegSel-erfmg.
                     modify table ht_mkpfmsegSel transporting erfmg.
             endif.
endloop.
" at this point ht_mkpfmsegSel has the data collected that you want.
loop at ht_mkpfmsegSel.
     .... Here put the code between your select ... endselect.
endloop.

This should run faster because of the next reasons:

1. you're not distracting the sql optimizer when it analizes the sql where clause. It makes use of the first index declared on mkpf (budat-mblnr)-

2. the data is selected into a hashed table which are the faster access tables provided by SAP from 4.0 up to 4.6c (I don't know about 4.7)

3. As the select isn't restricting bukrs and bwart (but there is a good reason on this: it's faster to read 100000 records into a hashed table and then filter the 80000 unwanted than to select the 20000 records via non index fields in the where clause. I tested it in my own programs).