In the docs, we have example of how temporary tables can be replaced with in-memory tables and because a memory-optimized table:
- is stored only in memory, and has no component on disk
- involves no IO activity
- involves no tempdb utilization or contention
they said that:
Memory-optimization results in speed increases that are often 10 times faster or more.
I am wondering if our system is already using ram for tempdb database, which of the benefits will still apply? I guess they are testing temdb on hard drive or ssd and doubt getting such great results.
More details. One of the system mostly used table variable function is the one calculating access to specific entity. Then, the result is joined to various table to get the request data. In order to optimize the latter joins, I am storing the result of this function in temporary table and the results are nice.
But for really large volumes (for example when the function returns 1-2 millions of entities) the execution of the function itself is slow (which is again normal since we are inserting so many rows in table variable).
So, I am thinking to rewrite this function as stored procedure and insert the entities in memory table, hoping that the CRUD operation with the table will
10 times faster or more.