topic Re: Joining FI tables in Application Development and Automation Discussions

Joining FI tables

Former Member — Wed, 14 Mar 2007 02:57:24 GMT

Hi,

I'm trying to join the following FI tables into 1 table:

bkpf

kna1

knb1

bsid

and these into another table:

bkpf

kna1

knb1

bsad

...so any efficient way of joining these? Current code uses inner joins.. but its causing performance issues.

i'm thinking of using for all entries...

any comments on which method? and why that method? if use for all entries...

what is the order i should start with?

Thanks.

Re: Joining FI tables

Former Member — Wed, 14 Mar 2007 03:23:25 GMT

you can go for for all entries start from bkpf you can get belnr gjahr bukrs and you can link to bsid or bsad to get kunnr for kna1.

another way you can create some views for those tables and access that in your program.

regards

shiba dutta

Re: Joining FI tables

Former Member — Wed, 14 Mar 2007 03:25:26 GMT

hi,

in the select statement take all the fields from the corresponding tables and put the inner join condition between the table ....

bkpf and bsid have the common key ie company code bukrs and bsid, kna1, knb1 have the common filed kunnr. so u can join the those table with those fields in the where condition

second one is also same keys so go ahead try to write the select statement with inner joins

~~Guduri

Re: Joining FI tables

Former Member — Wed, 14 Mar 2007 03:44:02 GMT

i wont' use inner join to join the 4tables..

i'm doing performance tuning. the current code is using inner join, which is very slow.

Re: Joining FI tables

Former Member — Wed, 14 Mar 2007 03:46:20 GMT

i'm also taking into consideration this information

total no of records in tables:

kna1: 2,356,681

knb1: 6,430,912

bkpf: 30,489,104

bsid: 507,874

bsad: 18,075,629

Re: Joining FI tables

Former Member — Wed, 14 Mar 2007 03:50:04 GMT

I have answered you in another post, here it is

You first need to understand what is contained in the secondary index tables as compared to the main FI document posting table BKPF. Then you can make a better decision in joining.

The index tables BSID (only customer active postings) and BSAD (only customer and that too cleared postings, huge piles up over time) are much smaller in size compared to BKPF which also contains postings to general ledger, vendor posting and so on. So BSID is subset of BSAD which in turn is subset of BKPF.

KNA1 on the other hand is master data table and should be still smaller in size (why because for each customer there may be many FI postings)

Also the MOST IMPORTANT point is which fields are available to you for selection? For example, if you have customer number, first join KNA1 with KNB1 and then with BSAD or BSID, then join with BKPF.

So tell us the selection fields that are available to you to seed the query

Re: Joining FI tables

Former Member — Wed, 14 Mar 2007 03:57:46 GMT

Thanks for all the input. I'll reward after i try them out.

i'm also taking into consideration this information

total no of records in tables:

kna1: 2,356,681

knb1: 6,430,912

bkpf: 30,489,104

bsid: 507,874

bsad: 18,075,629

some fields include:

bukrs "Company code, mandatory, usually single value selection

kunnr "Customer No, mandatory, may be single, range or selecting all

busab "Accounting clerk, mandatory, may be single or range values

ktokd "Customer Account Group, mandatory, usually single value selection

budat "13 months selection, mandatory, range, fixed range based on current date

zuonr " not mandatory

Re: Joining FI tables

Former Member — Fri, 16 Mar 2007 07:42:34 GMT

any more input? thx

Re: Joining FI tables

Former Member — Fri, 16 Mar 2007 08:08:43 GMT

i'm thinking of the following steps:

1) inner join kna1, kbb1

2) access bsid with for all entries (kna1, kbb1)

3) access bsad with for all entries (kna1, kbb1)

4) append bsad to bsid itab

5) acess bkpf with for all entries (bsid itab)

the problem is i'm cant really make use of indexes for accessing bsid and bsad...

which is causing the slowness...

accessing bsid has the same selection criteria and where condition as bsad..

but these the where fields are either using IN and ranges.. index cannot be used..


"select with quite a number of fields....
      WHERE bukrs IN s_bukrs 
        AND kunnr IN s_kunnr
        AND budat BETWEEN backdate_13 AND p_r_date  
        AND blart IN r_blart 
        AND zuonr IN szuonr.

Re: Joining FI tables

Former Member — Fri, 16 Mar 2007 10:59:28 GMT

What is wrong with this SQL statement? abap dump generated

checking through ST11...found this..

ERROR => max. statement length (65536) exceeded

there are

114 records in r_blart

44 records in itab

in this selection statement


    SELECT
      bukrs
      belnr
      gjahr
      kunnr
      blart
      buzei
      budat
      bldat
      mansp
      shkzg
      xblnr
      bschl
      dmbtr
      zuonr
      INTO TABLE i_bsid
      FROM bsid
      FOR ALL ENTRIES IN itab
      WHERE bukrs = itab-bukrs
        AND kunnr = itab-kunnr
        AND budat BETWEEN backdate_13 AND p_r_date
        AND blart IN r_blart
        AND zuonr IN szuonr.

Re: Joining FI tables

Former Member — Sat, 17 Mar 2007 09:08:25 GMT

anyone have better ideas?

This is just a normal report with total of almost 30 variants, not using alv...

but requires calculation..so no choice but to combine everything into a big itab...

the problem is with the inner join of the 4 tables.

& this is done 2 times.. 1st with bsid.. then 2nd with bsad.. & then both itabs are combined.

i realised if i break up this 2 inner join statements into smaller statements and use for all entries... i may encounter other problems..

eg. if i start by inner join kna1 and knb1..

some variants will result in a huge amount of customer data... so for all entries would have problem here

Re: Joining FI tables

Former Member — Sat, 17 Mar 2007 14:44:53 GMT

The max statement length may be exceeded due to big number of entries in your select-options & internal table (itab, r_blart, szuonr).

For all entries usually splits the table itab into packages of ~ 10 values and passes to DB request like where kunnr in ('...','...').

In your case these 10 kunnrs + 114 blarts + if anything in szuonr is causing overflow of where clause.

To fix - just split your big select-options (if they have too many entries) into smaller parts (use ranges) and use select ... appending your internal table.

On your steps :

1) inner join kna1, kbb1

2) access bsid with for all entries (kna1, kbb1)

3) access bsad with for all entries (kna1, kbb1) <<< you may use "appending table your internal table" here...

4) append bsad to bsid itab <<<<< remove duplicates before selecting from bkpf.

5) acess bkpf with for all entries (bsid itab)

BUT, if select from bkpf is more restrictive than BSID/BSAD, you may actually use

1) inner join kna1, kbb1

2) inner join bsid, bkpf with for all entries (kna1, kbb1)

3) inner join bsad, bkpf with for all entries (kna1, kbb1), "appending table"

Re: Joining FI tables

Former Member — Sat, 17 Mar 2007 14:59:40 GMT

problem already occurs at

2) access bsid with for all entries (kna1, kbb1)

there r too many rows in blart.. which cannot use range

plus.. if the variant selects a range of kunnr (customers)...

it either cause abap dump...

or in some variants.. slower than the inner join of 4 tables... although in some variants... it is faster..

Re: Joining FI tables

Former Member — Sun, 18 Mar 2007 05:56:52 GMT

any more ideas?

thanks.

i can't seem to improve the performance for this 2 inner join SQL statements..

Re: Joining FI tables

Former Member — Mon, 19 Mar 2007 17:41:06 GMT

- I would definitely add GJAHR to the list of select-options for your bsid, bsad.

you can use something like :

ranges: r_blart ...

if your s_blart has > 20 entries, use somehting like :

loop at s_blart

if counter = 20.

select from bsid ... as before.... APPENDING table itab_bsid ...

refresh r_blart.

clear counter.

endif.

increment counter.

append s_blart into r_blart.

endloop.

if r_blart is not initial - repeat select for the last package of r_blart.

As an option, you can include all your logic into this loop... it will reduce the size of internal tables .. like :

loop at s_blart

if you collected 20 blarts

- select from bsid

- select from bsad

- select from bkpf "appending table" !!!

refresh it_bsid, it_bsad.

endif

endloop.

Check which iindexes are available for your bsid/bsad/bkpf.

adding GJAHR (you can calculate it based on budat) will help for bsid for sure.

Re: Joining FI tables

Former Member — Tue, 20 Mar 2007 12:12:21 GMT

will open cursor help in this case?

Re: Joining FI tables

Former Member — Tue, 20 Mar 2007 12:26:30 GMT

yeah , that is the best option to get data from FI tables.

Regards

prabhu