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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply