KNVV and KNA1 join

[Former Member]

Dear All,

I am working on a KNVV and KNA1 join. Given below are the figures and requirements.

My requirement is to select all the customers and their related sales offices from KNVV & KNA1 join where customer should be a sold-to-party (KTOKD = 0001).

kna1: 8643029 records and if I take only sold-to-party into account then total 1247420 records (where KTOKD = 0001).

knvv: 8643029 records

My code as below:

data: begin of itab occurs 50000,

vkbur type vkbur,

kunnr type kunnr,

end of itab.

select knvv_{vkbur kna1}kunnr from knvv inner join kna1

on knvv_{mandt = kna1}mandt

and knvv_{kunnr = kna1}kunnr

into table itab where knvv~vkbur = 'VKBUR'

and kna1~ktokd = '0001'.

now for my sales office VKBUR, there are total 170421, but the resultant set is 50000 records. and for this select execution time taken is 11 - 15 minutes.

if i write the join in a reverse way, then also the execution takes 13 - 15 minutes. kna1 join knvv as below:

select kna1_{kunnr knvv}vkbur from kna1 inner join knvv

on kna1_{mandt = knvv}mandt

and kna1_{kunnr = knvv}kunnr

into table itab where kna1~ktokd = '0001'

and knvv~vkbur = 'VKBUR'.

if anybody could finetune the above stated situation.

Regards

Bhanu

[ThomasZloch]

Hi,

I suggest you use database view KNA1VV instead, then you don't need to worry about the join, SAP does it for you.

For quick access via KTOKD look at database view M_DEBIK.

Cheers

Thomas

[Former Member]

Hi Thomas,

Use of views does not work, it takes even more execution time, close to 30 minutes.

Regards,

Manas

[glio_ad]

Hi.

I would try the following things:

1) create an internal HASHED table for the KUNNRs in this KTOKD

2) Loop the hashed table and then, access KNVV with the KUNNR key and get the rest info you need.

In fact, VIEWs on large tables is not a good option sometimes. empirically, it is better to split the JOIN statements and try to create an internal table with the KEY fields of the next large table to access.

So, try this:

TYPES: BEGIN OF t_kunnr,

kunnr TYPE kunnr,

vkbur TYPE vkbur,

END OF t_kunnr.

DATA: i_kunnr TYPE HASHED TABLE OF t_kunnr

INITIAL SIZE 180000

WITH UNIQUE KEY kunnr,

wa_kunnr TYPE t_kunnr.

SELECT kunnr FROM kna1

INTO TABLE i_kunnr

WHERE ktokd = '0001'.

LOOP AT i_kunnr INTO wa_kunnr.

SELECT SINGLE vkbur FROM knvv

INTO wa_kunnr-vkbur

WHERE kunnr = wa_kunnr-kunnr.

MODIFY TABLE i_kunnr FROM wa_kunnr.

ENDLOOP.

Reward if it helps

Regards,

George

[glio_ad]

Hi again.

The code I have provided has a drwback though. Ii will fire 170000 select singles to KNVV to get the respective VKBURs. You could further enhance it as follows:

Instead of accessing KNVV for every single customer, you can create bunches of 100s or 1000s in a range and get these VKBURs for every 100s or 1000s of them at once in another internal. This way, you will reduce the SELECTs SINGLE to KNVV and you could probably further optimize the performance.

Check this sample code:

TYPES: BEGIN OF t_kunnr,

kunnr TYPE kunnr,

END OF t_kunnr.

TYPES: BEGIN OF t_vkburs,

kunnr TYPE kunnr,

vkbur TYPE vkbur,

END OF t_vkburs.

DATA: i_kunnr TYPE HASHED TABLE OF t_kunnr

INITIAL SIZE 180000

WITH UNIQUE KEY kunnr,

wa_kunnr TYPE t_kunnr.

DATA: i_vkburs TYPE HASHED TABLE OF t_vkburs

INITIAL SIZE 180000

WITH UNIQUE KEY kunnr,

wa_vkburs TYPE t_vkburs.

CONSTANTS: c_max TYPE i VALUE '1000'.

DATA: wl_cnt TYPE i.

RANGES: r_kunnr FOR kna1-kunnr.

SELECT kunnr FROM kna1

INTO TABLE i_kunnr

WHERE ktokd = '0001'.

LOOP AT i_kunnr INTO wa_kunnr.

ADD 1 TO wl_cnt.

IF wl_cnt <= c_max.

r_kunnr-sign = 'I'.

r_kunnr-option = 'EQ'.

r_kunnr-low = wa_kunnr-kunnr.

APPEND r_kunnr.

SELECT kunnr vkbur FROM knvv

APPENDING CORRESPONDING FIELDS OF TABLE i_vkburs

WHERE kunnr IN r_kunnr.

REFRESH r_kunnr.

ENDIF.

ENDLOOP.

IF NOT r_kunnr[] IS INITIAL.

SELECT kunnr vkbur FROM knvv

APPENDING CORRESPONDING FIELDS OF TABLE i_vkburs

WHERE kunnr IN r_kunnr.

REFRESH r_kunnr.

ENDIF.

Regards,

George

Edited by: George Lioumis on Jan 8, 2008 3:46 PM

[ThomasZloch]

I see. Well, you're browsing through almost 9 million records, unless you create a secondary index on KNVV-VKBUR, there will not be a really quick solution here I'm afraid.

Greetings

Thomas

[glio_ad]

Hi Thomas.

In fact the table is a bit.... too large (!!) but I don't think that a secondary index on VKBUR would help as the select on KNVV will be done using the customer number and not the Sales Office, so that index will not be used.

Regards,

George

[ThomasZloch]

Hello George,

I assume that the optimizer would use the new index when knvv~vkbur = ... appears in the select statement. But then it depends on how selective this really is, maybe there's just a few different values across the 9 million entries...

For my part we have given the OP some ideas, now it is on him.

Cheers

Thomas

[Former Member]

Hello George,

Thank you for the bright idea. Although I cannot use your idea entirely, but I have adopted some important part of your idea. Now i am working on it. If it reduces the runtime considerably, then I will let you know. I have rewarded your answer.

Thank you for your interest.

Bhanu

[glio_ad]

Hi Thomas.

You are right. The optimizer would use that new secondary index if it appeared in the where clause but I don't think that this is the case for our friend. In any case, I agree that it is up to him and it needs more of a try-and-error approach to find the best solution.

Regards,

George

[Former Member]

Hello Thomas,

Thank you for your interest and the proposal. But unfortunately, creating the secondary index over vkbur does not work in this case. I have already secondary index on sales office in table KNVV. Still it takes so much of time.

Regards

Manas

[Former Member]

Hello George,

I tried your idea of creating hashed table. Selecting data from kna1 and reading the same against knvv. but unfortunately, that does not enhance the performance too much.

Regards,

Bhanu

[Former Member]

Hi,

Try this code

Types: begin of ty_data,

kunnr type knvv-kunnr,

vkbur type knvv-vkbur,

end of ty_data.

Data: itab type table of ty_data,

wa type ty_data.

select kna1_{kunnr knvv}vkbur from knvv inner join kna1

on knvv_{kunnr = kna1}kunnr

into table itab where knvv~vkbur = 'VKBUR'

and kna1~ktokd = '0001'.

Regards,

Satish

[Former Member]

Hello Satish,

I have also tried this piece of code earlier, this does not give good result. For a single sales office where total number of records are close to 120000, this select takes 10-15 minutes. I also have a index on vkbur in table knvv. Still the execution time is too high. When I am watching the execution through trace, I am seeing that, maximum time is being consumed while reading KNA1 entries. It makes a sequential read.

Regards,

Bhanu