Home Update all or only where oldvalue<>newvalue?

# Update all or only where oldvalue<>newvalue?

George Menoutis
1#
George Menoutis Published in 2018-01-12 08:46:12Z
 I'm using SQL server 2014. Which would run faster, why, and is there a way to use the analyzer to find out: update tablename set flname=replace(flname,'(','') update tablename set flname=replace(flname,'(','') where flname<>replace(flname,'(','')  No indices on flname field. I could create one though. I expect the where would filter out about 90% of rows.
sepupic
2#
 Here is a simple repro that shows that the expression REPLACE (flname ,'(','') will be evaluated twice in case of WHERE flname <> REPLACE (flname ,'(',''), and it's CPU intensive work: select col into dbo.update_str from ( select top 1000 replicate ('a((', 2666) as col from dbo.nums union all select top 1000 replicate ('a', 8000) from sys.all_columns )t set statistics io, time on update dbo.update_str set col = replace (col ,'(','') --CPU time = 12278 ms, elapsed time = 12299 ms. drop table dbo.update_str select col into dbo.update_str from ( select top 1000 replicate ('a((', 2666) as col from dbo.nums union all select top 1000 replicate ('a', 8000) from sys.all_columns )t update dbo.update_str set col = replace (col ,'(','') where col <> replace (col ,'(','') --CPU time = 23946 ms, elapsed time = 24007 ms.  In my test I fill in the table dbo.update_str 2 times beacuse if it's filled only once the second update has nothing to do. Double work in the second case san be found out in the execution plan: while both plans have COMPUTE SCALAR after a scan (to calculate a value for UPDATE), the second has the same expression as a predicate of table scan. So in my test case where the half of data has '(' in it, the time difference is 2 times on 2000 rows (of course my rows are long enough to show the worst possible case), the second update is 2 time slower because the rows have many characters to replace.
 In my test it does not matter. You can test as follows: SELECT TOP 10000 flname INTO #TEST FROM tablename SET STATISTICS IO, TIME ON UPDATE #TEST SET flname = REPLACE (flname ,'(','') UPDATE #TEST SET flname = REPLACE (flname ,'(','') WHERE flname <> REPLACE (flname ,'(','')