Monday, October 13, 2014

0 SQL2014: In Memory OLTP (4) Natively Compiled Store procedure

Another enhancement for the In-Memory OLTP is that we can compile the store procedure with native assembly code so SQL server do not need to parse the query plan in the runtime. 

Native Store procedure has some limitations:

  • Only can access in-memory tables
  • EXECUTE AS CALLER is not supported
  • 2-part identifier required ( schema.table )
  • Limit T-SQL support:
    • No Distinct
    • No CTE
    • No Ranking functions
    • No Exists/in
    • No sub query

Below performance testing is done compare

  • Disk-based table and interpreted Transact-SQL
  • Disk-based table and store procedure
  • Memory-optimized table w/hash index and interpreted Transact-SQL
  • Memory-optimized table w/hash index and interpreted SP
  • Memory-optimized table w/hash index and Native SP

image

 

create the test table

-- ===============================================================================================================
-- Create disk base tables 
-- ===============================================================================================================
 
CREATE TABLE [product_disk]
(
[key] INT NOT NULL PRIMARY KEY,
[description] NCHAR(48) NOT NULL
)
GO
 
 
-- ===============================================================================================================
-- Create memory-optimized tables 
-- ===============================================================================================================
 
CREATE TABLE product_mem
(
[key] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
[description] NCHAR(48) NOT NULL
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA)
GO

Create store Procedure 

 
--- Create sp to insert disk base table
 
CREATE PROCEDURE sp_insert_product_disk
       @rowcount int,
       @description nchar(48)
AS
       DECLARE @key INT = 1
 
       WHILE @key <= @rowcount
       BEGIN
                 INSERT INTO [dbo].product_disk VALUES (@key, @description)
                 SET @key += 1
       END
GO
--- create sp to insert mem table
CREATE PROCEDURE sp_insert_product_mem
       @rowcount int,
       @description nchar(48)
AS
       DECLARE @key INT = 1
 
       WHILE @key <= @rowcount
       BEGIN
                 INSERT INTO [dbo].product_mem VALUES (@key, @description)
                 SET @key += 1
       END
GO
 
 
--- create native sp to insert into mem table
CREATE PROCEDURE sp_native_insert_product_mem
       @rowcount int,
       @description nchar(48)
WITH NATIVE_COMPILATION, SCHEMABINDING
,EXECUTE AS OWNER  -- EXECUTE AS CALLER is not supported 
AS 
 BEGIN ATOMIC -- Either entire sp complete succesfully or fail
 WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
       DECLARE @key INT = 1
 
       WHILE @key <= @rowcount
       BEGIN
                 INSERT INTO [dbo].product_mem VALUES (@key, @description)
                 SET @key += 1
       END
END
GO

After native store procedure, we can find the dll is being place in the disk folder .

SELECT name, DESCRIPTION FROM sys.dm_os_loaded_modules
WHERE name like '%xtp_p_' + CONVERT(varchar, db_id()) + '_' + CONVERT(varchar, OBJECT_ID('dbo.sp_native_insert_product_mem')) + '.dll'

image

Performance testing 

 
-- ===============================================================================================================
-- Inserting data into disk-based tables using interpreted T-SQL
-- ===============================================================================================================
 
-- Delete data if exists
DELETE FROM [product_disk]
GO 
DECLARE @starttime DATETIME2 = SYSDATETIME(),
        @timems INT
DECLARE @key INT = 1
DECLARE @rowcount INT = 100000
DECLARE @description NCHAR(48) = N'Product0123456789012345678901234567890'
 
BEGIN TRAN
WHILE @key <= @ROWCOUNT
BEGIN
       INSERT INTO [product_disk] VALUES (@key, @description)
       SET @key += 1
END
COMMIT
 
SET @timems = DATEDIFF(ms, @starttime, SYSDATETIME())
SELECT 'Disk-based table and interpreted Transact-SQL: ' + CAST(@timems AS VARCHAR(10)) + ' ms'
 
 
-- ===============================================================================================================
-- Inserting data into disk-based tables using interpreted stored procedure
-- ===============================================================================================================
 
DELETE FROM [product_disk]
GO 
SET STATISTICS TIME OFF
 
DECLARE @starttime DATETIME2 = SYSDATETIME(),
        @timems INT
DECLARE @key INT = 1
DECLARE @rowcount INT = 100000
DECLARE @description NCHAR(48) = N'Product0123456789012345678901234567890'
 
SET @starttime = SYSDATETIME()
EXEC sp_insert_product_disk @rowcount, @description
 
SET @timems = DATEDIFF(ms, @starttime, SYSDATETIME())
SELECT 'Disk-based table and interpreted stored procedure: ' + CAST(@timems AS VARCHAR(10)) + ' ms'
 
-- ===============================================================================================================
-- Inserting data into memory-optimized table using interpreted T-SQL
-- ===============================================================================================================
 
 
--Delete data if exists
DELETE FROM [dbo].product_mem
GO
 
DECLARE @starttime DATETIME2 = SYSDATETIME(),
        @timems INT
DECLARE @key INT = 1
DECLARE @rowcount INT = 100000
DECLARE @description NCHAR(48) = N'Product0123456789012345678901234567890'
 
 
SET @key = 1
SET @starttime = sysdatetime()
 
BEGIN TRAN
WHILE @key <= @rowcount
BEGIN
       INSERT INTO product_mem VALUES (@key, @description)
       SET @key += 1
END
COMMIT
 
SET @timems = DATEDIFF(ms, @starttime, SYSDATETIME())
SELECT 'Memory-optimized table w/hash index and interpreted Transact-SQL: ' + CAST(@timems AS VARCHAR(10)) + ' ms'
 
 -- ===============================================================================================================
-- Inserting data into memory-optimized table using interpreted stored procedure
-- ===============================================================================================================
 --Delete data if exists
DELETE FROM [dbo].product_mem
GO
 
DECLARE @starttime DATETIME2 = SYSDATETIME(),
        @timems INT
DECLARE @rowcount INT = 100000
DECLARE @description NCHAR(48) = N'Product0123456789012345678901234567890'
 
SET @starttime = sysdatetime()
 
EXEC [dbo].sp_insert_product_mem @rowcount, @description
 
SET @timems = DATEDIFF(ms, @starttime, SYSDATETIME())
SELECT 'Memory-optimized table w/hash index and interpreted SP:' + CAST(@timems AS VARCHAR(10)) + ' ms'
 
 -- ===============================================================================================================
-- Inserting data into memory-optimized table using native  stored procedure
-- ===============================================================================================================
 --Delete data if exists
DELETE FROM [dbo].product_mem
GO
 
DECLARE @starttime DATETIME2 = SYSDATETIME(),
        @timems INT
DECLARE @rowcount INT = 100000
DECLARE @description NCHAR(48) = N'Product0123456789012345678901234567890'
 
SET @starttime = sysdatetime()
 
EXEC [dbo].sp_native_insert_product_mem @rowcount, @description
 
SET @timems = DATEDIFF(ms, @starttime, SYSDATETIME())
SELECT 'Memory-optimized table w/hash index and interpreted SP:' + CAST(@timems AS VARCHAR(10)) + ' ms'
Posted by Po@sqlpanda at 5:36 PM
Labels: , ,

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)
 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates