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:
我的SQL Server 2005数据库遇到了一些问题。我有一个带有订单行的表，每行都有一个名为XmlData的NTEXT字段。我想计算所有没有信息存储在其中的字段。我正在使用此查询：
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 increase performance?
该表有230.314条记录，这个计数查询需要几分钟。你们中的任何人都有如何提高性能的线索吗？
Note: I can't change to type of the column to nvarchar(max) type. The NOLOCK was a tip of a colleague.
注意：我无法将列的类型更改为nvarchar（max）类型。 NOLOCK是一位同事的小费。
I'm looking forward to some tips and explanations.
我期待着一些提示和解释。
3 个解决方案
#1
3
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.
为了避免昂贵的LOB读取，用DATALENGTH（[XmlData]）> 0替换[XmlData] NOT LIKE'' - DATALENGTH不需要读入每行的LOB值。
Also, as others have suggested: use nvarchar(max) instead of ntext, if possible.
另外，正如其他人所建议的那样：如果可能的话，使用nvarchar（max）而不是ntext。
#2
2
NTEXT is deprecated, use nvarchar(max) instead (consider xml ...).
不推荐使用NTEXT，而是使用nvarchar（max）（考虑使用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.
如果更改列类型，则可以在列上创建索引，因此为列创建统计信息将有助于SQL选择使用此索引的最佳方法。
Create Statistics for the XMLData column, as this creates a map of values that will increase the of the count type of queries significantly.
为XMLData列创建统计信息，因为这会创建一个值映射，这将显着增加查询的计数类型。
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:
基于@ Pent的答案，您应该更改此查询：替换查询：
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.
如果要更改nvarchar（max）的列类型，请检查此链接。链接有关于在列更改后您需要做的一些棘手的更新信息。
#3
1
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.
首先，此查询将涉及表扫描。这将是230K行的缓慢。您可以尝试用长度（XmlData）= 0替换NOT LIKE，但我认为这不会有太大帮助。另一方面，我不确定Length函数是否适用于NText数据类型。我不这么认为，现在我想起来了。
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.
底线是表扫描很慢，处理NText数据类型很慢。所以你在这里有一个糟糕的组合。除非可以更改数据类型，否则我认为这里没有太大的改进空间。
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.
而且，我相信您可能会意识到使用WITH NOLOCK存在读取脏数据的风险？是的它可以帮助表现，但它不是免费的。您可能正在阅读未提交的更改。
#1
3
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.
为了避免昂贵的LOB读取，用DATALENGTH（[XmlData]）> 0替换[XmlData] NOT LIKE'' - DATALENGTH不需要读入每行的LOB值。
Also, as others have suggested: use nvarchar(max) instead of ntext, if possible.
另外，正如其他人所建议的那样：如果可能的话，使用nvarchar（max）而不是ntext。
#2
2
NTEXT is deprecated, use nvarchar(max) instead (consider xml ...).
不推荐使用NTEXT，而是使用nvarchar（max）（考虑使用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.
如果更改列类型，则可以在列上创建索引，因此为列创建统计信息将有助于SQL选择使用此索引的最佳方法。
Create Statistics for the XMLData column, as this creates a map of values that will increase the of the count type of queries significantly.
为XMLData列创建统计信息，因为这会创建一个值映射，这将显着增加查询的计数类型。
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:
基于@ Pent的答案，您应该更改此查询：替换查询：
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.
如果要更改nvarchar（max）的列类型，请检查此链接。链接有关于在列更改后您需要做的一些棘手的更新信息。
#3
1
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.
首先，此查询将涉及表扫描。这将是230K行的缓慢。您可以尝试用长度（XmlData）= 0替换NOT LIKE，但我认为这不会有太大帮助。另一方面，我不确定Length函数是否适用于NText数据类型。我不这么认为，现在我想起来了。
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.
底线是表扫描很慢，处理NText数据类型很慢。所以你在这里有一个糟糕的组合。除非可以更改数据类型，否则我认为这里没有太大的改进空间。
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.
而且，我相信您可能会意识到使用WITH NOLOCK存在读取脏数据的风险？是的它可以帮助表现，但它不是免费的。您可能正在阅读未提交的更改。