SQL Server Performance Forum – Threads Archive

Why is one update slower than the other?

Can anybody tell me why sql statement 1 is about 7 times slower than
sql statement 2? sql statement 1:
declare
@count int,
@starttime datetime,
@endtime datetime
set @count = 0
set @starttime = getDate()
while @count < 1000
begin
update customers
set companyname = ‘Alfreds Futterkiste’ + cast(@count as varchar)
where customerid = ‘ALFKI’
set @count = @count + 1
end
set @endtime = getDate()
select datediff(ms,@starttime,@endtime)
sql statement 2:
declare
@count int,
@starttime datetime,
@endtime datetime,
@tmp varchar
set @count = 0
set @starttime = getDate()
while @count < 1000
begin
set @tmp = ‘Alfreds Futterkiste’ + cast(@count as varchar)
update customers
set companyname = @tmp
where customerid = ‘ALFKI’
set @count = @count + 1
end
set @endtime = getDate()
select datediff(ms,@starttime,@endtime)
I run the sql in the Northwind database.
Ulrik

---

I found out. I declare the @tmp variable as a varchar in statement 2
(which mean that the field is only update to ‘A’). If I change this to
nvarchar(40) (which is the datatype for company) statement 2 takes the
same amount of time as statement 1. Pretty stupid of me.

---

]]>