CODEWITHSUNDEEP
sqlsql-serverperformancewhere-clause
Brent Heritier
Brent Heritier

Reputation: 81

Obnoxious WHERE Clause in UPDATE

I found the following WHERE clause in an UPDATE statement, and I hate it. The only way I can think to make it different would be with possibly a CTE and some Unions.

FROM dbo.Table1 T1      
INNER JOIN #Table2 T2 ON T1.IntField1 = T2.IntField1
WHERE (ISNULL(T1.IntField2, 0) <> ISNULL(T2.IntField2, 0)
    OR ISNULL(T1.IntField3, 0) <> ISNULL(T2.IntField3, 0))
    AND (T2.IntField1 IN (
        SELECT IntField1
        FROM dbo.Table3)
        OR T2.IntField1 IS NULL)

I think I've just been staring at this too long. I just happened to look at this SP, and see this. Really felt like something could be done differently/better.

Upvotes: 0

Views: 90

Answers (2)

HLGEM
HLGEM

Reputation: 96570

It's not the prettiest no, but no real need to change it unless it is performing badly. Don't ever change SQL code just because you don't like the way it looks, that is often counterproductive because some of the worst looking code is the most performant and the DBA will not thank you for changing their tuned code. Thinking you should change SQL code to suit your personal prefernces is BAD habit you need to break. Read up about performance tuning instead and refactor to improve performance not to suit your prejudices of what is pretty or (worse elegant!) code.

There are two things I can see that might help this though. First why do you need OR T2.IntField1 IS NULL? Since you are joining in an Inner join to table1 on that field, there can never be a result set where T2.IntField1 IS NULL.

The other thing depends on what else #table2 is used for. But since you are clearly creating and populating this table earlier, why not do the conversion of the T2.IntField2 and T2.IntField3 to 0 when they are null at the time the data is put into the table? That would reduce the complexity of the update query some. However, if you need those nulls for some other purpose during the process you can't do this.

Upvotes: 1

mrtig
mrtig

Reputation: 2267

It looks like you could combine the elements of the where clause into joins:

Overview:

1) NOT (A AND B) is the same as NOT(A) OR NOT(B)

2) IN OR NULL can be combined in an ISNULL() join.

FROM dbo.Table1 T1      
    JOIN #Table2 T2 ON T1.IntField1 = T2.IntField1
        AND NOT 
        (
             ISNULL(T1.IntField2, 0) = ISNULL(T2.IntField2, 0)
             and 
             ISNULL(T1.IntField3, 0) = ISNULL(T2.IntField3, 0)
        )
    JOIN dbo.Table3 t3 on 
         t3.IntField1 = ISNULL(T2.IntField1, t3.IntField1)

But as it's been stated before, if performance is the only focus, this -- although more readable (in my opinion) -- is not necessary.

Upvotes: 0

Related Questions