Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

SELECT Query with NOT EQUAL TO Condition in WHERE Clause

justine_t_babu
Explorer
0 Kudos

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.

1 ACCEPTED SOLUTION

Former Member
0 Kudos

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

3 REPLIES 3

Former Member
0 Kudos

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

muraleedharan_ks
Discoverer
0 Kudos

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

0 Kudos

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:

KNUMVKNUMVCHAR100Number of the document condition
KPOSNKPOSNNUMC60Condition item number
STUNRSTUNRNUMC30Step number
ZAEHKDZAEHKNUMC20Condition counter

    

In order to process faster you should use primary key in the select query.

Hope this is helpful to you.

Regards,

Dev.