topic Re: Performance Issue - Selecting cuplicate name from KNA1 table in Application Development and Automation Discussions

Performance Issue - Selecting cuplicate name from KNA1 table

Former Member — Wed, 19 Oct 2005 16:06:16 GMT

My colleague wrote this query and it takes more than 15 hours to finish in PROD. So now, I have to do the performance tuning. Please let me know if you have any idea ( i.e. split it or create index for name1) to make it faster? Purpose of this query is to search through the customer master table(KAN1) for all accounts in the Bill-to account group(0004) where there is more than one record with the same bill-to name.

SELECT kunnr name1 stras ort01 regio pstlz telf1

FROM kna1 INTO TABLE it_kna1

WHERE ktokd = '0004'

AND name1 IN

( select name1 FROM kna1

WHERE ktokd = '0004'

GROUP BY name1 HAVING COUNT( * ) > 1 )

ORDER BY kunnr.

Re: Performance Issue - Selecting cuplicate name from KNA1 table

former_member517 — Wed, 19 Oct 2005 16:14:32 GMT

Hi Kishore,

if you have a reasonable number of customers, I would select the whole KNA1 table into an internal table, sort it by name and select the duplicates in a loop. We did this with about 50.000 customers without problems.

Best regards

Rabanus

Re: Performance Issue - Selecting cuplicate name from KNA1 table

RichHeilman — Wed, 19 Oct 2005 16:17:32 GMT

This logic works pretty good in my system. It may not look pretty, but it works pretty fast.



report zrich_0002  .


data: it_kna1 type table of kna1 with header line.
data: it_kna12 type table of kna1 with header line.
data: it_kna13 type table of kna1 with header line.
data: counter type i.

select kunnr name1 stras ort01 regio pstlz telf1
         from kna1 into corresponding fields of table it_kna1
                where ktokd = '0004'.

it_kna12[] = it_kna1[].

sort it_kna1 ascending by name1.
delete adjacent duplicates from it_kna1 comparing name1.

loop at it_kna1.

  clear counter.
  loop at it_kna12 where name1 = it_kna1-name1.
    counter = counter + 1.
  endloop.
  check counter > 1.
  loop at it_kna12 where name1 = it_kna1-name1.
    move-corresponding it_kna12 to it_kna13.
    append it_kna13.
  endloop.



endloop.

loop at it_kna13.
write:/ it_kna13-kunnr, it_kna13-name1.
endloop.

I just tested this logic on our KNA1 with 4757 records, it ran in under 5 seconds.

Regards,

Rich Heilman

Re: Performance Issue - Selecting cuplicate name from KNA1 table

Former Member — Wed, 19 Oct 2005 16:18:34 GMT

Do not do your count on the database. Instead, get the records into an internal table and do the search for duplicate names.


SELECT kunnr name1 stras ort01 
       regio pstlz telf1  FROM kna1 
                    INTO TABLE it_kna1 
                   WHERE ktokd = '0004'.
it_kna2[] = it_kna1[].
sort it_kna1 by name1.
sort it_kna2 by name1.
delete adjacent duplicates from it_kna1 comparing name1.
loop at it_kna1.
 loop at it_kna2 where name1 = it_kna1-name1.
  count = count + 1.
 endloop.
 write:/ it_kna1-kunnr, count.
 clear count.
endloop.

There are options to optimize the loop processing also, but depending on your itab records, we can try that.

Srinivas

Re: Performance Issue - Selecting cuplicate name from KNA1 table

Former Member — Wed, 19 Oct 2005 16:28:31 GMT

How many records do you have in KNA1 and how many are you selecting? I ran your select in our system and it took just a few seconds.

Rob

Re: Performance Issue - Selecting cuplicate name from KNA1 table

Former Member — Wed, 19 Oct 2005 16:46:56 GMT

Hi Rob,

We have 4,701,555 records in KNA1 table.

Re: Performance Issue - Selecting cuplicate name from KNA1 table

Former Member — Wed, 19 Oct 2005 16:51:37 GMT

> Hi Rob,

> We have 4,701,555 records in KNA1 table.

How many Bill-to records?

Re: Performance Issue - Selecting cuplicate name from KNA1 table

RichHeilman — Wed, 19 Oct 2005 16:56:19 GMT

Wow..... I'm not sure what to say. I think that any processing of those records is going to take some time. Maybe not 15 hours, but some time.

Regards,

Rich Heilman

Re: Performance Issue - Selecting cuplicate name from KNA1 table

former_member517 — Wed, 19 Oct 2005 17:15:26 GMT

Even with 4.000.000 customers I would try to do it with one internal table sorted by name. Then you could go through the table in a single loop with 2 pointers. If the last entry is unique, you can delete it . Otherwise you go forward until you find the next difference. My estimate is that this can be done under 1 hour.

Best regards

Rabanus

Re: Performance Issue - Selecting cuplicate name from KNA1 table

Former Member — Wed, 19 Oct 2005 17:30:41 GMT

OK that number changes the equation. Try this.


SELECT kunnr name1 stras ort01 
       regio pstlz telf1  FROM kna1 
                    INTO TABLE it_kna1 
                   WHERE ktokd = '0004'.
it_kna2[] = it_kna1[].
sort it_kna1 by name1.
sort it_kna2 by name1.
delete adjacent duplicates from it_kna1 comparing name1.
loop at it_kna1.
  clear v_index.
  read table it_kna2 with key name1 = it_kna1-name1 binary search.
  if sy-subrc = 0.
    v_index = sy-tabix + 1.
*-- check if there is another record. Since it is sorted 
*   by name1, the duplicate record should be right next
*   to this one.
    read table it_kna2 index v_index.
    if sy-subrc <> 0.
*-- no other record exists with the same name. delete it.
      delete it_kna2 where name1 = it_kna1-name1.
    endif.
  endif.
endloop.

By the end of this loop, you should have only duplicate entries in the it_kna2 table.

Srinivas

Re: Performance Issue - Selecting cuplicate name from KNA1 table

RichHeilman — Wed, 19 Oct 2005 17:33:04 GMT

I like Srinivas's solution. I think that this will be the fastest.

Regards,

Rich Heilman

Re: Performance Issue - Selecting cuplicate name from KNA1 table

Former Member — Wed, 19 Oct 2005 18:24:27 GMT

I like Srinivas' solution too; but I'd be concerned about the sizes of the internal tables. I don't know how many rows will be selected, but if it's large, it could dump. In that case, you could try a less elegant solution:


REPORT ztemp LINE-SIZE 80 MESSAGE-ID zc.

TABLES: kna1.

DATA: BEGIN OF it_kna1 OCCURS 0.
        INCLUDE STRUCTURE kna1.
DATA: END   OF it_kna1.

DATA: BEGIN OF it2_kna1 OCCURS 0.
        INCLUDE STRUCTURE kna1.
DATA: END   OF it2_kna1.

DATA: BEGIN OF it_kunnr OCCURS 0,
        kunnr LIKE kna1-kunnr,
      END   OF it_kunnr.

DATA: old_name1 LIKE kna1-name1,
      old_kunnr LIKE kna1-kunnr.

SELECT kunnr name1 FROM kna1 INTO (kna1-kunnr, kna1-name1)
WHERE ktokd = '0004'
ORDER BY name1.
  IF kna1-name1 <> old_name1.
    old_name1 = kna1-name1.
    old_kunnr = kna1-kunnr.
  ELSE.
    it_kunnr-kunnr = kna1-kunnr.
    APPEND it_kunnr.
    it_kunnr-kunnr = old_kunnr.
    APPEND it_kunnr.
  ENDIF.
ENDSELECT.

SORT it_kunnr BY kunnr.
DELETE ADJACENT DUPLICATES FROM it_kunnr COMPARING kunnr.

SELECT kunnr name1 stras ort01 regio pstlz telf1
  FROM kna1 INTO CORRESPONDING FIELDS OF TABLE it_kna1
  FOR ALL ENTRIES IN it_kunnr
  WHERE kunnr = it_kunnr-kunnr.

I think the original problem is partly caused by having this all done by the database server (generally a good idea, but in this case not working very well). I wouldn't try this unless there are problems with the other solution. (This one could actually run longer.)

Rob

I changed the account group back to what was in the original post.

Message was edited by: Rob Burbank

Re: Performance Issue - Selecting cuplicate name from KNA1 table

Former Member — Wed, 19 Oct 2005 21:23:19 GMT

Hi Kishore,

You can try this also.


TYPES:
  BEGIN OF ty_kna1,
    name1 TYPE kna1-name1,
    kunnr TYPE kna1-kunnr,
    stras TYPE kna1-stras,
    ort01 TYPE kna1-ort01,
    regio TYPE kna1-regio,
    pstlz TYPE kna1-pstlz,
    telf1 TYPE kna1-telf1,
  END OF ty_kna1.

DATA:
  t_kna1 TYPE SORTED TABLE OF ty_kna1
    WITH NON-UNIQUE KEY name1
    INITIAL SIZE 1000.
DATA:
  v_count TYPE sy-tabix.

FIELD-SYMBOLS:
  <fs_kna1> TYPE ty_kna1.

SELECT name1 kunnr stras ort01 regio pstlz telf1
  INTO TABLE t_kna1
  FROM kna1
  WHERE ktokd EQ '0004'.

*--- TEST SECTION 1 BEGIN
**--- Test data for concept
*DATA: s_kna1 type ty_kna1.
*REFRESH t_kna1.
*s_kna1-name1 = 'A'. INSERT s_kna1 INTO TABLE t_kna1.
*s_kna1-name1 = 'B'. INSERT s_kna1 INTO TABLE t_kna1.
*s_kna1-name1 = 'B'. INSERT s_kna1 INTO TABLE t_kna1.
*s_kna1-name1 = 'E'. INSERT s_kna1 INTO TABLE t_kna1.
*s_kna1-name1 = 'B'. INSERT s_kna1 INTO TABLE t_kna1.
*s_kna1-name1 = 'F'. INSERT s_kna1 INTO TABLE t_kna1.
*s_kna1-name1 = 'D'. INSERT s_kna1 INTO TABLE t_kna1.
*s_kna1-name1 = 'F'. INSERT s_kna1 INTO TABLE t_kna1.
*s_kna1-name1 = 'G'. INSERT s_kna1 INTO TABLE t_kna1.
*--- TEST SECTION 1 END

LOOP AT t_kna1 ASSIGNING <fs_kna1>.
* The ASSIGNING statement saves time by skipping a copy
* to header/work area
  AT NEW name1.
* Clear counter for every unique name
    CLEAR v_count.
  ENDAT.

  ADD 1 TO v_count.

  AT END OF name1.
    IF v_count EQ 1.
* Only one instance of NAME1 found
      DELETE t_kna1.
    ENDIF.
  ENDAT.
ENDLOOP.

* At this point, only records with duplicate records should be left

*--- TEST SECTION 2 BEGIN
**--- Output test data begin
*LOOP AT t_kna1 into s_kna1.
*  WRITE:/ s_kna1-name1.
*ENDLOOP.
*--- TEST SECTION 2 END

This eliminates the second internal table and the reads on it saving memory and time.

If you want to test it, remove the comments in the test section and you will see it.

If you go with this solution, then remove the code of the test sections and move it up.

Hope this helps you.

Rich/AK/Rob If there is any problem with it, please let me know so I can correct myself for the future.

Cheers,

-Ramesh

Changed code to save a few milli seconds by using the Field symbol.

Re: Performance Issue - Selecting cuplicate name from KNA1 table

Former Member — Thu, 20 Oct 2005 01:19:09 GMT

Apart from the good solutions given above you may like to give this a try -

<b>REPORT ZTEST .

DATA : BEGIN OF ITAB OCCURS 0,

NAME1 TYPE KNA1-NAME1,

KUNNR TYPE KNA1-KUNNR,

STRAS TYPE KNA1-STRAS,

ORT01 TYPE KNA1-ORT01,

REGIO TYPE KNA1-REGIO,

PSTLZ TYPE KNA1-PSTLZ,

TELF1 TYPE KNA1-TELF1,

END OF ITAB.

SELECT NAME1 KUNNR STRAS ORT01 REGIO PSTLZ TELF1

FROM KNA1 AS F INTO TABLE ITAB

WHERE KTOKD = '0004'

AND EXISTS ( SELECT * FROM KNA1 WHERE KUNNR NE F~KUNNR AND NAME1 EQ

F~NAME1 ).

IF SY-SUBRC NE 0.

ENDIF.</b>

Cheers.

Re: Performance Issue - Selecting cuplicate name from KNA1 table

Former Member — Thu, 20 Oct 2005 05:50:53 GMT

one thing you have to take care while using internal tables is memory issue. you might get the short dump with message

TSV_TNEW_PAGE_ALLOC_FAILED

refer to OSS note 20527

regards

Re: Performance Issue - Selecting cuplicate name from KNA1 table

Former Member — Thu, 20 Oct 2005 13:41:52 GMT

You could also try your original select but using MCOD1 instead of NAME1:


SELECT kunnr name1 stras ort01 regio pstlz telf1
  FROM kna1 INTO CORRESPONDING FIELDS OF TABLE it_kna1
  WHERE ktokd = '0004'
  AND mcod1 IN
    ( select mcod1 from KNA1
        WHERE ktokd = '0004'
        GROUP by mcod1 HAVING COUNT( * ) > 1 )
        ORDER BY kunnr.

MCOD1 is a secondary index based on NAME1 (but shorter). It has the advantage of being case insensitive, so it will match Jones with JONES, while the original select won't.

Rob

Changed the account group again.

Message was edited by: Rob Burbank