10-19-2005 5:06 PM
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.
10-19-2005 5:17 PM
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
10-19-2005 5:14 PM
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
10-19-2005 5:17 PM
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
10-19-2005 5:18 PM
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
10-19-2005 6:30 PM
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
10-19-2005 6:33 PM
10-19-2005 7:24 PM
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
10-20-2005 2:41 PM
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
10-19-2005 5:28 PM
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
10-19-2005 5:46 PM
10-19-2005 5:51 PM
> Hi Rob,
> We have 4,701,555 records in KNA1 table.
How many Bill-to records?
10-19-2005 5:56 PM
10-19-2005 6:15 PM
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
10-19-2005 10:23 PM
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.
10-20-2005 2:19 AM
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.
10-20-2005 6:50 AM
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