慢速“选择"使用 varchar(max) 查询 [英] Slow "Select" Query with varchar(max)

问题描述

我有一个有 500 行的小桌子.该表有 10 列，其中包括一个 varchar(max) 列.

I have a small table with 500 rows. This table has 10 columns including one varchar(max) column.

当我执行此查询时:

SELECT TOP 36 *
FROM MyTable
WHERE (Column1 = Value1)

它在 3 分钟内检索了大约 36 行.varchar(max) 列每行包含 3000 个字符.

It retrieves around 36 rows in 3 minutes. The varchar(max) columns contains in each row 3000 characters.

如果我尝试只检索少一行:

If I try to retrieve only one row less:

SELECT TOP 35 *
FROM MyTable
WHERE (Column1 = Value1)

然后查询在 0 秒内检索到 35 行.

Then the query retrieves 35 rows in 0 seconds.

在我的客户端统计数据中，从服务器收到的字节数，我有:

In my clients statistics, Bytes received from server, I have:

95 292 用于在 0 秒内检索数据的查询

95 292 for the query retrieving data in 0 sec

查询在 3 分钟内检索数据超过 200 000 000

over 200 000 000 for the query retrieving data in 3 min

你知道它来自哪里吗?

编辑---这是我的真实代码:

EDIT --- Here is my real code:

select top 36 *
from Snapshots
where ExamId = 212

select top 35 *
from Snapshots
where ExamId = 212

编辑 --- 有关客户统计数据的更多信息

EDIT --- More info on clients statistics

差异巨大的两个统计数据是:

The two statistics having a huge variation are:

从服务器收到的字节数:66 038 Vs 超过 2 000 000

Bytes received from server : 66 038 Vs More than 2 000 000

从服务器收到的 TDS 数据包 30 Vs 11000

TDS packets received from server 30 Vs 11000

推荐答案

没有完整的表描述作为 DDL 语句(CREATE TABLE...)和索引，很难回答.

Without the complete table description as a DDL statement (CREATE TABLE...) and indexes, it is very difficult to answer.

一个重要的问题是:您是否使用指令"?创建表时 TEXTIMAGE_ON ?这会将 LOB 存储与关系数据分开，以避免行溢出存储...

One important question is: do you use the "directive" TEXTIMAGE_ON when creating your table ? This will separate LOBs storage from relational data to avoid row overflow storage...

这篇关于慢速“选择"使用 varchar(max) 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！