21

In the past I've noted terrible performance when querying a varbinary(max) column. Understandable, but it also seems to happen when checking if it's null or not, and I was hoping the engine would instead take some shortcuts. 

select top 100 * from Files where Content is null

I would suspect that it's slow because it's

  1. Needing to pull the whole binary out, and
  2. It's not indexed (varbinary can't be part of a normal index)

This question seems to disagree with my premise of slowness here, but I seem to have performance problems with binary fields time and time again.

One possible solution I thought of is to make a computed column that is indexed:

alter table Files
add ContentLength as ISNULL(DATALENGTH(Content),0) persisted

CREATE NONCLUSTERED INDEX [IX_Files_ContentLength] ON [dbo].[Files] 
(
    [ContentLength] ASC
)

select top 100 * from Files where ContentLength = 0

Is that a valid strategy? What other ways are there to efficiently query when binary fields are involved?

Community
  • 1
  • 1
Gavin
  • 9,855
  • 7
  • 49
  • 61

2 Answers2

17

I think it's slow because the varbinary column is not (and can't be) indexed. Therefore, your approach to use a computed (and indexed) column is valid.

However, I would use ISNULL(DATALENGTH(Content), -1) instead, so that you can distinguish between length 0 and NULL. Or just use DATALENGTH(Content). I mean, Microsoft SQL Server is not Oracle where an empty string is the same as NULL.

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132
4

We had a similar problem when looking for rows where a varbinary value was not null. For us the solution was to update the statistics for the database:

exec sp_updatestats

After doing this the queries ran much faster.

Geoff Hardy
  • 331
  • 3
  • 9
  • Thanks for posting this suggestion. It turned my 4min+ runaway query into a 1sec query. – Plate Sep 18 '19 at 20:19