Strategies for checking ISNULL on varbinary fields?
Solution 1
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.
Solution 2
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.
Gavin
I work for a small company in Adelaide, Australia. You wouldn't think our software is all that interesting - we provide software for companies who test soil, asphalt, concrete and so on - yet I love the job and the company.
Updated on June 06, 2022Comments
-
Gavin about 2 years
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
- Needing to pull the whole binary out, and
- 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?
-
Plate almost 5 yearsThanks for posting this suggestion. It turned my 4min+ runaway query into a 1sec query.