A silly SQL Performance test

A question came up on the relative performance of the old Blobs (Image, Text, NText) and the newer nvarchar(max) and nvarbinary(max). It seemed a moot question since the blobs are depreciated in SQL Server 2008 and may not see the light of 2011. The question of relative performance came up and that was an interesting problem to try to assess natively in SQL Server. I like interesting questions. Some Bingoogling found nothing on the web evaluating actual performance between the two – so an experiment was in order.

 

First I created a simple table:

CREATE TABLE [dbo].[PerfTest](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [FieldA] [nvarchar](max) NULL,
  [FieldB] [ntext] NULL,
  [FieldC] [image] NULL,
  [FieldD] [text] NULL,
  [FieldE] [varchar](max) NULL,
  [FieldF] [varbinary](max) NULL

 

Then I grabbed a 500K row table and inserted data into FieldE. Once inserted, I updated all of the other fields from it – so all fields contain “equivalent” data.  For performance, I did the weakest test possible, but one that applies to all of the data type uniformly… i.e.

 

Print @@CPU_BUSY
Select count(1) from perfTest where FieldA is null
Print @@CPU_BUSY
Select count(1) from perfTest where FieldB is null
Print @@CPU_BUSY
Select count(1) from perfTest where FieldC is null
Print @@CPU_BUSY
Select count(1) from perfTest where FieldD is null
Print @@CPU_BUSY
Select count(1) from perfTest where FieldE is null
Print @@CPU_BUSY
Select count(1) from perfTest where FieldF is null
Print @@CPU_BUSY

From the output, I could calculate the cpu time that each took.  The results are shown below:

DataType	Pass 1	Pass 2	Pass 3	Pass 4
nvarchar(max)	18	17	17	16
ntext	170	64	60	61
Image	62	63	67	60
text	66	62	64	63
varchar(max)	15	15	15	17
varbinary			16	17

 

It is clear that the blobs take FOUR TIMES the @@CPU_BUSY that the newer data types took. Definitely, it’s time to have a good Irish Wake (with Jamieson, Baileys and Guinness ) when the Blobs are actually dropped. IMHO, the test is interesting because the issue of mechanism for retrieving the data was side stepped nicely.

 

That’s it – if you are maintaining a legacy system using BLOBS, feel free to use the above to argue that there is a need to update the code.

 

Addendum

The following was also executed to evaluate retrieval time:

Print @@CPU_BUSY
Select count(1) from perfTest where LEN(CAST(FieldA AS NVARCHAR(MAX))) < 12
Print @@CPU_BUSY
Select count(1) from perfTest where LEN(CAST(Fieldb AS NVARCHAR(MAX))) < 12
Print @@CPU_BUSY
Select count(1) from perfTest where LEN(CAST(Fieldd AS NVARCHAR(MAX))) < 12
Print @@CPU_BUSY
Select count(1) from perfTest where LEN(CAST(Fielde AS NVARCHAR(MAX))) < 12
Print @@CPU_BUSY

With the following results:

DataType	Pass 1	Pass 2
nvarchar(max)	21	16
ntext	106	103
text	122	128
varchar(max)	46	46

 

In this we are retrieving the data and it’s interesting to note that converting from varchar to nvarchar was approximately the same difference as betwen text and ntext (25-35).  What was interesting to observe was that instead of being four times slower, blogs became five time slower.