I don't think this QUITE qualifies as a code sin, but it's close. I'm reviewing code today, and I wanted to prove beyond a shadow of a doubt that this:
UPDATE T2 SET col1 = CASE WHEN ( col1– @d ) > 0 THEN ( col1– @d) ELSE 0 ENDIs better than this:
UPDATE T1 SET col1 = CASE WHEN ( col1– @d ) > 0 THEN ( col1– @d) WHEN ( col1– @d) <= 0 THEN 0 ENDIn short: In a CASE statement, if the second WHEN is the ONLY other option possible, just use ELSE. Here's my proof… first load a buttload of data into your tables (this used up about 8Gb in my test DB):
DECLARE @Q BIGINT SET @Q = 1 CREATE TABLE t1 ( col1 BIGINT ) CREATE TABLE t2 ( col1 BIGINT ) WHILE @Q < 1000000 BEGIN INSERT INTO t1 VALUES ( 123456789123456789 ) INSERT INTO t1 VALUES ( 321654987321654987 ) INSERT INTO t1 VALUES ( 987654321987654321 ) INSERT INTO t1 VALUES ( 741852963741852963 ) INSERT INTO t2 VALUES ( 123456789123456789 ) INSERT INTO t2 VALUES ( 321654987321654987 ) INSERT INTO t2 VALUES ( 987654321987654321 ) INSERT INTO t2 VALUES ( 741852963741852963 ) SET @Q = @Q + 1 ENDNow test the difference:
SET STATISTICS TIME ON DECLARE @Q BIGINT , @d BIGINT UPDATE T1 SET col1 = CASE WHEN ( col1 – @d ) > 0 THEN ( col1 – @d ) WHEN ( col1 – @d ) <= 0 THEN 0 END UPDATE T2 SET col1 = CASE WHEN ( col1 – @d ) > 0 THEN ( col1 – @d ) ELSE 0 ENDMy results were as follows (STATISTICS IO for this was identical, by the way): The first update took 11,684ms, and the second took 7,637ms, an improvement of 35%. And yes, every little bit counts.
-Jen McCown, http://www.MidnightDBA.com