SQL Performance

former_member305179 — Tue, 17 Feb 2015 13:29:34 GMT

Hello,

i have one Report with two SQL Statements to collect data from DB. First time the Select-Statement p_avovo == abap_true was very fast. After perform the second SQL-Statement, the Performance of the first one decrease. After refreshing Analyse table with db13 Performance increase.

Can someone tell me how the db optimization works? - is the db Analyse table bound with Report?

IF abap_true = p_avovo.
      SELECT
        "afvc
        a~arbid a~vornr a~werks a~bedid
        "afko
        o~aufnr o~cy_seqnr o~ftrmi
        "aufk
        k~objnr k~auart k~erdat
        "afvv
        v~ssavd v~ssedd v~isdd v~iedd
        "afpo
        p~matnr AS kmatnr
        "crhd
        c~veran c~arbpl
        "jest
        j1~stat AS frei j1~inact AS frei_i
        j2~stat AS loekz j2~inact AS loekz_i
        j3~stat AS sper j3~inact AS sper_i
        j4~stat AS loevm j4~inact AS loevm_i
        INTO CORRESPONDING FIELDS OF TABLE lt_liefertreue
        FROM        afvc AS a
        INNER JOIN t430 AS t ON (      a~steus = t~steus AND t~ruek = 2 )
        INNER JOIN crhd AS c ON (      a~arbid = c~objid )
        INNER JOIN afko AS o ON (      a~bedid = o~bedid )
        INNER JOIN aufk AS k ON (      o~aufnr = k~aufnr )
        INNER JOIN afvv AS v ON (      a~aufpl = v~aufpl AND a~aplzl = v~aplzl
                                  "avo-vorrat aus SAP
                                  AND ( v~ssedd >= gv_ssedd_von AND v~ssedd <= gv_ssedd_bis )
                                  )
        LEFT JOIN   afpo AS p ON (      o~aufnr = p~aufnr AND p~posnr = '1' )
        LEFT JOIN   jest AS j1 ON (     k~objnr = j1~objnr AND j1~stat = 'I0002' ) "FREI
        LEFT JOIN   jest AS j2 ON (     k~objnr = j2~objnr AND j2~stat = 'I0013' ) "LÖKZ
        LEFT JOIN   jest AS j3 ON (     k~objnr = j3~objnr AND j3~stat = 'I0043' ) "SPER
        LEFT JOIN   jest AS j4 ON (     k~objnr = j4~objnr AND j4~stat = 'I0076' ) "LÖVM
        ORDER BY c~arbpl ASCENDING..

    ELSE.
      SELECT
        "afvc
        a~arbid a~vornr a~werks a~bedid
        "afko
        o~aufnr o~cy_seqnr o~ftrmi
        "aufk
        k~objnr k~auart k~erdat
        "afvv
        v~ssavd v~ssedd v~isdd v~iedd
        "afpo
        p~matnr AS kmatnr
        "crhd
        c~veran c~arbpl
*      "jcds
        j~stat j~udate
        INTO CORRESPONDING FIELDS OF TABLE lt_l
        FROM        jcds AS j
        INNER JOIN afvc AS a ON (      a~objnr = j~objnr )
        INNER JOIN afvv AS v ON (      a~aufpl = v~aufpl AND a~aplzl = v~aplzl )
        INNER JOIN t430 AS t ON (      a~steus = t~steus AND t~ruek = 2        )
        INNER JOIN crhd AS c ON (      a~arbid = c~objid )
        INNER JOIN afko AS o ON (      a~bedid = o~bedid )
        INNER JOIN aufk AS k ON (      o~aufnr = k~aufnr )
        LEFT JOIN   afpo AS p ON (      o~aufnr = p~aufnr AND p~posnr = '1' )
        WHERE j~udate = gv_xdat
        AND j~objnr LIKE 'OV%'
        AND j~stat = 'I0009'
        AND j~chgnr = '1'
        AND j~inact = ''
        AND j~chind = 'I'
*        AND a~werks = gv_werks
*        AND k~auart IN s_aufart
        ORDER BY c~arbpl ASCENDING.

    ENDIF.

Re: SQL Performance

matt — Tue, 17 Feb 2015 13:35:44 GMT

What database do you have?

Re: SQL Performance

former_member305179 — Tue, 17 Feb 2015 13:39:16 GMT

We use MS SQL

topic Re: SQL Performance in Application Development and Automation Discussions

SQL Performance

Re: SQL Performance

Re: SQL Performance