rowversion/timestamp 是否会显着影响性能?

发布于 2024-12-02 06:13:54 字数 111 浏览 0 评论 0原文

我计划添加到数据库行版本中的大多数表以跟踪这些表中的更改。我知道添加它会影响查询的性能。

有谁知道它是否会稍微影响性能(慢几个百分点),或者我不应该向许多表添加行版本,因为它会使数据库慢得多。

I plan to add to most tables in my DB rowversion to track changes in those tables. I know that adding it will affect performance of queries.

Does anyone knows if it affect performance a little bit (few percent slower) or I should not to add rowversion to many tables, because it make DB much slower.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

凉城凉梦凉人心 2024-12-09 06:13:54

仅添加 rowversion/timestamp 列的性能差异在于您的行现在宽了 8 个字节。

当您开始实际使用它们时,实际的性能差异就会出现。但正如我在回答类似问题时指出的那样: RowVersion 和 Performance

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

如:

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

因此,仅检查行以确保自应用程序上次读取以来未更新该行时的性能将是微不足道的性能差异(无论如何它都必须读取该行来更新它)。

但是,当尝试使用 rowversion/timestamp 列作为获取自上次检查以来所有更新项目的方法时,性能将非常差。

Performance difference of just adding a rowversion/timestamp column is that your rows are now 8 bytes wider.

The actual performance difference comes when you start actually using them for something. But as I point out in my answer to a similar question: RowVersion and Performance

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.

Such As:

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

So performance when just checking the row to make sure that it's not been updated since the application last read is going to be trivial performance difference (it has to read the row to update it anyway).

But performance when trying to use a rowversion/timestamp column as a means to get all updated items since last time we checked is going to be very poor.

烟沫凡尘 2024-12-09 06:13:54

我感兴趣的是,我们使用的版本控制是由触发器填充的,所以当我看到这个时,我必须了解更多有关性能的信息。因此,我开始设置一个测试场景。我想将我们当前使用的(触发器)与 rowversion 列与另一个没有版本控制的表进行比较。

毫不奇怪,触发器的性能肯定比 rowversion 差。 Rowversion 本质上与没有版本控制的表上的更新时间相同;一些运行显示没有版本控制的表更快,但大约相同的 # show rowversion 更快。对我来说,这意味着使用它的开销非常小,随机 CPU 和磁盘 I/O 隐藏了真正的性能差异。

SET NOCOUNT ON
GO

CREATE TABLE _TEST_BaseTest(myKey bigint PRIMARY KEY,myValue bigint,UselessColumn bigint)
CREATE TABLE _TEST_RowVersionTest(myKey bigint PRIMARY KEY,myValue bigint, RV rowversion)
CREATE TABLE _TEST_ModifiedVersionTest(myKey bigint PRIMARY KEY,myValue bigint, MV bigint)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER dbo._TEST_ModifiedVersionTest_Trigger
   ON  dbo._TEST_ModifiedVersionTest
   AFTER UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    UPDATE tbl
    SET tbl.MV=tbl.MV+1
    FROM _TEST_ModifiedVersionTest tbl
    INNER JOIN inserted i on i.myKey=tbl.myKey
END
GO

INSERT INTO _TEST_BaseTest (myKey,myValue)
    SELECT TOP 50000 <FIELD1>,<FIELD2> FROM <SOME_TABLE>

INSERT INTO _TEST_RowVersionTest (myKey,myValue)
    SELECT myKey,myValue
    FROM _TEST_BaseTest

INSERT INTO _TEST_ModifiedVersionTest (myKey,myValue,MV)
    SELECT myKey,myValue,1
    FROM _TEST_BaseTest

DECLARE     @StartTimeBase DATETIME, @EndTimeBase DATETIME
        ,   @StartTimeRV DATETIME, @EndTimeRV DATETIME
        ,   @StartTimeMV DATETIME, @EndTimeMV DATETIME
        ,   @CNT INT=0, @Iterations INT=25

--BASE
    SET @StartTimeBase=GETDATE()
    SET @CNT=1
    WHILE @CNT<=@Iterations
    BEGIN
        UPDATE _TEST_RowVersionTest SET myValue=myValue

        SET @CNT=@CNT+1
    END
    SET @EndTimeBase=GETDATE()

--RV
    SET @StartTimeRV=GETDATE()
    SET @CNT=1
    WHILE @CNT<=@Iterations
    BEGIN
        UPDATE _TEST_RowVersionTest SET myValue=myValue

        SET @CNT=@CNT+1
    END
    SET @EndTimeRV=GETDATE()

--MV
    SET @StartTimeMV=GETDATE()
    SET @CNT=1
    WHILE @CNT<=@Iterations
    BEGIN
        UPDATE _TEST_ModifiedVersionTest SET myValue=myValue

        SET @CNT=@CNT+1
    END
    SET @EndTimeMV=GETDATE()

DECLARE @Rows INT
SELECT @Rows=COUNT(*) FROM _TEST_BaseTest

PRINT CONVERT(VARCHAR,@Rows) + ' rows updated ' + CONVERT(VARCHAR,@Iterations) + ' time(s)'
PRINT CONVERT(VARCHAR,DATEDIFF(MS,@StartTimeBase,@EndTimeBase)) + ' Base Time Elapsed (ms)'
PRINT CONVERT(VARCHAR,DATEDIFF(MS,@StartTimeRV,@EndTimeRV))     + ' Rv Time Elapsed (ms)'
PRINT CONVERT(VARCHAR,DATEDIFF(MS,@StartTimeMV,@EndTimeMV))     + ' Mv Time Elapsed (ms)'

drop TABLE _TEST_BaseTest
drop TABLE _TEST_RowVersionTest
drop table _TEST_ModifiedVersionTest

Of interest to me, the versioning we use is populated by trigger, so when I saw this I had to learn more about the performance. So, I set about setting up a test scenario. I wanted to compare what we currently use (trigger) versus a rowversion column versus another table with no versioning.

Not surprisingly, the trigger definitely performed worse than the rowversion. Rowversion was essentially identical to the update time on a table with no versioning; Some runs show the table with no versioning is faster, but about an equal # show rowversion to be faster. To me this means that there is so little overhead in using it, that random CPU and disk I/O hide the real performance difference.

SET NOCOUNT ON
GO

CREATE TABLE _TEST_BaseTest(myKey bigint PRIMARY KEY,myValue bigint,UselessColumn bigint)
CREATE TABLE _TEST_RowVersionTest(myKey bigint PRIMARY KEY,myValue bigint, RV rowversion)
CREATE TABLE _TEST_ModifiedVersionTest(myKey bigint PRIMARY KEY,myValue bigint, MV bigint)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER dbo._TEST_ModifiedVersionTest_Trigger
   ON  dbo._TEST_ModifiedVersionTest
   AFTER UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    UPDATE tbl
    SET tbl.MV=tbl.MV+1
    FROM _TEST_ModifiedVersionTest tbl
    INNER JOIN inserted i on i.myKey=tbl.myKey
END
GO

INSERT INTO _TEST_BaseTest (myKey,myValue)
    SELECT TOP 50000 <FIELD1>,<FIELD2> FROM <SOME_TABLE>

INSERT INTO _TEST_RowVersionTest (myKey,myValue)
    SELECT myKey,myValue
    FROM _TEST_BaseTest

INSERT INTO _TEST_ModifiedVersionTest (myKey,myValue,MV)
    SELECT myKey,myValue,1
    FROM _TEST_BaseTest

DECLARE     @StartTimeBase DATETIME, @EndTimeBase DATETIME
        ,   @StartTimeRV DATETIME, @EndTimeRV DATETIME
        ,   @StartTimeMV DATETIME, @EndTimeMV DATETIME
        ,   @CNT INT=0, @Iterations INT=25

--BASE
    SET @StartTimeBase=GETDATE()
    SET @CNT=1
    WHILE @CNT<=@Iterations
    BEGIN
        UPDATE _TEST_RowVersionTest SET myValue=myValue

        SET @CNT=@CNT+1
    END
    SET @EndTimeBase=GETDATE()

--RV
    SET @StartTimeRV=GETDATE()
    SET @CNT=1
    WHILE @CNT<=@Iterations
    BEGIN
        UPDATE _TEST_RowVersionTest SET myValue=myValue

        SET @CNT=@CNT+1
    END
    SET @EndTimeRV=GETDATE()

--MV
    SET @StartTimeMV=GETDATE()
    SET @CNT=1
    WHILE @CNT<=@Iterations
    BEGIN
        UPDATE _TEST_ModifiedVersionTest SET myValue=myValue

        SET @CNT=@CNT+1
    END
    SET @EndTimeMV=GETDATE()

DECLARE @Rows INT
SELECT @Rows=COUNT(*) FROM _TEST_BaseTest

PRINT CONVERT(VARCHAR,@Rows) + ' rows updated ' + CONVERT(VARCHAR,@Iterations) + ' time(s)'
PRINT CONVERT(VARCHAR,DATEDIFF(MS,@StartTimeBase,@EndTimeBase)) + ' Base Time Elapsed (ms)'
PRINT CONVERT(VARCHAR,DATEDIFF(MS,@StartTimeRV,@EndTimeRV))     + ' Rv Time Elapsed (ms)'
PRINT CONVERT(VARCHAR,DATEDIFF(MS,@StartTimeMV,@EndTimeMV))     + ' Mv Time Elapsed (ms)'

drop TABLE _TEST_BaseTest
drop TABLE _TEST_RowVersionTest
drop table _TEST_ModifiedVersionTest
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文