November 18, 2015 at 1:29 am
I have a sample table with following structure:
CREATE TABLE [dbo].[RTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Amount] [int] NULL,
[DateRange] [date] NULL,
CONSTRAINT [PK_RTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
To add rows (note that the database is using high insertions/updates and selects), I want to see which method is best among following or is there any other method (SQL 2012+) which may I need to learn and apply in my database SPs. It should be looking by Performance wise faster with no convertions/selects or in-memory additions and retrieval etc.
Alter procedure usp_AddRates1 (@Amount int, @DateR datetime, @ReturnId int out)
AS
/*
Declare @RId int
EXEC usp_AddRates1 @Amount = 100, @DateR = '17-Nov-2015 12:30AM', @ReturnId = @RId output
select @RId
*/
BEGIN
set nocount on
INSERT INTO [dbo].[RTable] ([Amount] ,[DateRange]) VALUES(@Amount, @DateR)
select @ReturnId = SCOPE_IDENTITY ()
END
--
Alter procedure usp_AddRates2 (@Amount int, @DateR datetime, @ReturnId int = null out )
AS
/*
EXEC usp_AddRates2 @Amount = 100, @DateR = '17-Nov-2015 12:30AM'
*/
BEGIN
set nocount on
INSERT INTO [dbo].[RTable] ([Amount] ,[DateRange])
output inserted.Id
select @Amount, @DateR
--return
END
--
Alter procedure usp_AddRates3 (@Amount int, @DateR datetime, @ReturnId int = null out)
AS
/*
Declare @RId int
EXEC usp_AddRates3 @Amount = 100, @DateR = '17-Nov-2015 12:30AM', @ReturnId = @RId output
select @RId
*/
BEGIN
set nocount on
Declare @T table (RId int)
INSERT INTO [dbo].[RTable] ([Amount] ,[DateRange])
output inserted.Id into @T
select @Amount, @DateR
select RId from @T
END
Clients will be using C# MVC/WCF to call this SP and get the Id in return.
November 18, 2015 at 1:48 am
If what you are interested in is only performance, then I would suggest using sequence instead of identity and also use the return code or an output parameter in the stored procedure instead of returning it as a recordset by using select statement. Notice that from what I read, there should be an improvement, but a very small improvement. I never checked it in a benchmark.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 18, 2015 at 2:26 am
I would suggest using sequence instead of identity
Please explain in simple. Also let me know which SP from my given sample you suggest one should I or everyone use. A smaller Performance improvement makes an improvement for large system with heavy insertions/select. Is not it?
Thanks !!!
Shamshad Ali
November 18, 2015 at 7:02 am
Adi Cohn-120898 (11/18/2015)
If what you are interested in is only performance, then I would suggest using sequence instead of identity and also use the return code or an output parameter in the stored procedure instead of returning it as a recordset by using select statement. Notice that from what I read, there should be an improvement, but a very small improvement. I never checked it in a benchmark.Adi
Why is sequence better than identity. Identity just uses sequence under the covers. They're basically the same functionality.
I agree that using an output parameter is the best bet for performance. Although, using it as the return code is even a little better, but that requires odd coding, so it's not something I generally worry about to gain a millisecond or two. Few people are at that point of worry.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 18, 2015 at 7:04 am
shamshad.ali (11/18/2015)
I have a sample table with following structure:
CREATE TABLE [dbo].[RTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Amount] [int] NULL,
[DateRange] [date] NULL,
CONSTRAINT [PK_RTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
To add rows (note that the database is using high insertions/updates and selects), I want to see which method is best among following or is there any other method (SQL 2012+) which may I need to learn and apply in my database SPs. It should be looking by Performance wise faster with no convertions/selects or in-memory additions and retrieval etc.
Alter procedure usp_AddRates1 (@Amount int, @DateR datetime, @ReturnId int out)AS
/*
Declare @RId int
EXEC usp_AddRates1 @Amount = 100, @DateR = '17-Nov-2015 12:30AM', @ReturnId = @RId output
select @RId
*/
BEGIN
set nocount on
INSERT INTO [dbo].[RTable] ([Amount] ,[DateRange]) VALUES(@Amount, @DateR)
select @ReturnId = SCOPE_IDENTITY ()
END
--
Alter procedure usp_AddRates2 (@Amount int, @DateR datetime, @ReturnId int = null out )
AS
/*
EXEC usp_AddRates2 @Amount = 100, @DateR = '17-Nov-2015 12:30AM'
*/
BEGIN
set nocount on
INSERT INTO [dbo].[RTable] ([Amount] ,[DateRange])
output inserted.Id
select @Amount, @DateR
--return
END
--
Alter procedure usp_AddRates3 (@Amount int, @DateR datetime, @ReturnId int = null out)
AS
/*
Declare @RId int
EXEC usp_AddRates3 @Amount = 100, @DateR = '17-Nov-2015 12:30AM', @ReturnId = @RId output
select @RId
*/
BEGIN
set nocount on
Declare @T table (RId int)
INSERT INTO [dbo].[RTable] ([Amount] ,[DateRange])
output inserted.Id into @T
select @Amount, @DateR
select RId from @T
END
Clients will be using C# MVC/WCF to call this SP and get the Id in return.
The two mechanisms you're using are best. If you only ever add a single row, just use scope_identity. Using the inserted table is good for multi-row inserts where you need to get all the identity (or sequence) values generated.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply