xml模式集合的改变——巨大的性能损失

I'm seeing a huge performance hit in our enterprise application when changing the XML schema collection of a given column on a large table. Simplistically put, we're doing something like this:

当更改大型表上给定列的XML模式集合时，我在企业应用程序中看到了巨大的性能损失。简单地说，我们这样做:

ALTER TABLE HugeTable ALTER COLUMN CustomFields XML 

(note: CustomFields was previously bound to XML(CustomFieldsSchemaCollection, but of course we need to modify that xml schema, so we need this statement so that that schema can be modified)

(注意:CustomFields以前被绑定到XML(CustomFieldsSchemaCollection，但当然我们需要修改该XML模式，因此我们需要这个语句，以便修改该模式)

And then, after modifying the CustomFieldSchemaCollection, we do this:

然后，在修改了CustomFieldSchemaCollection之后，我们这样做:

ALTER TABLE HugeTable ALTER COLUMN CustomFields XML(CustomFieldSchemaCollection)

The first statement takes 8 minutes and the second statement takes 10 minutes.

第一个陈述需要8分钟，第二个陈述需要10分钟。

We found we could slightly optimize the first statement (50% performance boost) by using the following:

我们发现我们可以使用以下方法稍微优化第一个语句(50%性能提升):

ALTER TABLE HugeTable ALTER COLUMN CustomFields nvarchar(max)

The effect is that the first statement takes 4 minutes and the second statement takes 10 (so, 14 min, down from 18).

其效果是，第一个语句需要4分钟，第二个语句需要10分钟(因此，从18分钟减少到14分钟)。

Bottom line question is... Is there a way to do this "xml schema re-binding" (or whatever one calls it) in a way that avoids SQL Server's totally unnecessary and redundant checking of every value in the column? (Note: yes, we can safely assume that the existing XML data in that table will conform to the new xml schema collection.)

底线的问题是……是否有一种方法可以避免SQL Server对列中的每个值进行完全不必要和冗余的检查，从而实现这种“xml模式重新绑定”(或任何调用它的方法)?(注意:是的，我们可以安全地假设该表中的现有XML数据将符合新的XML模式集合。)

Thanks to anyone who can assist!

感谢所有能帮助我的人!

1 个解决方案

#1

If time really is a big issue (which on a 1 time upgrade shouldn't really matter much) could you consider just removing the underlying data, do your re-binding to the new schema, then do a bulk insert turning all the identity insert issues off etc...?

如果时间真的是一个大问题(在第一次升级时不太重要)，您是否可以考虑删除底层数据，重新绑定到新模式，然后进行批量插入，关闭所有身份插入问题等等?

Or for a super stepwise, write a script that does the following as batches:

或者对于一个超级步骤，编写一个脚本，以批处理以下内容:

1. Alter the table and add a new XML column with the new schema
   binding
2. 修改表并使用新的模式绑定添加一个新的XML列。
3. Set the new column data = new old column data
4. 设置新的列数据=新的旧列数据
5. Drop the old column.
6. 把旧的列。
7. Rename the new column to the old column name.
8. 将新列重命名为旧列名。
9. Modify Ordinality if needed (Different Topic... And unless all your consuming queries are written safely by specifying column names instead of relying on the
   underlying ordinality)
10. 如果需要(不同的主题……)而且，除非所有的消费查询都是通过指定列名而不是依赖于底层的顺序来安全地编写的。