Increase speed of a NOT EQUAL operator and a workaround using a computed column

In almost all cases where a NOT EQUAL  operator (<>) is used in a JOIN or a WHERE there will be a scan. Although you can often avoid a scan by using 2 <> , it’s always better to try and use an EQUAL  OPERATOR (=)

This situation becomes pronounced when there is an attempt at a large bulk load or archiving job. Applying the <> operator can have a serious impact on run times of jobs. It may not be as noticeable over a few rows , but if you’re targeting millions of rows, you may need to rethink your strategy.

There are plenty of workarounds to this problem. It may require you break away from completing the task in a 1 step transaction, it may require a few interim steps.

One particular workaround is to use a computed column . Let’s work through an example.

Step 1 – Initial sql statement with a NOT EQUAL operator , which generates a Scan

SELECT *

 FROM  myTable1 mt

WHERE mt.myvalue <> ' hghghg ';

Step 2 – Create a computed column for rows which qualify to be manipulated

ALTER TABLE myTable ADD computed_col AS (CASE WHEN  myvalue = 'hghghg' then 1 ELSE 0 END)

All of a sudden you have an extra column which has a value based on the condition you’ve set

Step 3 – Adjust the SQL code from Step 1 , notice now it includes the equal operator

SELECT *

FROM myTable mt

WHERE mt.computed_col = 1

This workaround is not suitable for every situation where you may see a scan as a result of a NOT EQUAL operator. But it is a useful technique for ad-hoc bulk edit jobs.

You may need to drop the computed column when completed.

Before you use the computed column workaround, doublecheck with the application owner or vendor , as in some cases adjustments to tables are not allowd.

Read More on speeding up bulk operations

How to delete millions of rows in batches

How to complete an INSERT in batches for large data sets

Data Loading Performance Guide