topic Re: Performance of a SQL Query in Application Development and Automation Discussions

Performance of a SQL Query

Former Member — Thu, 17 May 2007 14:12:57 GMT

HI mark,

The code given by you is working absolutely fine.

can you please help me to solve the following complicated query.

SELECT a~vbeln

a~erdat

a~ernam

a~vkorg

a~vkbur

a~kunnr

b~kunnr AS kunnr_shp

b~adrnr

c~gbstk

c~fkstk

d~vbeln AS vbeln_i

INTO CORRESPONDING FIELDS OF TABLE t_z92sales

FROM vbak AS a INNER JOIN vbuk AS c ON a_{vbeln = c}vbeln

INNER JOIN vbpa AS b ON a_{vbeln = b}vbeln

AND b_{posnr = 0 AND b}parvw = 'WE'

LEFT OUTER JOIN vbfa AS d ON a_{vbeln = d}vbelv

AND d~vbtyp_n = 'M'

FOR ALL ENTRIES IN vbeln_s

WHERE a~vbeln = vbeln_s-low

AND a~vbtyp = 'C'

AND a~vkorg IN vkorg_s

AND c~gbstk IN gbstk_s.

Thanks and Regards,

Giri

Re: Performance of a SQL Query

Former Member — Thu, 17 May 2007 16:51:49 GMT

Hi Giri,

Could you post the whole program because I am not sure why the person who wrote this code user for all entries on a select option instead of just using the select option in the query?

Regards,

Mark

Re: Performance of a SQL Query

Former Member — Thu, 17 May 2007 18:13:03 GMT

Hi Giri,

ASSUMPTION:

As you have not answered my previous post I am assuming that the programmer who wrote this query had no apparent reason for using the following

FOR ALL ENTRIES IN vbeln_s

WHERE a~vbeln = vbeln_s-low

and could have written it as

WHERE a~vbeln IN vbeln_s

Based on this assumption I have written the following code. Please let me know if it solves your problem.

Code:

TABLES: vbak,
        vbuk.

SELECT-OPTIONS: vbeln_s FOR vbak-vbeln,
                vkorg_s FOR vbak-vkorg,
                gbstk_s FOR vbuk-gbstk.

DATA: w_index TYPE sy-tabix,

      BEGIN OF t_z92sales OCCURS 0,
        vbeln     TYPE vbak-vbeln,
        erdat     TYPE vbak-erdat,
        ernam     TYPE vbak-ernam,
        vkorg     TYPE vbak-vkorg,
        vkbur     TYPE vbak-vkbur,
        kunnr     TYPE vbak-kunnr,
        kunnr_shp TYPE vbpa-kunnr,
        adrnr     TYPE vbpa-adrnr,
        gbstk     TYPE vbuk-gbstk,
        fkstk     TYPE vbuk-fkstk,
        vbeln_i   TYPE vbfa-vbeln,
      END OF t_z92sales,

      BEGIN OF t_vbak OCCURS 0,
        vbeln TYPE vbak-vbeln,
        erdat TYPE vbak-erdat,
        ernam TYPE vbak-ernam,
        vkorg TYPE vbak-vkorg,
        vkbur TYPE vbak-vkbur,
        kunnr TYPE vbak-kunnr,
      END OF t_vbak,

      BEGIN OF t_vbuk OCCURS 0,
        vbeln TYPE vbuk-vbeln,
        gbstk TYPE vbuk-gbstk,
        fkstk TYPE vbuk-fkstk,
      END OF t_vbuk,

      BEGIN OF t_vbpa OCCURS 0,
        vbeln TYPE vbpa-vbeln,
        kunnr TYPE vbpa-kunnr,
        adrnr TYPE vbpa-adrnr,
      END OF t_vbpa,

      BEGIN OF t_vbfa OCCURS 0,
        vbelv TYPE vbfa-vbelv,
        posnv TYPE vbfa-posnv,
        vbeln TYPE vbfa-vbeln,
        posnn TYPE vbfa-posnn,
      END OF t_vbfa.


REFRESH t_z92sales.

SELECT vbeln
       erdat
       ernam
       vkorg
       vkbur
       kunnr
FROM vbak
INTO TABLE t_vbak
WHERE vbeln IN vbeln_s
AND   vbtyp EQ 'C'
AND   vkorg IN vkorg_s.

IF sy-subrc EQ 0.

  SORT t_vbak BY vbeln.

  SELECT vbeln
         gbstk
         fkstk
    FROM vbuk
    INTO TABLE t_vbuk
    FOR ALL ENTRIES IN t_vbak
    WHERE vbeln EQ t_vbak-vbeln
    AND   gbstk IN gbstk_s.

  IF sy-subrc EQ 0.

    SORT t_vbuk BY vbeln.

    SELECT vbeln
           kunnr
           adrnr
      FROM vbpa
      INTO TABLE t_vbpa
      FOR ALL ENTRIES IN t_vbuk
      WHERE vbeln EQ t_vbuk-vbeln
      AND   posnr EQ '000000'
      AND   parvw EQ 'WE'.

    IF sy-subrc EQ 0.

      SORT t_vbpa BY vbeln.

      SELECT vbelv
             posnv
             vbeln
             posnn
        FROM vbfa
        INTO TABLE t_vbfa
        FOR ALL ENTRIES IN t_vbpa
        WHERE vbelv EQ t_vbpa-vbeln
        AND   vbtyp_n EQ 'M'.
      IF sy-subrc EQ 0.
        SORT t_vbfa BY vbelv
                       posnv
                       vbeln
                       posnn.
      ENDIF.

    ENDIF.

  ENDIF.

ENDIF.

LOOP AT t_vbak.

  READ TABLE t_vbuk WITH KEY vbeln = t_vbak-vbeln
                             BINARY SEARCH
                             TRANSPORTING
                               gbstk
                               fkstk.
  IF sy-subrc EQ 0.
    READ TABLE t_vbpa WITH KEY vbeln = t_vbak-vbeln
                               BINARY SEARCH
                               TRANSPORTING
                                 kunnr
                                 adrnr.
    IF sy-subrc EQ 0.

      t_z92sales-vbeln     = t_vbak-vbeln.
      t_z92sales-erdat     = t_vbak-erdat.
      t_z92sales-ernam     = t_vbak-ernam.
      t_z92sales-vkorg     = t_vbak-vkorg.
      t_z92sales-vkbur     = t_vbak-vkbur.
      t_z92sales-kunnr     = t_vbak-kunnr.
      t_z92sales-kunnr_shp = t_vbpa-kunnr.
      t_z92sales-adrnr     = t_vbpa-adrnr.
      t_z92sales-gbstk     = t_vbuk-gbstk.
      t_z92sales-fkstk     = t_vbuk-fkstk.

      READ TABLE t_vbfa WITH KEY vbelv = t_vbak-vbeln
                                 BINARY SEARCH
                                 TRANSPORTING NO FIELDS.
      IF sy-subrc EQ 0.
        w_index = sy-tabix.

        LOOP AT t_vbfa FROM w_index.

          IF t_vbfa-vbelv GT t_vbak-vbeln.
            EXIT.
          ELSEIF t_vbfa-vbelv EQ t_vbak-vbeln.
            t_z92sales-vbeln_i = t_vbfa-vbeln.
            APPEND t_z92sales.
          ENDIF.

        ENDLOOP.

        CLEAR  t_z92sales.

      ELSE.
        APPEND t_z92sales.
        CLEAR  t_z92sales.
      ENDIF.

    ENDIF.

  ENDIF.

ENDLOOP.

Regards,

Mark

Re: Performance of a SQL Query

Former Member — Fri, 18 May 2007 04:34:59 GMT

Hi,

If u want to imrpove perfromance of this query:

1. Remove corrosponding fields of table option

2. first use all inner join and outer join condition and then write seprate sql for for all entreis in vbeln_s

3. in worst case give inner joins in one query and outer join in other and for all entries in third one, but it think 1.2. will work....

Jogdand M B

Re: Performance of a SQL Query

Former Member — Fri, 18 May 2007 11:41:54 GMT

HI Mark,

Thanks for the promp reply.

Your previous code is working absolutely fine as i made few changes as per my requirement.

Ii need to improve the performanceof this Function module.

I am testing your code as well, will update you accordingly. Please prvovide me your persoanl mail ID so that i can interact with you directly.

Cheers and Regards,

Ramada Giri