Clustered Columnstore Index Update Performance

The CCS is great for select performance, but what about update performance?

Let's find out, shall we?

Step 1.  Create a narrow table with the following characteristics:

create table [dbo].[Transactions](

[actid] [int] not null,

[tranid] [int] not null,

[val] [money] not null

) on [PRIMARY]

go

Step 2.  Created a clustered column store index on the table:

CREATE CLUSTERED COLUMNSTORE INDEX [ccs_idx] ON [dbo].[Transactions] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0) ON [PRIMARY]
GO

Step 3. Execute the following query ten times using SQLQueryStress Utility or you can just execute the DML statement in SSMS:

UPDATE dbo.Transactions
SET val = val + 1
WHERE tranid between 10000 and 10000000

SQLQueryStress Results

As you can see the average processing time was approx 82.2 seconds per iteration with the clustered columnstore enabled.

Step 4. Lets perform the same test again.  However, this time disable the clustered columnstore index

DROP INDEX [ccs_idx] ON [dbo].[Transactions];

UPDATE dbo.Transactions
SET val = val + 1
WHERE  tranid between 10000 and 10000000

CREATE CLUSTERED COLUMNSTORE INDEX [ccs_idx] on [dbo].[Transactions] with (drop_existing = off, COMPRESSION_DELAY = 0) on [PRIMARY]

And here are the results:

As you can see the average processing time decreased by a factor of 4 and was approx 19.9 seconds per iteration and the logical reads are much less.

Something to think about when designing your data pipelines.