Insert Performance was slow

Hello,

The dev team was doing bulk inserts and it takes 15 minutes to insert almost 6k rows and would timeout. After a few attempts the all the inserts succesfully get done in around 17 mins. This is our staging env. Here is a DDL of how the table looked before.

CREATE TABLE [dbo].[Vres](
	[VresID] [uniqueidentifier] NOT NULL,
	[Message] [varchar](max) NOT NULL,
	[PID] [uniqueidentifier] NOT NULL,
	[Segment] [int] NOT NULL,
	[MessageType] [nvarchar](50) NOT NULL,
	[PMID] [uniqueidentifier] NULL,
 CONSTRAINT [PK_VresID] PRIMARY KEY CLUSTERED 
(
	[VresID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Vres]  WITH CHECK ADD  CONSTRAINT [FK_Vres_PPer] FOREIGN KEY([PID])
REFERENCES [dbo].[PPer] ([PID])
GO

ALTER TABLE [dbo].[Vres] CHECK CONSTRAINT [FK_Vres_PPer]
GO

The primary key on this table as shown above is on a GUID. However upon reaching me I suggested the devteam that adding an integer identity column as a primary key would fasten the inserts. The changes were made and upon testing we noticed that a multiple 6k row inserts were taking around 4mins.  Here is how the new DDL looks like.

CREATE TABLE [dbo].[Vres](
	[VresID] [uniqueidentifier] NOT NULL,
	[Message] [varchar](max) NOT NULL,
	[PID] [uniqueidentifier] NOT NULL,
	[Segment] [int] NOT NULL,
	[MessageType] [nvarchar](50) NOT NULL,
	[PMID] [uniqueidentifier] NULL,
	[PTID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_PTID] PRIMARY KEY CLUSTERED 
(
	[PTID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Vres]  WITH CHECK ADD  CONSTRAINT [FK_Vres_PPer] FOREIGN KEY([PID])
REFERENCES [dbo].[PPer] ([PID])
GO

ALTER TABLE [dbo].[Vres] CHECK CONSTRAINT [FK_Vres_PPer]
GO



ALTER TABLE [dbo].[Vres] ADD  CONSTRAINT [PK_PTID] PRIMARY KEY CLUSTERED 
(
	[PTID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

However the devteam is now saying that they dont want the primary key as the new identity column instead have a clustered index on the new columnn that I added  which is PTID but have the primary key as the VResID.

I am not sure if this is going to really help speed up the inserts here.

Experts please leave your valuable inputs here. Do you think it speeds up the inserts?


September 7th, 2015 6:57am

I agree with Naomi's suggestion to simply use NEWSEQUENTIALID().  Random key values are costly because of reduced buffer efficiency, which is especially costly against spinning media due to head movement.  See http://www.dbdelta.com/improving-uniqueidentifier-performance/ for a more detailed explanation.

Free Windows Admin Tool Kit Click here and download it now
September 10th, 2015 7:54pm

I think 2) fits your scenario better. From performance perspective there should be no difference as long as your clustered index in the identity and it doesn't matter if GUID is PK or Identity is a PK. Since your existing PK is GUID, it makes sense to leave i
September 10th, 2015 10:27pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics