• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

Performance Impact of Adding TimeStamp Columns

The new typed DataSet generation in .NET 2.0 (and VS2005) optimizes concurrency conflict checking when you have a timestamp column in the table. That's a very good thing. But, what is the impact on performance for adding such a column to all of my tables? Is it pretty negligible? I'm not worried about the space - I can compute that.


Thanks,
David
0
anyoneis
Asked:
anyoneis
  • 2
1 Solution
 
JesterTooCommented:
There will be no measurable impact.  This is not a new datatype in SS2005... it existed in earlier versions of SQL Server.  However, it's use to assist in concurrency conflicts is new... but that won't come into play until a conflict actually occurs which should be so infrequent as to also have no performance implications.

It's "time cost" on an insert is not much different than any other "auto-populate" column types such as IDENTITY, DEFAULT, GUID, etc.

HTH,
Lynn
0
 
anyoneisAuthor Commented:
Thanks. The old typed dataset code generated (and also the code generated by SqlCommandBuilder) was very wordy, since it would use the where clause to ensure that the only row updated was the one which had all the original contents intact. This meant that for a 40 column table, 40 columns of new data and 40 columns of old data where included in the generated SQL  statement. Now, just the primary key and the TimeStamp column are used, which cuts down on the amount of T-SQL used which should have a net positive effect on performance.

Any other comments about performance implications of TimeStamp (aka RowVersion) columns?

David
0
 
anyoneisAuthor Commented:
Thanks... But I needed some more certainty, so I tried this:

use test
DROP TABLE Foo1
CREATE TABLE Foo1
(
      ID INTEGER IDENTITY(1,1) NOT NULL,
      NOTID CHAR(8) DEFAULT('12345678'),
      CONSTRAINT [PK_Foo1] PRIMARY KEY  CLUSTERED
      (
            [ID]
      )  ON [PRIMARY]
)


DROP TABLE Foo2
CREATE TABLE Foo2
(
      ID INTEGER IDENTITY(1,1) NOT NULL,
      TS TIMESTAMP NOT NULL,
      CONSTRAINT [PK_Foo2] PRIMARY KEY  CLUSTERED
      (
            [ID]
      )  ON [PRIMARY]
)

SET NOCOUNT ON
DECLARE @Start DATETIME

SELECT @Start = GETDATE()
WHILE  IDENT_CURRENT('Foo1') <= 10000
      INSERT Foo1 DEFAULT VALUES
SELECT '10000 Foo1 Records took ' + CONVERT(VARCHAR, DATEDIFF(ms, @Start, GETDATE())) + ' ms'

SELECT @Start = GETDATE()
WHILE  IDENT_CURRENT('Foo2') <= 10000
      INSERT Foo2 DEFAULT VALUES
SELECT '10000 Foo2 Records took ' + CONVERT(VARCHAR, DATEDIFF(ms, @Start, GETDATE())) + ' ms'

SELECT @Start = GETDATE()
WHILE  IDENT_CURRENT('Foo1') <= 20000
      INSERT Foo1 VALUES('12345678')
SELECT '10000 Foo1 Records took ' + CONVERT(VARCHAR, DATEDIFF(ms, @Start, GETDATE())) + ' ms'

SELECT @Start = GETDATE()
WHILE  IDENT_CURRENT('Foo2') <= 20000
      INSERT Foo2 DEFAULT VALUES
SELECT '10000 Foo2 Records took ' + CONVERT(VARCHAR, DATEDIFF(ms, @Start, GETDATE())) + ' ms'

And the results:

                                                         
---------------------------------------------------------
10000 Foo1 Records took 2133 ms
                                                         
---------------------------------------------------------
10000 Foo2 Records took 2106 ms
                                                         
---------------------------------------------------------
10000 Foo1 Records took 2103 ms
                                                         
---------------------------------------------------------
10000 Foo2 Records took 2073 ms

As predicted, there is very little difference - and even a speedup over using a default CHAR value or a specified one.

David
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now