Query over text column takes ages to run

Question

I'm not used to querying text columns with a large amount of data as I've always used NVARCHAR(MAX) and VARCHAR(MAX) but this is not my Database that I'm querying.

myTable has 305,000 rows.

myDescriptionField is a text column and allows nulls & has loads of data in it.

When I run the following query, it returns a row instantly.

SELECT TOP 1 userid
FROM myTable 
WHERE ISNULL(userID,0) > 0 
AND TRIM(SUBSTRING(myDescriptionField,1,1)) is not null

However, when I add this line to the where clause, the query just sits for over a minute (not sure how long it actually takes to finish as I always end up stopping the execution).

AND myDescriptionField NOT LIKE ''

I thought it might be because I'm forced to do a not like as it's a text column and also that it may be where a lot of rows have blanks or null so I tried different combinations of the following :-

AND ISNULL(TRIM(SUBSTRING(myDescriptionField,1,1)),'') <> ''

None of this worked.

So now a quick explanation as to what I need to achieve :-

I just need to find out if at least one row has data in the column myDescriptionField and if so, I can move on to the next table.

Is there a quick way of returning just one row once it's hit one?

Answer 1

Can you use DATALENGTH?

SELECT TOP 1
    userID
FROM
    myTable 
WHERE
    DATALENGTH(myDescriptionField) >= 1

Answer 2

Not sure if you added another predicate, or if you changed the existing one, but you could try like:

SELECT TOP 1 userid
FROM myTable 
WHERE ISNULL(userID,0) > 0 
  AND NULLIF(SUBSTRING(myDescriptionField,1,1),'') is not null;