找到你要的答案

Q:Does rowversion/timestamp affects performance significantly?

Q:没有时间戳记/时间戳对绩效的影响显著?

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.

我计划增加在我的DB RowVersion跟踪这些表中变化最表。我知道添加它会影响查询的性能。

没有人知道它会影响性能的一点点(百分之几的慢)或我不应该添加RowVersion很多表,因为它使数据库更慢。

answer1: 回答1:

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.

只是添加一个时间戳记/时间戳列性能不同的是,你现在是8个字节宽的行。

实际的性能差异来当你开始实际使用他们的东西。但在我回答类似的问题点我:时间戳记和性能

如果你不使用时间戳记字段检查更新的项目,而你要使用它的一致性,确保不因为你读的最后一个更新,那么这将是一个完全可以接受的使用并不会影响。

如:

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

因此,当检查行的性能,以确保它没有更新,因为应用程序上次读取将是微不足道的性能差异(它必须读取行更新反正)。

但性能时,试图用一个时间戳记/时间戳列作为一种手段来获取所有更新的项目,因为我们最后一次检查会很可怜。

answer2: 回答2:

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

我感兴趣的,我们使用的版本是由触发,所以当我看到这个我要了解更多关于性能。所以,我开始设置一个测试场景。我想比我们目前使用的(触发)和时间戳记柱与另一个表没有版本控制。

毫不奇怪,触发一定的表现比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
sql  sql-server  performance  rowversion