SQL Server，将 NTEXT 转换为 NVARCHAR(MAX) [英] SQL Server, Converting NTEXT to NVARCHAR(MAX)

问题描述

我有一个包含大量当前 NTEXT 字段的数据库.

I have a database with a large number of fields that are currently NTEXT.

升级到 SQL 2005 后，我们运行了一些性能测试，将它们转换为 NVARCHAR(MAX).

Having upgraded to SQL 2005 we have run some performance tests on converting these to NVARCHAR(MAX).

如果您阅读本文:

http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx

这说明简单的 ALTER COLUMN 不会将数据重新组织成行.

This explains that a simple ALTER COLUMN does not re-organise the data into rows.

我通过我的数据体验到了这一点.如果我们只运行 ALTER COLUMN，我们实际上在某些方面的性能要差得多.但是，如果我为所有这些字段运行 UPDATE Table SET Column = Column ，我们就会获得极其巨大的性能提升.

I experience this with my data. We actually have much worse performance in some areas if we just run the ALTER COLUMN. However, if I run an UPDATE Table SET Column = Column for all of these fields we then get an extremely huge performance increase.

我遇到的问题是数据库由数百个这样的列组成，其中包含数百万条记录.一个简单的测试(在低性能虚拟机上)有一个包含单个 NTEXT 列的表，其中包含 700 万条记录，需要 5 小时才能更新.

The problem I have is that the database consists of hundreds of these columns with millions of records. A simple test (on a low performance virtual machine) had a table with a single NTEXT column containing 7 million records took 5 hours to update.

有人可以就我如何以更有效的方式更新数据以最大限度地减少停机时间和锁定提供任何建议吗?

Can anybody offer any suggestions as to how I can update the data in a more efficient way that minimises downtime and locks?

我的备份解决方案是随着时间的推移以块为单位更新数据，但是，使用我们的数据会导致性能更差，直到所有记录都已更新，而且时间越短越好，所以我仍在寻找以便更快地更新.

My backup solution is to just update the data in blocks over time, however, with our data this results in worse performance until all the records have been updated and the shorter this time is the better so I'm still looking for a quicker way to update.

推荐答案

如果您无法获得预定的停机时间....

If you can't get scheduled downtime....

创建两个新列:nvarchar(最大)处理标志 INT DEFAULT 0

create two new columns: nvarchar(max) processedflag INT DEFAULT 0

在processedflag上创建非聚集索引

Create a nonclustered index on the processedflag

您有可用的 UPDATE TOP(您想更新按主键排序的 top).

You have UPDATE TOP available to you (you want to update top ordered by the primary key).

只需在更新期间将processedflag设置为1，以便下次更新只会更新processed flag仍然为0的地方

Simply set the processedflag to 1 during the update so that the next update will only update where the processed flag is still 0

更新后可以使用@@rowcount 看看是否可以退出循环.

You can use @@rowcount after the update to see if you can exit a loop.

我建议在每次更新查询后使用 WAITFOR 几秒钟，让其他查询有机会获取表上的锁，而不是使磁盘使用过载.

I suggest using WAITFOR for a few seconds after each update query to give other queries a chance to acquire locks on the table and not to overload disk usage.

这篇关于SQL Server，将 NTEXT 转换为 NVARCHAR(MAX)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！