SQL Server: Performance Insert Into vs Select Into
I am hearing different things from colleagues/research. What are good guidelines in performance for, Select Into vs Insert into when creating a temp table? I know difference is minimal for small tables.
Eg: Table has 20 columns, 50 million rows.
I've had DBAs state, Insert into is faster, since compiler/parser, does not need to find Column data types on the fly. Others stating Select into is faster.
We conducted performance testing, and seems select into is slightly faster.
What are good principles in figuring which is faster and why? I would think Microsoft would optimize to make insert into , just as fast, for careful programming.
Article states following.
https://www.mssqltips.com/sqlservertip/4523/sql-server-performance-of-select-into-vs-insert-into-for-temporary-tables/
"The INSERT...INTO command will reuse data pages which are created in cache for insert/update/delete operations. It will also truncate the table when it is dropped. The SELECT...INTO command will create new pages for table creation similar to regular tables and will physically remove them when the temporary table is dropped."
Question is, why wouldn't Microsoft optimize to make insert into as fast as select into?
We have over 500 stored procedures to write for data warehouse, and require good guidelines for temp usage.
This article does not really focus on performance and reasons, https://stackoverflow.com/questions/6947983/insert-into-vs-select-into, so asking question in more specific format.
Person in article mentioned good point:
"that's mostly because SQL Server knows that there is no contention for the destination table. The performance for insert into #temp with(tablock) select * from .. is roughly the same as the performance for select * into #temp from"
sql-server sql-server-2016 performance-tuning
asked 4 mins ago
Martin Smith51Martin Smith51
112
New contributor
Martin Smith51 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I am hearing different things from colleagues/research. What are good guidelines in performance for, Select Into vs Insert into when creating a temp table? I know difference is minimal for small tables.
Eg: Table has 20 columns, 50 million rows.
I've had DBAs state, Insert into is faster, since compiler/parser, does not need to find Column data types on the fly. Others stating Select into is faster.
We conducted performance testing, and seems select into is slightly faster.
What are good principles in figuring which is faster and why? I would think Microsoft would optimize to make insert into , just as fast, for careful programming.
Article states following.
https://www.mssqltips.com/sqlservertip/4523/sql-server-performance-of-select-into-vs-insert-into-for-temporary-tables/
"The INSERT...INTO command will reuse data pages which are created in cache for insert/update/delete operations. It will also truncate the table when it is dropped. The SELECT...INTO command will create new pages for table creation similar to regular tables and will physically remove them when the temporary table is dropped."
Question is, why wouldn't Microsoft optimize to make insert into as fast as select into?
We have over 500 stored procedures to write for data warehouse, and require good guidelines for temp usage.
This article does not really focus on performance and reasons, https://stackoverflow.com/questions/6947983/insert-into-vs-select-into, so asking question in more specific format.
Person in article mentioned good point:
"that's mostly because SQL Server knows that there is no contention for the destination table. The performance for insert into #temp with(tablock) select * from .. is roughly the same as the performance for select * into #temp from"
sql-server sql-server-2016 performance-tuning
asked 4 mins ago
Martin Smith51Martin Smith51
112
New contributor
Martin Smith51 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I am hearing different things from colleagues/research. What are good guidelines in performance for, Select Into vs Insert into when creating a temp table? I know difference is minimal for small tables.
Eg: Table has 20 columns, 50 million rows.
I've had DBAs state, Insert into is faster, since compiler/parser, does not need to find Column data types on the fly. Others stating Select into is faster.
We conducted performance testing, and seems select into is slightly faster.
What are good principles in figuring which is faster and why? I would think Microsoft would optimize to make insert into , just as fast, for careful programming.
Article states following.
https://www.mssqltips.com/sqlservertip/4523/sql-server-performance-of-select-into-vs-insert-into-for-temporary-tables/
"The INSERT...INTO command will reuse data pages which are created in cache for insert/update/delete operations. It will also truncate the table when it is dropped. The SELECT...INTO command will create new pages for table creation similar to regular tables and will physically remove them when the temporary table is dropped."
Question is, why wouldn't Microsoft optimize to make insert into as fast as select into?
We have over 500 stored procedures to write for data warehouse, and require good guidelines for temp usage.
This article does not really focus on performance and reasons, https://stackoverflow.com/questions/6947983/insert-into-vs-select-into, so asking question in more specific format.
Person in article mentioned good point:
"that's mostly because SQL Server knows that there is no contention for the destination table. The performance for insert into #temp with(tablock) select * from .. is roughly the same as the performance for select * into #temp from"
sql-server sql-server-2016 performance-tuning
asked 4 mins ago
Martin Smith51Martin Smith51
112
New contributor
Martin Smith51 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I am hearing different things from colleagues/research. What are good guidelines in performance for, Select Into vs Insert into when creating a temp table? I know difference is minimal for small tables.
Eg: Table has 20 columns, 50 million rows.
I've had DBAs state, Insert into is faster, since compiler/parser, does not need to find Column data types on the fly. Others stating Select into is faster.
We conducted performance testing, and seems select into is slightly faster.
What are good principles in figuring which is faster and why? I would think Microsoft would optimize to make insert into , just as fast, for careful programming.
Article states following.
https://www.mssqltips.com/sqlservertip/4523/sql-server-performance-of-select-into-vs-insert-into-for-temporary-tables/
"The INSERT...INTO command will reuse data pages which are created in cache for insert/update/delete operations. It will also truncate the table when it is dropped. The SELECT...INTO command will create new pages for table creation similar to regular tables and will physically remove them when the temporary table is dropped."
Question is, why wouldn't Microsoft optimize to make insert into as fast as select into?
We have over 500 stored procedures to write for data warehouse, and require good guidelines for temp usage.
This article does not really focus on performance and reasons, https://stackoverflow.com/questions/6947983/insert-into-vs-select-into, so asking question in more specific format.
Person in article mentioned good point:
"that's mostly because SQL Server knows that there is no contention for the destination table. The performance for insert into #temp with(tablock) select * from .. is roughly the same as the performance for select * into #temp from"
sql-server sql-server-2016 performance-tuning
sql-server sql-server-2016 performance-tuning
asked 4 mins ago
Martin Smith51Martin Smith51
112
New contributor
Martin Smith51 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 4 mins ago
Martin Smith51Martin Smith51
112
New contributor
Martin Smith51 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 4 mins ago
Martin Smith51Martin Smith51
112
New contributor
Martin Smith51 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 4 mins ago
Martin Smith51Martin Smith51
112
asked 4 mins ago
Martin Smith51Martin Smith51
112
112
New contributor
Martin Smith51 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Martin Smith51 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Martin Smith51 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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
});
}
});
Martin Smith51 is a new contributor. Be nice, and check out our Code of Conduct.
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%2f229698%2fsql-server-performance-insert-into-vs-select-into%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
Martin Smith51 is a new contributor. Be nice, and check out our Code of Conduct.
Martin Smith51 is a new contributor. Be nice, and check out our Code of Conduct.
Martin Smith51 is a new contributor. Be nice, and check out our Code of Conduct.
Martin Smith51 is a new contributor. Be nice, and check out our Code of Conduct.
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%2f229698%2fsql-server-performance-insert-into-vs-select-into%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
