Select statement with inner join is taking forever

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).