SQL Server Performance

Why is one update slower than the other?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by GetUpAah, May 23, 2007.

  1. GetUpAah New Member

    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


  2. GetUpAah New Member

    quote:Originally posted by GetUpAah

    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.

Share This Page