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?

• Edited by oleolehoohoo 6 minutes ago