Welcome to the last part of this series, you can find the previous post here (http://www.sqlservercentral.com/blogs/denniss-sql-blog-1/2017/02/01/improve-performance-by-replacing-temp-tables-with-memory-optimized-tables-part-1/) and here (http://www.sqlservercentral.com/blogs/denniss-sql-blog-1/2017/02/08/improve-performance-by-replacing-temp-tables-with-memory-optimized-tables-part-2/).

Previously we discussed about using memory optimized tables to replace table variable and global temp table, this time we will replace local temp tables and seek for performance gain. We all use local temp tables here and there and its pretty common in development to hold temp data during a transaction, with SQL 2016 we should be able to get performance improvement with memory optimized tables.

Same as last time, let's assume a simple case here, a local temp table is used within a stored procedure and within it, it will insert 300 records to the temp table. As this is a local temp table, which existent are based on its session, we will need to modify our memory optimized table to fit that requirement, as each session should only allow to read its session data and nothing else, but given the way memory optimized table works, its just like a normal user table, hence we will need to add some constraint to limit it usage.

Firstly, let's start with the local temp table:

By using the ostress tool again, we simulate 100 connections with each connection execute 1000 times - a total of 100 thousand executions, it took a total of 27 seconds.

As discussed previously, the memory optimized table to replace local temp tables need to have additional constraint to ensure that only its own session can view and modify its data, this is done by creating function along with security policy.

With the same number of execution by 100 connections, it only take 7 seconds, which is close to 4X performance increase, its not as good as the previous use case, but it still a pretty good performance gain. Like everything else, there is a small code change involves, so please do test it in your own environment and make sure it work as the way you expected.

I hope this series can help everyone using SQL 2016 to improve performance in their own environment and pushing more on their workload.