NOT IN clause is a Performance Killer

 

Performance is a very important factor in terms of web based applications. Sometimes I find that the developer use the NOT IN clause in SQL statement which decrease the performance of entire SQL statements. Developers told that we don't have any options rater then it.

My suggestion is that, do not use the NOT IN clause in WHERE statements, instead use the NOT EXISTS statements which definitely increase the performance of your query.

To illustrate my points, I am providing you a simple example of SQL statements.

 

SELECT *

FROM   tableA

WHERE  tableA.tableBKey NOT IN (SELECT tableBKey

                                FROM   tableB

                                WHERE  fieldB1='something');

The above SQL statement is a performance Killer.

You can alter these statements like this to increase the real performance of SQL statement.

SELECT *

FROM   tableA

WHERE  NOT EXISTS( SELECT 1

                   FROM   tableB

                   WHERE  tableBKey = tableA.tableBKey

                          AND fieldB1 = 'something')

 

Hope this article is quite informative and thanking you to provide your valuable time on it.

 

Posted by: MR. JOYDEEP DAS