Hi,

I am working on testing a system and have created about 13 million records in a transaction table. Now that I am testing my SP in SSMS that inserts the data, it sits there forever (so far over 20 minutes). On the test site with the application that calls this SP of course it times out. What can I do to improve this?

This is the table definition:

/****** Object:  Table [dbo].[Transactions]    Script Date: 25/02/2016 09:41:14 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Transactions](
	[GUID] [uniqueidentifier] NOT NULL,
	[DateImported] [datetime] NULL,
	[OID] [nvarchar](max) NULL,
	[PNR] [nvarchar](max) NULL,
	[StoredFare] [float] NULL,
	[RMQ] [nvarchar](max) NULL,
	[LowerFare] [float] NULL,
	[Timestamp] [datetime] NULL,
	[Month] [nvarchar](50) NULL,
	[Day] [int] NULL,
	[Year] [int] NULL,
	[Time] [varchar](50) NULL,
	[MFRA] [int] NULL,
	[ScriptResult] [varchar](max) NULL,
	[AgentID] [varchar](max) NULL,
	[PNRStatus] [varchar](max) NULL,
	[Segments] [varchar](max) NULL,
	[Currency] [varchar](50) NULL,
 CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED 
(
	[GUID] 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].[Transactions] ADD  CONSTRAINT [DF_Transactions_GUID]  DEFAULT (newid()) FOR [GUID]
GO

ALTER TABLE [dbo].[Transactions] ADD  CONSTRAINT [DF_Transactions_DateImported]  DEFAULT (getdate()) FOR [DateImported]
GO

This is the SP:

USE [RESREPRICERTest]
GO

/****** Object:  StoredProcedure [dbo].[InsertTransaction]    Script Date: 25/02/2016 09:42:15 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[InsertTransaction]
	-- Add the parameters for the stored procedure here
	@GUID uniqueidentifier
	,@OID nvarchar(max)
	,@PNR nvarchar(max)
	,@StoredFare float
	,@RMQ nvarchar(max)
	,@LowerFare float
	,@Timestamp datetime
	,@MFRA int
	,@ScriptResult varchar(max)
	,@AgentID varchar(max)
	,@PNRStatus varchar(max)
	,@Segments varchar(max)
	,@Currency varchar(50)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	if not exists(Select 1 From [dbo].[Transactions] Where PNR = @PNR AND Timestamp = @Timestamp)
      BEGIN
		INSERT INTO [dbo].[Transactions]
				   ([GUID] 
				   ,[OID]
				   ,[PNR]
				   ,[StoredFare]
				   ,[RMQ]
				   ,[LowerFare]
				   ,[Timestamp]
				   ,[MFRA]
				   ,[ScriptResult]
				   ,[AgentID]
				   ,[PNRStatus]
				   ,[Segments]
				   ,[Currency])
			 VALUES
				   (@GUID
				   ,@OID
				   ,@PNR
				   ,@StoredFare
				   ,@RMQ
				   ,@LowerFare
				   ,@Timestamp
				   ,@MFRA
				   ,@ScriptResult
				   ,@AgentID
				   ,@PNRStatus
				   ,@Segments
				   ,@Currency)

		END
END


GO

I am trying to get the execution plan to see if that helps but if course it is taking so long.

Any ideas appreciated :)