Home Update all or only where oldvalue<>newvalue?
Reply: 2

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#
sepupic Reply to 2018-01-12 09:52:06Z

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.

SQL_M
3#
SQL_M Reply to 2018-01-12 08:55:29Z

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 ,'(','')
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.300247 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO