Clever Geek Handbook

The text data type does not work where the condition is

I have a stored procedure that returns multiple fields, most of which contain some information about the client, and then 1 more containing an xslfo "blob" in a text data type field. I am trying to streamline the process to ignore records that have no value in that text data type field, but when I add this to the where clause:

And cl.CorrespondenceFO IS NOT NULL
And Convert(varchar(1), cl.CorrespondenceFO) <> ''

Request timeout. I understand that the text data type is deprecated, so I will need to convert this column in the future, but I need to optimize it before that happens. Are there any suggestions on how I can get this stored procedure to return results with these two additional where suggestions are added? TIA

Edit: I updated the datatypes to varchar (max) and tried all the suggestions below and the request is still in sync. Any other suggestions?

+3


source to share


5 answers


I finally got this to work. After making suggestions for converting old text, ntext and figurative data types to varchar (max), nvarchar (max), and varbinary (max), the query was still failing. So here's the fix that worked.

Old version = Select FieldA, FieldB From Table A Where FieldA In ('value1', 'value2') AND Len (FieldB)> 0 is what doesn't work

New version = Create table #temp (FieldA varchar (10), FieldB varchar (max))

Paste into #temp (FieldA, FieldB) Select FieldA, FieldB From Table A Where FieldA In ('value1', 'value2')

Select * From #temp Where Len (FieldB)> 0



Droplet table #temp

(I'm not sure why my formatting isn't working, sorry)

For some reason, the query always timed out when I tried to do Len () validation on the original query (maybe something to do with the table being over 17 million records) so it just creates a subset of the record and then the validation Len () of this subset allowed me to get my records in a timely manner.

Thanks everyone!

0


source


  • Now convert to varchar(max)

  • Correct your data on write by disallowing empty string with CHECK constraint
  • Use only WHERE ... cl.CorrespondenceFO IS NOT NULL

I can't check, but the IS NOT NULL check should use a NULL bitmap



Alternatively use a computed column with LEN (or DATALENGTH for text) and index / filter that.

+4


source


Why are you still using TEXT

? It should be VARCHAR(MAX)

. Instead of asking, you have why not:

WHERE DATALENGTH(cl.CorrespondenceFO) > 0;

(Although I agree with @gbn - you shouldn't allow an empty string if it means the same thing NULL

.)

+3


source


Converting to is VARCHAR

not very efficient, are you sure it's cl.CorrespondenceFO IS NOT NULL

not enough to filter out rows with empty blocks?

If that's really not enough, you can use DATALENGTH

to avoid the conversion:

And cl.CorrespondenceFO IS NOT NULL
And DATALENGTH(cl.CorrespondenceFO) > 0
+1


source


I'm not familiar with SQL Server, but if this text type is similar to the MySQL / PostGres text type, it will be ineffective. These types are stored at the table, so the actual string contains a pointer to the actual text. This means there are a lot of disk queries, the index won't do you much good, etc.

0


source






More articles:


All Articles