April 27, 2016 at 7:14 am
Hello everyone.
Could anyone please tell me if there any workaround to implement update in natively compiled stored procedure of one memory optimized table using the data from another mem optimized table?
CREATE PROCEDURE [dbo].[bsp_SetMinCompetitorPrices_MO]
@spid INT,
@MinCompetitorsPrices as dbo.bT_MinCompetitorsPrices_MO readonly
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
UPDATE
otu
SET
otu.CompetitorPriceNat = mincp.MinCompetitorPrice
FROM
TT_OffersToUpdate_MO otu
INNER JOIN
@MinCompetitorsPrices mincp ON mincp.ConcreteProductId = otu.ConcreteProductId
WHERE
otu.spid = @spid
END
Thank you in advance,
respectfully,
Sergey
April 27, 2016 at 8:33 am
I do not have a SQL Server 2014 instance at hand to play around with so I need to go by what I find documented at https://msdn.microsoft.com/en-us/library/dn452279%28v=sql.120%29.aspx.
I do not see anything there that would rule out the construction you posted, but I assume that you have a valid reason for posting so I probably overlooked something in the small print.
My first thought was to replace the UPDATE ... FROM (which is, in my opinion, a dangerous and impractical construction anyway) with an ANSI standard UPDATE syntax. But that would use subqueries, and those are listed as not supported in native compilation on SQL Server 2014, so that option is out.
The only vialbe workaround that I still see is to code a loop that iterates over the rows in the @MinCompetitorsPrices table and then does an update of matching row(s) for each of the iterations of that loop. A coding style that is bound to be slow in traditional T-SQL, but native compilation can compensate for that. If you do build a procedure like that, I do recommend that you run a performance comparison between that version and a traditional (not compiled) version that used the UPDATE FROM you posted.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply