Faster SQL temp table and table variable by using memory optimization

Question

Scenario C in This Microsoft Doc describes how temp tables scoped to a connection can be replaced with Memory-Optimized Tables. The scheme uses a Filter Security Policy which calls a function to determine if @@spid matches the SpidFilter column in the Memory-Optimized table.

Will this work with .NET connection pooling? I would expect @@spid will return the same number as a connection is re-used over and over again. .NET clears the session scoped temp tables by calling sp_reset_connection, but that will not clear Memory-Optimized tables, or change @@spid. Maybe sys.dm_exec_sessions's session_id could be added to make it work in a connection pooling environment?

Maybe instead of using @@spid you could utilize context info or session context function Of course during each call you have to set it to correct value before doing your query and after reset to default, but it should handle pooling. — Lukasz Szozda
– Lukasz Szozda, Commented Jun 14, 2018 at 5:49
I gave it a try but hit a wall when attempting to retrieve the context info from the Security Policy Predicate function. That function must be natively compiled since it is being called from a memory optimized table. Compiled modules may not call the Session_Context function : "The function 'session_context' is not supported with natively compiled modules" — Christopher J. Grace
– Christopher J. Grace, Commented Jun 16, 2018 at 3:03
No one has responded to the comment in Microsoft's documentation either. I am surprised my question has not received more action. This is such an important use case in my world because it helps multiple stored procedures work with large temporary datasets. An alternative is declaring a temp table in a "top-level" proc, then referring to it in a "sub" proc, but the temp table's schema is unknown to the VSDT at design time and compile time. A second alternative is using table variables as proc parameters, but performance is poor with large datasets. — Christopher J. Grace
– Christopher J. Grace, Commented Aug 1, 2018 at 21:08
Notice that Scenario C has special note: "Replace the CREATE TABLE #tempSessionC statements in your code with DELETE FROM dbo.soSessionC, to ensure a session is not exposed to table contents inserted by a previous session with the same session_id." — i-one
– i-one, Commented Aug 23, 2018 at 10:32

Christopher J. Grace · Accepted Answer · 2018-08-24 18:44:02Z

With the help of Microsoft Support, I was able to get the necessary details about ASP.NET Connection Pooling to answer this concern. It is true that ASP.NET threads will share the same SPID, but never at the same time. A thread gets assigned a connection only after the connection is no longer being used by the previous thread. Connection Pooling does not reduce the number of connections needed, it only reduces the number of times connections need to be opened and closed.

This is good documentation on Connection Pooling, although it does not make that distinction. https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling

Notice that Scenario C has special note: "Replace the CREATE TABLE #tempSessionC statements in your code with DELETE FROM dbo.soSessionC, to ensure a session is not exposed to table contents inserted by a previous session with the same session_id." – i-one

Since only one thread will be using the connection at a time, this is sufficient. If the table is not also deleted after being used, it will continue consuming memory (especially precious in Azure) until another thread happens to use a connection with the same SPID.

Collectives™ on Stack Overflow

Faster SQL temp table and table variable by using memory optimization

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related