In memory table vs temporary table - benefits if temdb in already on ram disk
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
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.
sql-server sql-server-2016 tempdb in-memory-database
asked 1 min ago
gotqngotqn
1,55052653
add a comment |
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.
sql-server sql-server-2016 tempdb in-memory-database
asked 1 min ago
gotqngotqn
1,55052653
add a comment |
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.
sql-server sql-server-2016 tempdb in-memory-database
asked 1 min ago
gotqngotqn
1,55052653
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.
sql-server sql-server-2016 tempdb in-memory-database
sql-server sql-server-2016 tempdb in-memory-database
asked 1 min ago
gotqngotqn
1,55052653
asked 1 min ago
gotqngotqn
1,55052653
asked 1 min ago
gotqngotqn
1,55052653
asked 1 min ago
gotqngotqn
1,55052653
asked 1 min ago
gotqngotqn
1,55052653
1,55052653
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f235303%2fin-memory-table-vs-temporary-table-benefits-if-temdb-in-already-on-ram-disk%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f235303%2fin-memory-table-vs-temporary-table-benefits-if-temdb-in-already-on-ram-disk%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
