SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Insert performance for cluster index or no index?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

qwert
Starting Member

2 Posts

Posted - 08/15/2013 :  04:14:14  Show Profile  Reply with Quote
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-performance

http://support.microsoft.com/kb/297861

my test scripts:

-------------------------------------------
--prepare table with clustered index
CREATE TABLE [dbo].[BigTable_CI](
[id] [int] IDENTITY(1,1) NOT NULL,
[BigChar] [char](4100) NOT NULL
)
GO

CREATE CLUSTERED INDEX CIX_BigTable_CI
ON BigTable_CI(id)
GO


ALTER TABLE [dbo].[BigTable_CI]
ADD CONSTRAINT [DF_BigTable_BigChar_CI] DEFAULT ('a') FOR [BigChar]
GO

CREATE 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
)
GO

ALTER TABLE [dbo].[BigTable_NI]
ADD CONSTRAINT [DF_BigTable_BigChar_NI] DEFAULT ('a') FOR [BigChar]
GO

CREATE 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

Canada
594 Posts

Posted - 08/15/2013 :  09:12:10  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000