Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
qwert
Starting Member
2 Posts |
Posted - 2013-08-15 : 04:14:14
|
Note, I am not talking about a clustered index against a non-cluster index, I mean a clustered index comparing to no index at all for inserting performance.I saw lots of links as below said that, a clustered index has better performance against no index even for insert operation, due to IAM/PFS/bitmap/..., but with my testing, seems no index is faster than cluster index, what's the problem?http://stackoverflow.com/questions/7264820/removing-a-primary-key-clustered-index-to-increase-insert-performancehttp://support.microsoft.com/kb/297861my test scripts:---------------------------------------------prepare table with clustered indexCREATE TABLE [dbo].[BigTable_CI]( [id] [int] IDENTITY(1,1) NOT NULL, [BigChar] [char](4100) NOT NULL) GOCREATE CLUSTERED INDEX CIX_BigTable_CI ON BigTable_CI(id)GOALTER TABLE [dbo].[BigTable_CI] ADD CONSTRAINT [DF_BigTable_BigChar_CI] DEFAULT ('a') FOR [BigChar]GOCREATE PROCEDURE [dbo].[AddDataToBigTable_CI](@NumberOfRows bigint) AS SET NOCOUNT ON; DECLARE @Counter int = 0; DECLARE @Start datetime = GETDATE(); DECLARE @End datetime; DECLARE @ElapsedTime int = 0; DECLARE @RowsPerSecond int = 0; WHILE (@Counter < @NumberOfRows) BEGIN INSERT INTO dbo.BigTable_CI DEFAULT VALUES; SELECT @Counter += 1; END; -- Calculate elapsed time and rows/second SET @End = GETDATE(); SET @ElapsedTime = CONVERT(INTEGER, DATEDIFF (second, @Start, @End)); SET @RowsPerSecond = @NumberOfRows/@ElapsedTime; -- Record results in local table INSERT INTO dbo.Results (StartTime, EndTime, ElapsedTime, NumberOfRows, RowsPerSecond) VALUES (@Start, @End, @ElapsedTime, @NumberOfRows, @RowsPerSecond); RETURN;---------------------------------------------prepare table without any index at all.CREATE TABLE [dbo].[BigTable_NI]( [id] [int] IDENTITY(1,1) NOT NULL, [BigChar] [char](4100) NOT NULL) GOALTER TABLE [dbo].[BigTable_NI] ADD CONSTRAINT [DF_BigTable_BigChar_NI] DEFAULT ('a') FOR [BigChar]GOCREATE PROCEDURE [dbo].[AddDataToBigTable_NI](@NumberOfRows bigint) AS SET NOCOUNT ON; DECLARE @Counter int = 0; DECLARE @Start datetime = GETDATE(); DECLARE @End datetime; DECLARE @ElapsedTime int = 0; DECLARE @RowsPerSecond int = 0; WHILE (@Counter < @NumberOfRows) BEGIN INSERT INTO dbo.BigTable_NI DEFAULT VALUES; SELECT @Counter += 1; END; -- Calculate elapsed time and rows/second SET @End = GETDATE(); SET @ElapsedTime = CONVERT(INTEGER, DATEDIFF (second, @Start, @End)); SET @RowsPerSecond = @NumberOfRows/@ElapsedTime; -- Record results in local table INSERT INTO dbo.Results (StartTime, EndTime, ElapsedTime, NumberOfRows, RowsPerSecond) VALUES (@Start, @End, @ElapsedTime, @NumberOfRows, @RowsPerSecond); RETURN; ---------------------------------------------prepare the results table create table dbo.Results ( StartTime datetime, EndTime datetime, ElapsedTime int, NumberOfRows int, RowsPerSecond int )---------------------------------------------run scripts: exec [dbo].[AddDataToBigTable_NI] 1000000 exec [dbo].[AddDataToBigTable_CI] 1000000 |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-08-15 : 09:12:10
|
Insert Performance should be better without any indexes.I am surprised if otherwise. What did you benchmark results say?Although a clustered index on an identity column, I think should be comparable to no indexes. So it would be interesting if you re-did your benchmark using a clustered index on a non-identity column, say an integer and then insert random numbers. |
|
|
|
|
|
|
|