Slow "Select" Query with varchar(max)

Question

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

When I perform this query:

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

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)

Then the query retrieves 35 rows in 0 seconds.

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

95 292 for the query retrieving data in 0 sec

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

Do you know does it come from?

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:

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

TDS packets received from server 30 Vs 11000

Potty

if the problem already exists, please send the query plan. – Adolfoadolph

@Potty did you manage to solve this? I'm running in the exact same problem. Only 10 records, 1 column with a nvarchar(max) and some data, but nothing crazy. Takes 3 minutes to do a top 1. – Lexi

Answer 1

Varchar(max) can't be part of a index key and apart from this other major drawback is it cannot be stored internally as a contiguous memory area as they can possibly grow up to 2Gb. So for improve the performance you need to avoid it.

Aquarium

Please add further details to expand on your answer, such as working code or documentation citations. – Laggard

Answer 2

Use Index for ExamId also use select field1,field2,etc instead of select * ....

Communize

Tried it. Doesn't change anything – Potty

Answer 3

I am not sure but try this:

select * from Snapshots where ExamId = (select top 36 ExamId from Snapshots where ExamId = 212)

Bughouse

I tried (and replaced "=" by "in") and it doesn't change anything – Potty

Answer 4

Your execution time should be very low, while fetch is much longer. Remove the varchar(max) from the SELECT TOP statement and only retrieve those values as you specifically need them.

Youthful

Answer 5

Include SET STATISTICS IO ON before running the SELECT query and provide the output. Also, can you post the query plans from the 2 different queries as that will go a long way to explaining what the differences are. You can use https://www.brentozar.com/pastetheplan/ to upload it and provide the links.

Your TOP also does not have a matching ORDER BY so you cannot guarantee the ordering of the first 35 or 36 rows returned. This means that the 35 rows may not all be included in the 36 and you may be returning hugely different volumes of data.

Finally, also try in SSMS to enable Client Statistics with the query - this will show whether the delay is at the server side or all in latency in returning the result set to you.

Overstreet

Answer 6

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

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...

Slotter

Answer 7

As other people are saying you should throw schema (datatype+existing index) of Snapshot table.

In snapshot table i believe examid is non clustered index which is not unique. One examid has many record.Snapshot table must be having any PK column .

Top clause should always be use with Order by clause.Top clause without Order by clause is Non Determinstic. On what basis it will select Top N. So knowing schema of Snapshot then decide correct Index.

Using Order by clause can also be Non Determinstic but this is another discussion.

You can try this,

create  table #temp(PKID int)

insert into #temp(pkid)
select top 36 pkid
from dbo.Snapshots
where ExamId = 212

Then you can do this,

select col1,col2,col3,col4
from dbo.Snapshots S
where exists(select 1 from #temp t where t.pkid=s.pkid)

Now your main question and problem,

Why 35 rows retrieve in 0 seconds and 36 rows retrieve in 3 minute.

I will write thst soon here.Meanwhile I am waiting for complete structure of Snapshot table.

Oao