Don't use stored procedure for inserting one record.

If you want insert more then 1000 records, then use XML.
Yes, you can pass XML to stored procedure as parameter (VARCHAR(MAX)).

MartinaJ

>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 :)