Skip to Content
author's profile photo Former Member
Former Member

KNVV and KNA1 join

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 knvvvkbur kna1kunnr from knvv inner join kna1

on knvvmandt = kna1mandt

and knvvkunnr = kna1kunnr

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 kna1kunnr knvvvkbur from kna1 inner join knvv

on kna1mandt = knvvmandt

and kna1kunnr = knvvkunnr

into table itab where kna1~ktokd = '0001'

and knvv~vkbur = 'VKBUR'.

if anybody could finetune the above stated situation.

Regards

Bhanu

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Posted on Jan 08, 2008 at 01:11 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Thomas Zloch

      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

  • author's profile photo Former Member
    Former Member
    Posted on Jan 10, 2008 at 07:29 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.