09-11-2014 4:42 PM
Hi,
I have this curiosity regarding the performance impact of having a NOT EQUAL TO condition in the WHERE clause of a SELECT query. Please provide your inputs and reasoning on the performance impact on the below 2 scenarios.
1. SELECT knumv kposn kbetr
FROM konv
INTO TABLE it_konv
FOR ALL ENTRIES IN it_ekko
WHERE knumv = it_ekko-knumv.
IF sy-subrc = 0.
DELETE it_konv WHERE kbetr IS INITIAL.
ENDIF.
2. SELECT knumv kposn kbetr
FROM konv
INTO TABLE it_konv
FOR ALL ENTRIES IN it_ekko
WHERE knumv = it_ekko-knumv AND
kbetr <> lv_zero. "Initial or 0 value.
Between the two of the above scenarios, can you please help me evaluate which would have a better overall performance and why.
I am more interested in knowing the reason for the difference in performance probably with respect to index and DB optimizer than a direct answer that scenario 1 or 2 would work faster.
Appreciate the help.
Thanks in advance.
-Justine.
09-11-2014 5:28 PM
Find the answer here:
I have just done a small report with your select and tested in SE30, the selection without <> in where clause took 9.328 microsec. With '<>' operator the program took 51.086 microsec.
As you can see in the link above, if you use the table index in the selection, use the '<>' operator in a non-index field could help the selection, otherwise better not to use.
Regards,
Felipe
Message was edited by: Felipe Simionatto
09-11-2014 5:28 PM
Find the answer here:
I have just done a small report with your select and tested in SE30, the selection without <> in where clause took 9.328 microsec. With '<>' operator the program took 51.086 microsec.
As you can see in the link above, if you use the table index in the selection, use the '<>' operator in a non-index field could help the selection, otherwise better not to use.
Regards,
Felipe
Message was edited by: Felipe Simionatto
09-12-2014 7:34 AM
Ideally if I do the coding, I would avoid the delete statement. and it also depends on the number of entries that have kbetr as Initial or zero and the total number of records that you had fetched out of the selection.
you can control it on the loop, where you use the table by putting an additional if statement after the loop
Loop at it_konv Assigning ....
if <fs_konv>-kbetr is initial.
Continue.
endif.
....
Endif.
This would be definitely faster than pushing load to the database.
Regards
Muralee
09-12-2014 7:52 AM
Hi,
1 scenario will run much faster than 2 . Reason is that, in 2 scenario select query hit database with negative condition on non-primary key. Table KNOV have following primary keys:
KNUMV | KNUMV | CHAR | 10 | 0 | Number of the document condition |
KPOSN | KPOSN | NUMC | 6 | 0 | Condition item number |
STUNR | STUNR | NUMC | 3 | 0 | Step number |
ZAEHK | DZAEHK | NUMC | 2 | 0 | Condition counter |
In order to process faster you should use primary key in the select query.
Hope this is helpful to you.
Regards,
Dev.