We have a lot many stored procedure and all our business logic is in stored procedure itself. In order to improve the performance of the stored procedure, we have used #temp table to store data first from the physical table and later use the same. Recently
we have migrated to SQL 2016 in order to gain memory optimise table benefits. Memory optimizes table are behaving inappropriately while used in a stored procedure, in fact, #temp tables are faster than memory-optimized tables. Below are sample examples, please
let me know how to optimized stored procedure performance using memory-optimized tables.
create table tblDepartment
(
departmentid int primary key
departmentname varchar(500)
)
GO
create table tblEmployee
(
employeeid int primary key,
departmentid int foreign key,
name varchar(500)
age int
)
GO
create table tblEmployeeTransaction
(
transactionid int primary key,
employeeid int foreign key,
amt money,
currentdate datetime
)
GO
-------------Using temporary tables---------------
CREATE procedure spGetEmployeesTransaction_Temp
(
@deparmentid int
)
as begin
---inserts are happening in parallel post sql 2014
select * into #tempEmployee from tblEmployee where departmentid = @departmentid
--- this are the basics there are many complex joins
select * from tblEmployeeTransaction T inner join #tempEmployee E on T.employeeid = E.employeeid
end
go
-------------Using memory optimized tables---------------
create type udtEmployeeMemory AS TABLE
(
employeeid int primary key nonclustered,
departmentid int,
name varchar(500)
age int
) with memory_optimise = ON
GO
CREATE procedure spGetEmployeesTransaction_MemoryOptimized
(
@deparmentid int
)
as begin
DECLARE @mememployee AS udtEmployeeMemory
---inserts are happening in sequential
insert into @mememployee (employeeid,name,age,departmentid)
select employeeid,name,age,departmentid from tblEmployee where departmentid = @departmentid
--- this are the basics there are many complex joins and even joins with @mememployee is ---- index scan not index seek.
-- index seek will come if i hardcode the values
--- e.g. select * from @mememployee where id = 500
select * from tblEmployeeTransaction T inner join @mememployee E on T.employeeid = E.employeeid
end
go
I will appreciate if this problem is fixed ASAP.
Please mark as Answer if post helps[ Imran Khan MCP,SCJP]
I cannot see following clause in CREATE TABLE command that makes the table memory optimized table. Could you please verify
MEMORY_OPTIMIZED=ON
It is very interesting that following code works with better performance
select * into #tempEmployee from tblEmployee where departmentid = @departmentid
select * from tblEmployeeTransaction T inner join #tempEmployee E on T.employeeid = E.employeeid
It would work with a simple INNER JOIN between tblEmployee and tblEmployeeTransaction tables
Maybe execution plan caching was creating the performance issues...
----create type with memory optimise on
DROP TYPE IF exists dbo.Employee_Mem_Type
CREATE TYPE dbo.Employee_Mem_Type AS TABLE
(
emp_id int primary key nonclustered,
emp_name varchar(100),
emp_status char(1)
)
WITH (MEMORY_OPTIMIZED=ON)
GO
DROP TABLE IF exists dbo.Employee
CREATE TABLE dbo.Employee
(
emp_id int primary key,
emp_name varchar(100),
emp_status char(1)
)
GO
DECLARE @intcounter int = 0
WHILE(@intcounter < 500000) BEGIN
insert into Employee(emp_id,emp_name,emp_status) values(@intcounter,'imran','Y')
SET @intcounter = @intcounter + 1
END
DROP PROCEDURE IF exists dbo.spGetEmployee_Mem
GO
CREATE PROCEDURE spGetEmployee_Mem
as BEGIN
DECLARE @employeememtype as Employee_Mem_Type
insert into @employeememtype(emp_id,emp_name,emp_status)
select emp_id,emp_name,emp_status from Employee
select * from @employeememtype
END
GO
DROP PROCEDURE IF exists dbo.spGetEmployee
GO
CREATE PROCEDURE spGetEmployee
as BEGIN
select emp_id,emp_name,emp_status into #temp from Employee
select * from #temp
END
GO
-- insert records from the normal physical table to temp tables
spGetEmployee -- check execution plan along with timing
-- insert records from the normal physical table to memory table types
spGetEmployeeMem
Please mark as Answer if post helps[ Imran Khan MCP,SCJP]
Participant
786 Points
508 Posts
memory optimised table is slower than temporary table while using in stored procedure, how to get...
Oct 26, 2018 02:12 AM|luckyforu2006|LINK
Dear experts,
We have a lot many stored procedure and all our business logic is in stored procedure itself. In order to improve the performance of the stored procedure, we have used #temp table to store data first from the physical table and later use the same. Recently we have migrated to SQL 2016 in order to gain memory optimise table benefits. Memory optimizes table are behaving inappropriately while used in a stored procedure, in fact, #temp tables are faster than memory-optimized tables. Below are sample examples, please let me know how to optimized stored procedure performance using memory-optimized tables.
I will appreciate if this problem is fixed ASAP.
Contributor
6101 Points
1449 Posts
Re: memory optimised table is slower than temporary table while using in stored procedure, how to...
Oct 26, 2018 06:22 AM|eralper|LINK
I cannot see following clause in CREATE TABLE command that makes the table memory optimized table. Could you please verify
It is very interesting that following code works with better performance
It would work with a simple INNER JOIN between tblEmployee and tblEmployeeTransaction tables
Maybe execution plan caching was creating the performance issues...
SQL Server 2017
Participant
786 Points
508 Posts
Re: memory optimised table is slower than temporary table while using in stored procedure, how to...
Oct 26, 2018 09:47 AM|luckyforu2006|LINK
The SQL statement was pseudo code and I have missed memory_optimized tag. I have made following changes now.
Participant
786 Points
508 Posts
Re: memory optimised table is slower than temporary table while using in stored procedure, how to...
Oct 29, 2018 03:23 PM|luckyforu2006|LINK
-- insert records from the normal physical table to temp tables
spGetEmployee -- check execution plan along with timing
-- insert records from the normal physical table to memory table types
spGetEmployeeMem