Using .WRITE on VARCHAR(MAX)? – Part 2


I’ve been delving into the .WRITE operator a bit more, and in particular it’s more complex execution plan when replacing data. Here’s the setup code for the tests I’ve been running:

SET NOCOUNT ON
USE tempdb
GO

— CONFIGURE THE NUMS TABLE
IF OBJECT_ID(‘dbo.Nums’) IS NOT NULL
DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 100000;
SET @rc = 1;

INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END

INSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GO

— CONFIGURE THE SAMPLE TABLE
IF OBJECT_ID(‘dbo.Sample’) IS NOT NULL
DROP TABLE dbo.Sample;
GO
CREATE TABLE dbo.Sample(id INT PRIMARY KEY, data VARCHAR(MAX));
DECLARE @String VARCHAR(MAX);
SET @String = ”;
WHILE LEN(@String) < 50000
BEGIN
SET @String = @string + REPLICATE(‘Lorem ipsum. ‘, 1500);
END

INSERT INTO Sample (id, data)
SELECT n, @string
FROM dbo.Nums
WHERE n < 10000;
GO

I think this represents a plausible real life situation. A table with a few thousand rows (9999), a primary key which we will use in our SARGS and a VARCHAR(MAX) field that we will update. A quick detour: I’ve seen a post on the net from March 06 where a person puts together some code and compares + to .WRITE. The .WRITE code is apparently 1000 times faster. Here is his sample:

Using .WRITE

declare @t table (v varchar(max))
insert into @t values (”)
declare @i int
set @i = 0
while @i < 5000
begin
update @t set v.write (replicate(‘A’,10),null,null)
set @i = @i + 1
end

Using +

declare @t table (v varchar(max))
insert into @t values (”)
declare @i int
set @i = 0
while @i < 5000
begin
update @t set v = v + replicate(‘A’,10)
set @i = @i + 1
end

I have two issues with this sample,

  1. He states that the .WRITE operation takes half a second, and the + operation takes over 18minutes. On my machine (3.6Ghz, 3Gb) the .WRITE also takes half a second, but the + operation takes 3 seconds (which is nowhere near 18minutes!).
  2. Have you ever seen this scenario in the real world? Appending the same string to the same column 5000 times! I doubt it! I think it’s important to try keep samples, especially performance tuning, as close to real life scenarios as possible.

Back to the .WRITE investigation. In Part 1 I showed you the Execution Plans, lets now take a look at the SHOW_PLANS

UPDATE Sample SET data = REPLICATE(‘Lorem ipsum. ‘, 200) WHERE id < 4999;
|–Clustered Index Update(OBJECT:([tempdb].[dbo].[Sample].[PK__Sample__1DE57479]), SET:([tempdb].[dbo].[Sample…

UPDATE Sample SET data.WRITE (REPLICATE(‘Lorem ipsum. ‘, 200), 0, NULL) WHERE id < 4999;
|–Clustered Index Update(OBJECT:([tempdb].[dbo].[Sample].[PK__Sample__1DE57479]), SET:(PartialUpdate([tempdb].[dbo].[…
|–Assert(WHERE:([Expr1007]))
|–Top(ROWCOUNT est 0)
|–Compute Scalar(DEFINE:([Expr1007]=CASE WHEN [tempdb].[dbo].[Sample].[data] IS NULL THEN (0) ELSE NULL END))
|–Clustered Index Seek(OBJECT:([tempdb].[dbo].[Sample].[PK__Sample__1DE57479]), SEEK:([tempdb].[dbo].[Sample].[id] < (4999)) ORDERED FORWARD)

The classic operation appears to do everything in one operation, the Clustered Index Update. The .WRITE operator needs five operations though. First it does a Clustered Index Seek using the SARG we provided. Then it computes a scalar that is stored in Expr1007 and used in the later Assert operation. After the scalar is computed, it runs a TOP, then the Assert and finally a PartialUpdate is run on the Clustered Index. This all seems a bit much considering that the classic operation can do everything in one step.

I’ve sent out a few email for comment on this. If you have any feedback, feel free to leave a comment below.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s