Count(*) with NText column is very very very slow. Why is it so slow? And how can I improve performance?

I've got some problems with my SQL Server 2005 database. I've got a table with order lines and every line has an NTEXT field named XmlData. I want to count all the fields that have no information stored in them. I'm using this query:

SELECT Count(*) FROM [OrderLine] WITH(NOLOCK) 
WHERE [XmlData] NOT LIKE '' AND [XmlData] IS NOT NULL 

The table has 230.314 of records and this counting query it takes several minutes. Do any of you guys have a clue as how to in开发者_运维问答crease performance?

Note: I can't change to type of the column to nvarchar(max) type. The NOLOCK was a tip of a colleague.

I'm looking forward to some tips and explanations.

To avoid the expensive LOB reads, replace [XmlData] NOT LIKE '' with DATALENGTH([XmlData])>0 - DATALENGTH doesn't need to read in the LOB value of each row.

Also, as others have suggested: use nvarchar(max) instead of ntext, if possible.

NTEXT is deprecated, use nvarchar(max) instead (consider xml ...).

If you change your column type you can create an index on the column, thus creating statistics for the column will help SQL choose the best way to use this index.

Create Statistics for the XMLData column, as this creates a map of values that will increase the of the count type of queries significantly.

CREATE STATISTICS STATOrderLineXmlData
ON OrderLine (XmlData)
WITH FULLSCAN

Based on @Pent's answer you should change your query for this one: Replace the query for this:

SELECT Count(*) 
FROM [OrderLine] WITH(NOLOCK) 
WHERE [XmlData] IS NOT NULL 
AND DATALENGTH([XmlData]) > 0

Check this Link if you're changing the column type for nvarchar(max). The Link has info around a little tricky update you have to do to gain performance after the column change.

First of all, this query is going to involve a table scan. That is going to be slow with 230K rows. You can try replacing NOT LIKE with Length(XmlData) = 0, but I don't think that is going to help much. On the other hand, I'm not sure the Length function works with NText data types. I don't think it does, now that I think about it.

The bottom line is that table scans are slow, and dealing with NText data types is slow. So you have a bad combination here. I don't see much room for improvement here, unless the data type can be changed.

And, I'm sure you probably realize that using WITH NOLOCK runs the risk of reading dirty data? Yes it can help performance, but it's not free. You might be reading uncommitted changes.