February 14, 2017 at 8:52 am
This question is for anyone with experience with memory-optimized tables and natively-compiled stored procedures in SQL Server 2014.
I have a stored procedure that among many of the queries, it updates records in a memory-optimized table (target) from values in another memory-optimized table (source). These are staging tables, and the update is as follows:
update TARGET
set VALUE = S1.VALUE - S2.VALUE
from TARGET T inner join
SOURCE S1 on
S1.ID = T.ID1 inner join
SOURCE S2 on
S2.ID = T.ID2
The target table has about 100,000 records and the source table has about 20 million records. Column ID in the source table is hash-indexed.
My question is whether this simple update will run faster if I create a natively-compiled stored procedure with the update statement only, or if I keep this in the standard SQL stored procedure. If it runs faster, is it orders of magnitude, or only fractionally faster? I'm staying away from natively-compiled procedures in SQL Server 2014 as it is functionally very limited.
February 14, 2017 at 9:59 am
This has got to be pretty darn easy to do a POC for. Everything you need is in place. Just make a new sproc that is compiled and time that compared to the regular version. I would not expect it to be orders of magnitude from just that change. Maybe one order, but possibly not even that.
I would take time to make absolutely certain I have the best indexing strategy for the tables. And don't forget your bucket count guidelines!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 15, 2017 at 6:52 am
Just piling on.
It'll be faster if you use natively compiled. It won't be orders of magnitude faster, but it'll run faster. The question I have, if you have a situation where you can use the natively compiled procedures, why wouldn't you? I get it that they're limited so you can only use them in certain situations, but if you're within one of those situations, it's a win with very little down-side. Why not take advantage?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply