memory optimised table is slower than temporary table while using in stored procedure, how to get performance on it - SQL server 2016 RSS

• • Reply
  luckyforu200...

  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. 

  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]
• • Reply
  eralper

  Contributor

  6041 Points

  1428 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

  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...

  T-SQL programming SQL Server Tutorials
  SQL Server 2017
  
• • Reply
  luckyforu200...

  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

  eralper

  I cannot see following clause in CREATE TABLE command that makes the table memory optimized table. Could you please verify

  MEMORY_OPTIMIZED=ON
  

  The SQL statement was pseudo code and I have missed memory_optimized tag. I have made following changes now. 

  Please mark as Answer if post helps[ Imran Khan MCP,SCJP]
• • Reply
  luckyforu200...

  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

  ----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]