RowVersion 和性能 [英] RowVersion and Performance

问题描述

我想知道在 Sql-Server 数据库的表上添加 rowversion 列是否对性能有影响?

I was wondering if there was an performance implications of adding a rowversion column on a table in a Sql-Server database?

推荐答案

几乎没有性能影响，rowversion 只是旧时间戳数据类型的新名称.所以你的数据库需要存储额外的二进制字段.当您尝试对这些数据进行查询时，您的性能会受到更大的影响，例如:

There are few performance implications, rowversion is just a new name for the old timestamp datatype. So your database will need to store the additional binary field. Your performance will suffer much more when you try to do queries on this data such as:

SELECT *
FROM MyTable
WHERE rowVersion > @rowVersion

这是可用于获取自上次@rowVersion 以来更新项目列表的常用方法.这看起来不错，并且非常适合包含 10,000 行的表.但是当您达到 100 万行时，您会很快发现它一直在执行表扫描，而您的性能下降是因为您的表现在不再完全适合服务器的 RAM.

Which is the common way that can be used to get the list of updated items since last @rowVersion. This looks fine and will work perfect for a table with say 10,000 rows. But when you get to 1M rows you will quickly discover that it has always been doing a tablescan and your performance hit is because your table now no longer fits entirely within the RAM of the server.

这是rowVersion 列遇到的常见问题，它不会自己神奇地索引.此外，当您索引 rowVersion 列时，您必须接受索引通常会随着时间的推移变得非常碎片化，因为新的更新值始终位于索引的底部，在更新现有项目时会在整个索引中留下间隙.

This is the common problem that is encountered with the rowVersion column, it is not magically indexed on it's own. Also, when you index a rowVersion column you have to accept that the index will often get very fragmented over time, because the new updated values are always going to be at the bottom of the index, leaving gaps throughout the index as you update existing items.

如果您不打算使用 rowVersion 字段来检查更新的项目，而是将使用它来保持一致性，以确保自上次以来记录未更新阅读，那么这将是一个完全可以接受的用途，不会产生影响.

If you're not going use the rowVersion field for checking for updated items and instead you're going to use it for consistency to ensure that the record isn't updated since you last read, then this is going to be a perfectly acceptable use and will not impact.

UPDATE MyTable SET MyField = ' @myField
WHERE Key = @key AND rowVersion = @rowVersion

这篇关于RowVersion 和性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！