问题描述
过去，我注意到查询 varbinary(max) 列时性能很差.可以理解，但在检查它是否为空时似乎也会发生这种情况，我希望引擎会采取一些捷径.
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
- 需要提取整个二进制文件，并且
- 未编入索引(varbinary 不能成为普通索引的一部分)
这个问题似乎不同意我在这里缓慢的前提，但我似乎一次又一次地遇到二进制字段的性能问题.
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?
推荐答案
我认为这很慢，因为 varbinary 列没有(也不能)被索引.因此，您使用计算(和索引)列的方法是有效的.
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.
但是，我会使用 ISNULL(DATALENGTH(Content), -1) 代替，以便您可以区分长度 0 和 NULL.或者只使用 DATALENGTH(Content).我的意思是，Microsoft SQL Server 不是 Oracle，其中空字符串与 NULL 相同.
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.
这篇关于检查 varbinary 字段上的 ISNULL 的策略?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！