Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Using In-Memory Temp Tables
Published: Dec 02, 2020
Following on from the few posts I’ve written about in-memory tables I wanted to mention this useful tip you can use in order to potentially speed up your temp tables whether that be within stored procedures or maybe within an ETL process.

Let’s look at the example of a stored procedure:

drop procedure if exists dbo.testProc
go

create procedure dbo.testProc
as
       drop table if
exists #temp

      
select *
      
into #temp
      
from AdventureWorks2012.sales.SalesOrderDetail

      
update #temp
      
set CarrierTrackingNumber = CarrierTrackingNumber + '_New'
      
where salesOrderID in (53913, 68138)

      
update #temp
      
set UnitPrice = case UnitPrice when 0 then 1 else UnitPrice * 1.1 end
       where
ProductID between 850 and 900
go


Now, let’s run this using SQL Query Stress to see how it performs when run 25 times over 10 parallel threads…


So now let’s see if we can do this in a different manner utilizing in-memory tables (therefore avoiding issues around logging and disk activity):

create type dbo.mySalesOrderDetail
as table
(
      
SalesOrderID int not null,
      
SalesOrderDetailID int not null,
      
CarrierTrackingNumber nvarchar(25) null,
      
OrderQty smallint not null,
      
ProductID int not null,
      
SpecialOfferID int not null,
      
UnitPrice money not null,
      
UnitPriceDiscount money not null,
      
LineTotal decimal(19, 10) not null,
      
rowguid uniqueidentifier not null,
      
ModifiedDate datetime not null,
      
index ix_salesOrderMem hash (salesOrderDetailID) with (bucket_count = 100)
)
with (memory_optimized = on)
go

drop procedure if exists dbo.testProc
go

create procedure dbo.testProc
as
       declare
@myTable mySalesOrderDetail

      
insert into @myTable
      
select *
      
from AdventureWorks2012.sales.SalesOrderDetail

      
update @myTable
      
set CarrierTrackingNumber = CarrierTrackingNumber + '_New'
      
where salesOrderID in (53913, 68138)

      
update @myTable
      
set UnitPrice = case UnitPrice when 0 then 1 else UnitPrice * 1.1 end
       where
ProductID between 850 and 900
go


Again, we run this 25 times over 10 threads…


This was a very rudimentary proc running a small amount of data (and throwing a lot into memory each time)… yet even so we can still see a clear 30% reduction in costs simply by using an in-memory table type rather than traditional temp tables.

I would advise testing because it’s possible you could see even greater system performance by using them. It’s also possible you could find they’re not suitable (my next post shows a down side to in-memory objects). Either way I think it’s well worth trialing to see what results you get.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.