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

  • Question

  • User-1800438376 posted

    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 <g class="gr_ gr_13 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Grammar only-ins doubleReplace replaceWithoutSep" id="13" data-gr-id="13">#temp</g> 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 <g class="gr_ gr_16 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling multiReplace" id="16" data-gr-id="16">optimise</g> 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.

    Friday, October 26, 2018 2:12 AM

All replies

  • User-595703101 posted

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

    Friday, October 26, 2018 6:22 AM
  • User-1800438376 posted

    I cannot see <g class="gr_ gr_14 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="14" data-gr-id="14">following</g> clause in CREATE TABLE command that makes the table <g class="gr_ gr_10 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="10" data-gr-id="10">memory optimized</g> 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. 

    Friday, October 26, 2018 9:47 AM
  • User-1800438376 posted
    ----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

    Monday, October 29, 2018 3:23 PM