05-02-2012 7:21 AM
Hi,
I am facing a performance issue when fetching vkont and gpart from dfkkop table.
Iam confused with the following options.
Please suggest me to get better performance for large volumes of data in production.
Option 1:
Using Inner Join:
SELECT b~opbel
b~augst
b~gpart
b~vkont
b~bldat
INTO TABLE it_dfkkop
FROM dfkkko AS a INNER JOIN dfkkop AS b
ON a~opbel = b~opbel
WHERE a~bldat IN s_date
AND b~augst = ' '.
Option 2:
For all entries:
SELECT opbel
bldat
FROM dfkkko
INTO TABLE it_dfkkko
WHERE bldat IN s_date.
CHECK it_dfkkko[] IS NOT INITIAL.
SORT it_dfkkko BY opbel.
SELECT opbel
augst
gpart
vkont
bldat
FROM dfkkop
INTO TABLE it_dfkkop
FOR ALL ENTRIES IN it_dfkkko
WHERE opbel = it_dfkkko-opbel
AND augst = ' '.
Thanks & Regards
Jayasree
05-02-2012 7:23 AM
05-02-2012 7:49 AM
Raymond,
I dont have much data in Dev.
But for Inner join its taking 81,827 sec for dfkkko and for 'for all entries' 296 for dfkkko and 776 for dfkkop.
Regards
Jayasree
05-02-2012 8:10 AM
I hope you have miore data on quality system than in DEV.
- Selection on dfkkko - AFAIK there is no index on BLDAT
- Selection on dfkkop - There is an index on AUGST (DFKKOP~1)
- No database view found on my system which could be useful.
Assuming that most positions are cleared, you could try to reverse the order in the JOIN or the FOR ALL ENTRIES, selecting first from dfkkop ?
Regards,
Raymond
05-02-2012 8:30 AM
Earlier it was
* SELECT AUGST
* GPART
* VKONT
* FROM DFKKOP
* INTO TABLE IT_DFKKOP_TMP
* WHERE BLDAT IN S_DATE AND
* AUGST = SPACE.
This is taking much time.
We do have a index for BLDAT in DFKKKO.
Regards
Vijayasree
05-02-2012 12:59 PM
Try to create a secondary index for the fields you use in the where condition and use that secondary index in the select statement, which will improve the performance.
05-02-2012 1:06 PM
I do have a secondary index on BLDAT in DFKKKO.
My question is from the above two options (For all entries / Inner Join ) which one is the better in order to improve performance?
05-02-2012 3:01 PM
Moderator message - discussed many, many times. Please search.
Locked.
Rob