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.
WHERE tableA.tableBKey NOT IN (SELECT tableBKey
The above SQL statement is a performance Killer.
You can alter these statements like this to increase the real performance of SQL statement.
WHERE NOT EXISTS( SELECT 1
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