Re: MVCC cons

> On 08/14/07 14:34, Kenneth Downs wrote:
> > Tom Lane wrote:
> >> Kenneth Downs <ken(at)secdat(dot)com> writes:
> >>
> >>> Speaking as an end-user, I can give only one I've ever seen, which is
> >>> performance. Because of MVCC, Postgres's write performance (insert
> >>> and update) appears on my systems to be almost exactly linear to row
> >>> size. Inserting 1000 rows into a table with row size 100 characters
> >>> takes twice as long as inserting 1000 rows into a table with row size
> >>> 50 characters.
> >>>
> >>
> >> Not sure why you'd think that's specific to MVCC. It sounds like
> purely
> >> an issue of disk write bandwidth.
> >>
> >> regards, tom lane
> >>
> >
> > I did not see this in MS SQL Server.
>
> It is only logical that it will take 2x as long to insert 2x as much
> data.
>
> Maybe SQL Server is compressing out white space? Or (shudder)
> heavily caching writes?

There's no SQL Server magic. It doesn't compress whitespace or cache writes
in any scary way. Doubling with row width does double the insert time.

On SQL Server 2000 sp4:

Setup via:

create database test_db
use test_db
create table t50 ( f1 char(50) )
create table t100 ( f1 char(100) )

Test 1:

declare @start datetime
select @start = getdate()
begin transaction
insert into t50 ( f1 ) values (
'01234567890123456789012345678901234567890123456789' );
-- Repeat above insert 1000 times
commit transaction
select datediff(ms, @start, getdate())

Test 2:
declare @start datetime
select @start = getdate()
begin transaction
insert into t50 ( f1 ) values (
'012345678901234567890123456789012345678901234567890123456789012345678901234
5678901234567890123456789' );
-- Repeat above insert 1000 times
commit transaction
select datediff(ms, @start, getdate())

On my system, test one averages around 16ms over 100 tests. Test 2 averages
around 33ms over 100 tests.

I would wager my week's coffee change the same outcome on SQL 2005 sp2.

Paul