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.