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

20 March,2016 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

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


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer