topic Re: Performance tuning: Retrieving data from 4 tables in Application Development and Automation Discussions

Performance tuning: Retrieving data from 4 tables

Former Member — Mon, 12 Nov 2007 05:06:56 GMT

Hi All,

Please find below a select stmt and recommend a better one.

select a_{belnr a}bldat a_{budat a}monat a_{usnam a}bktxt a_{waers b}racct b_{wsl b}drcrk b_{buzei c}txt50 d~altkt

into table gt_all

from bkpf as a inner join faglflexa as b on a_{belnr = b}docnr

inner join skat as c on b_{racct = c}saknr

inner join skb1 as d on c_{saknr = d}saknr

where a~bukrs = p_bukrs and

a~gjahr = p_gjahr and

a~blart = p_blart and

a~budat in s_budat and

a~monat in s_monat and

b~ryear = p_gjahr and

b~rbukrs = p_bukrs and

b~rldnr = '0L' and

b~rvers = '001'.

Reward points awaiting.....

Regards,

Younus

Re: Performance tuning: Retrieving data from 4 tables

Former Member — Mon, 12 Nov 2007 05:34:32 GMT

don't use JOIN for 4 tables use FOR ALL ENTRIES

because if use JOIN for that 4 tables the join will exit for that tables until the program execution which results in load on database and it will reduce the performance

if use FOR ALL ENTRIES then at once it will retrive the data and there won't be any load on database result in good performance

use like this

START-OF-SELECTION.

SELECT OTYPE

OBJID

RELAT

BEGDA

ENDDA

SCLAS

SOBID FROM HRP1001 INTO TABLE IT_HRP1001

WHERE OTYPE = 'D'

AND OBJID IN S_OBJID

AND BEGDA GE DATE-LOW

AND ENDDA LE DATE-HIGH

AND ( SCLAS = 'E' OR SCLAS = 'ET' ).

IF SY-SUBRC NE 0.

MESSAGE 'NO RECORD FOUND FOR THE GIVEN SELECTION CRITERIA ' TYPE 'E'.

ENDIF.

SELECT OTYPE

OBJID

AEDTM

UNAME

DELET

CANCR

FROM HRP1026

INTO TABLE IT_HRP1026

FOR ALL ENTRIES IN IT_SOBID

WHERE OBJID = IT_SOBID-SOBID

AND ( OTYPE = 'E' OR OTYPE = 'ET' )

AND DELET = 'X' AND

BEGDA GE DATE-LOW AND

ENDDA LE DATE-HIGH.

IF SY-SUBRC EQ 0.

SELECT OBJID

STEXT

FROM HRP1000

INTO TABLE IT_HRP1000

FOR ALL ENTRIES IN IT_SOBID

WHERE OBJID = IT_SOBID-SOBID AND

BEGDA GE DATE-LOW AND

ENDDA LE DATE-HIGH.

SELECT CANCR

CANCRT

FROM T77CRT

INTO TABLE IT_REASON

FOR ALL ENTRIES IN _HRP1000

WHERE CANCR = IT_HRP1026-CANCR

AND LANGU = 'E' .

ENDif.

use in this way FOR ALL ENTRIES

<b>Reward if usefull</b>

Re: Performance tuning: Retrieving data from 4 tables

Former Member — Mon, 12 Nov 2007 07:27:42 GMT

Hi Yonus,

do like this

declare independent internal table and 1 final table for each table and fetch data as below.

select belnr bldat budat monat usnam bktxt waers from bkpf into table it_bkpf where bukrs = p_bukrs and
gjahr = p_gjahr and
blart = p_blart and
budat in s_budat and
monat in s_monat.
if sy-subrc = 0.
 if it_bkpf[] is not initial.
  select docnr racct wsl drcrk buzei from faglflexa into table it_faglflexa
   for all entries in it_bkpf
   where docnr = it_bkpf-belnr and
            ryear = p_gjahr and
            rbukrs = p_bukrs and
            rldnr = '0L' and
            rvers = '001'.
   if sy-subrc = 0.
      if it_faglflexa[] is not intial.
         select saknr txt50 from skat into table it_skat
           for all entries in it_faglflexa
            where saknr = it_faglflexa-racct.
         if sy-subrc = 0.
          select saknr altkt from skb1 into table it_skb1 
           for all entries in it_skat
             where saknr = it_skat-saknr.
         endif.
      endif.
   endif.
 endif.
endif.

Once you get all the data to your internal tables, read one by one and move to final internal table.

Hope this will help you.

<b>Reward for helpful answers</b>

Satish

Re: Performance tuning: Retrieving data from 4 tables

Former Member — Mon, 12 Nov 2007 14:22:33 GMT

1) Rather than using the inner-joins on 4 tables, it would be better to have individual select statements and reconciling

the data later into one single table.

2) Fetch data from BKPF based on the information you have, i.e.

select bukrs gjahr belnr bldat budat monat usnam bktxt waers

from bkpf

into table t_bkpf

where bukrs = p_bukrs

and gjahr = p_gjahr

and blart = p_blart

and budat in s_budat

and monat in s_monat.

3) For all the entries selected above, fetch data from "FAGLFLEXA" based on the document number, fiscal year and company code

in t_bkpf table. Based on the "KEY" in table "FAGLFLEXA" you may fetch more fields from BKPF if required.

IF NOT t_bkpf[] IS INITIAL.

select rbukrs docnr rgjahr buzei racct wsl drcrk

from table faglflexa

into table t_fagl

for all entries in t_bkpf

where rbukrs = t_bkpf-bukrs

and rgjahr = t_bkpf-gjahr

and docnr = t_bkpf-belnr

and rldnr = 'OL'

and rvers = '001'.

ENDIF.

4) Selecting from SKAT. Based on the accounts obtained in t_fagl, we can get the G/L account long text from SKAT. But there

will be mulitple lines with same account in t_fagl. Also, while selecting from SKB1, G/L account and company code are the

details required for fetching the data.

So, may be you can take the data in t_fagl into some other internal table t_fagltemp.

SORT t_fagltemp BY BUKRS RACCT.

DELETE ADJACENT DUPLICATES in t_fagltemp COMPARING FIELDS BUKRS RACCT.

5) IF NOT t_fagltemp[] IS INITIAL.

select spras ktopl saknr

from SKAT

INTO table t_skat

for all entries in t_fagltemp

where saknr = t_fagltemp-racct.

ENDIF.

Here, if the company code i.e. p_bukrs is a parameter and mandatory field, then you can actualll get the language key

i.e. field "SPRAS" from company code master data. And the same can be used in the above query on SKAT.

6) IF NOT t_fagltemp[] IS INITIAL.

select bukrs saknr altkt

FROM TABLE SKB1

into table t_skb1

for all entres in t_fagltemp

where bukrs = t_fagltemp-bukrs

and saknr = t_fagltemp-racct.

ENDIF.

Based on the information obtained above, the data can be formatted according to the requirement into a single internal table.

Re: Performance tuning: Retrieving data from 4 tables

Former Member — Mon, 12 Nov 2007 14:42:33 GMT

JOINs usually perform better than FOR ALL ENTRIES, contrary to what many believe. Please see:

<a href="/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better">JOINS vs. FOR ALL ENTRIES - Which Performs Better?</a>

and then try:

SELECT a~belnr a~bldat a~budat a~monat a~usnam a~bktxt a~waers
       b~racct b~wsl   b~drcrk b~buzei c~txt50 d~altkt
  INTO TABLE gt_all
  FROM bkpf AS a
    INNER JOIN faglflexa AS b
      ON a~belnr = b~docnr
    INNER JOIN skat AS c
      ON b~racct = c~saknr
    INNER JOIN skb1 AS d
      ON  d~bukrs = a~bukrs     "<== Include company code
      AND d~saknr = c~saknr
  WHERE a~bukrs = p_bukrs  AND
        a~bstat = ' '      AND  "<== Any others you want too
        a~budat IN s_budat AND
        a~gjahr = p_gjahr  AND
        a~blart = p_blart  AND
        a~monat IN s_monat AND
        b~ryear = p_gjahr  AND
        b~rbukrs = p_bukrs AND
        b~rldnr = '0L'     AND
        b~rvers = '001'    AND
        c~spras = sy-langu AND  "<== Language
        c~ktopl = '????'.       "<== Chart of accounts

Rob