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.