添加datalength条件使查询变慢。

Question

我有一个包含一些列的表mytable，包括列datekey (它是一个date和一个索引)、一个列contents，它是一个varbinary(max)，以及一个列stringhash，它是一个varchar(100)。stringhash和datekey一起构成表的主键。一切都在我的本地机器上运行。

正在运行

SELECT TOP 1 * FROM mytable where datekey='2012-12-05'

返回0行并花费0秒。但是，如果我添加了一个datalength条件：

SELECT TOP 1 * FROM mytable where datekey='2012-12-05' and datalength(contents)=0

它运行了很长一段时间，在我放弃等待之前没有归还任何东西。

我的问题是：为什么？我如何找出为什么要花这么长时间呢？

到目前为止，我查过的情况如下：

当我单击“显示估计执行计划”时，也需要很长时间，并且在放弃等待之前不返回任何内容。

如果我做了

SELECT TOP 1000 datalength(contents) FROM mytable order by datalength(contents) desc

它需要7秒，并返回一个列表4228081,4218689等。

exec sp_spaceused 'mytable'

返回

rows        reserved     data         index_size  unused
564019      50755752 KB  50705672 KB  42928 KB    7152 KB

所以这张桌子很大，只有50 GB。正在运行

SELECT TOP 1000 * FROM mytable

需要26秒。

sqlservr.exe进程大约是6GB，这是我为数据库设置的限制。

Answer 1

这需要很长时间，因为您的查询需要为每一行计算DATALENGTH，然后在返回第一条记录之前对结果进行排序。如果字段的DATALENGTH (或它是否包含任何值)是您可能重复查询的内容，我建议添加一个索引字段(可能是一个持久化的计算字段)来保存结果，并对其进行搜索。

Answer 2

这个旧的msdn博客文章似乎同意@MartW的回答，即对每一行都对datalength进行评估。但是理解“评估”的真正含义以及性能下降的真正根源是很好的。

正如问题中提到的，列contents中每个值的大小都可能很大。这意味着每一个大于8KB的值都存储在特殊的LOB存储中。因此，考虑到其他列的大小，很明显，表占用的大部分空间都是由这个LOB存储占用的，也就是说它大约是50 it。

即使已经计算了每一行的contents列的长度(在上面的post链接中已经证明了这一点)，它仍然存储在LOB中。因此，引擎仍然需要读取LOB存储的某些部分来执行查询。

如果在执行查询时LOB-存储不在RAM中，那么我们需要从磁盘读取它，这当然比从RAM读取慢得多。另外，LOB-部件的读取可能是随机的，而不是线性的，这甚至更慢，因为它倾向于增加需要从磁盘读取的全部内存块。

Answer 3

目前，它可能不会使用主键，因为在datekey列之前包含了stringhash列。尝试添加一个只包含datekey列的附加索引。一旦创建了该键，如果它仍然很慢，您还可以尝试查询提示，例如：

SELECT TOP 1 * FROM mytable where datekey='2012-12-05' and datalength(contents)=0 WITH INDEX = IX_datekey

还可以在应用程序中或在insert / update触发器中创建更新的单独长度列。