SQL Server performance deterioration

Question

I'm currently dealing with performance/memory consumption optimizations of our application. One of the tasks to perform is to replace all blobs in the a table that correspond to empty arrays with null values; this should reduce db size, memory consumption and speed up the load. Here is the table definition:

CREATE TABLE [dbo].[SampleTable](
    [id] [bigint] NOT NULL,
    [creationTime] [datetime] NULL,
    [binaryData] [image] NULL,
    [isEvent] [bit] NULL,
    [lastSavedTime] [datetime] NULL,
 CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

I updated the table and replaced image field values (binaryData) with NULL values where appropriate (data corresponding to empty arrays in the application). Now, I observe the performance deterioration when running trivial SELECT * FROM SampleTable.

Originally those fields that had been updated had length = 512 bytes, not sure if it matters, though.

Any ideas why selecting blobs containing NULL values takes longer than selecting real binary data even if the data is the same for different rows?

Answer 1

Let me help you restate this:

You have sql server doing a table scan while testing every.single.record. for a null value on the one side, versus the other where you have sql server doing a massive dump of ALL the records...

If your blobs are relatively small, then it's pretty obvious which one would be faster...

Answer 2

I don't know the answer to this question. I tried the following test though and got a result that I found surprising.

CREATE TABLE [dbo].[SampleTable](
    [id] [BIGINT] NOT NULL,
    [creationTime] [DATETIME] NULL,
    [binaryData] [IMAGE] NULL,
    [isEvent] [BIT] NULL,
    [lastSavedTime] [DATETIME] NULL,
 CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)
) 

INSERT INTO [dbo].[SampleTable]
SELECT 1, GETDATE(),
0x1111,
1, GETDATE()

INSERT INTO [dbo].[SampleTable]
SELECT 2, GETDATE(),
0x2222,
2, GETDATE()

INSERT INTO [dbo].[SampleTable]
SELECT 3, GETDATE(),
NULL,
3, GETDATE()


UPDATE [dbo].[SampleTable] SET [binaryData] = NULL 
WHERE [id]=2

Looking at this in SQL Internals Viewer I was surprised to see a difference between the row I inserted as NULL and the one I updated to NULL.

It looks as though even when the value is updated to NULL it doesn't just set the NULL bitmap for some reason and still needs to follow a pointer to another LOB_DATA page.

Inserted as NULL

Updated to NULL