Increase the performance of updates by 1000

Following on from my previous post on varchar(max) for which the consensus was that one shouldn't use varchar(max) everywhere, I have found this great function that allows you to update a varchar(max) column with great performance gain.

The function is called Write and is used in an update statement i.e

UPDATE myTable
SET myvarcharmaxcolumn.write('New Text',10,5)

This will remove 5 characters from myvarcharmaxcolumn starting at position 10 and insert "New Text" at this position.

If you don't specifiy an offset then the text is appended to the column.

Firstly this allows text to be inserted into a LOB text column which isn't possible any other way. (STUFF only supoprts 8000 characters),

Secondly it provides a very performant way of appending text. How performant? Very, try the following on my machine the one that uses .write takes 500 ms thats 0.1ms per update, the one using "+" takes 8 minutes 36 seconds. Thats 1000 times faster. Wow

/******************************* varchar(max) Append with Write *****************************************/

declare @t table (v varchar(max))

insert into @t values ('')

declare @i int, @s datetime

set @s = getdate()

set @i = 0

while @i < 5000

begin

update @t set v.write (replicate('A',10),null,null)

set @i = @i + 1

end

select datediff(ms,@s,getdate()), len(v)

from @t

go

/******************************* varchar(max) Append with + *****************************************/

declare @t table (v varchar(max))

insert into @t values ('')

declare @i int, @s datetime

set @s = getdate()

set @i = 0

while @i < 5000

begin

update @t set v = v + replicate('A',10)

set @i = @i + 1

end

select datediff(ms,@s,getdate()), len(v)

from @t

go

 

 

-
Published 02 March 2006 06:51 by simonsabin

Comments

No Comments