SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2017, 2012 Express Edition Insert Select Performance Issue with very less data.


SQL Server 2017, 2012 Express Edition Insert Select Performance Issue with very less data.

Author
Message
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (390K reputation)SSC Guru (390K reputation)SSC Guru (390K reputation)SSC Guru (390K reputation)SSC Guru (390K reputation)SSC Guru (390K reputation)SSC Guru (390K reputation)SSC Guru (390K reputation)

Group: General Forum Members
Points: 390739 Visits: 42815
Sharat Gupta-482974 - Thursday, October 11, 2018 10:49 AM
Its getting late here. Lets meets tomorrow.

Regards
Sharat

You are on your own. To paraphrase the Terminator: "I won't be back!"


Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
andycadley
andycadley
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2576 Visits: 1611
Sharat Gupta-482974 - Thursday, October 11, 2018 10:32 PM

You have quoted, "944k microsecond on the table variable and 1,011k microseconds for the temp table compared to 5 seconds for the real table." As we can see temp table and table variable are close (1,011k microseconds = 1,011,000 microseconds = 1.011 seconds). Question is, why is real table 5 seconds, approximately 5 times? If we are testing on a standalone machine with database in single user mode, real table should be as fast?

Because SQL Server is smart enough not to actually persist the table variable/temp table to disk in those cases, because it knows how much available memory it has and that it can avoid the penalty by using spare capacity in RAM for short lived objects. In computing terms, writing to disk is fantastically slow.

ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)

Group: General Forum Members
Points: 165416 Visits: 21618
Sharat Gupta-482974 - Thursday, October 11, 2018 11:37 PM
andycadley - Thursday, October 11, 2018 10:40 PM
Sharat Gupta-482974 - Thursday, October 11, 2018 10:32 PM

You have quoted, "944k microsecond on the table variable and 1,011k microseconds for the temp table compared to 5 seconds for the real table." As we can see temp table and table variable are close (1,011k microseconds = 1,011,000 microseconds = 1.011 seconds). Question is, why is real table 5 seconds, approximately 5 times? If we are testing on a standalone machine with database in single user mode, real table should be as fast?

Because SQL Server is smart enough not to actually persist the table variable/temp table to disk in those cases, because it knows how much available memory it has and that it can avoid the penalty by using spare capacity in RAM for short lived objects. In computing terms, writing to disk is fantastically slow.

Agreed, writing to disk is slower than reading from disk. Real table has a physical storage on disk in user database, temp table and table variable have physical storage on disk in tempdb database. Writing to a table in user database (in single user mode) on a standalone machine should be as quick to writing to a table in tempdb database. tempdb is a shared resource, so lets have only one connection to server.

Lets run the test created by Grant like this:
1) Standalone machine.
2) Only one instance of SQL Server (default instance).
3) Only one connection to the server (close all other others), so that tempdb contention is not there.
4) User database is in single user mode.
5) Connect to the user database.
6) Run the test created by Grant.

Writing to real table should be as quick as writing to table in tempdb, as there is no tempdb contention. If its not, then why?

You know for certain that Microsoft WILL NOT REWARD YOU for disclosing whatever you've found.
Are there any other reasons why you might want to withhold your view definition?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)

Group: General Forum Members
Points: 165416 Visits: 21618
Sharat Gupta-482974 - Friday, October 12, 2018 3:16 AM
ChrisM@Work - Friday, October 12, 2018 3:02 AM
Sharat Gupta-482974 - Thursday, October 11, 2018 11:37 PM
andycadley - Thursday, October 11, 2018 10:40 PM
Sharat Gupta-482974 - Thursday, October 11, 2018 10:32 PM

You have quoted, "944k microsecond on the table variable and 1,011k microseconds for the temp table compared to 5 seconds for the real table." As we can see temp table and table variable are close (1,011k microseconds = 1,011,000 microseconds = 1.011 seconds). Question is, why is real table 5 seconds, approximately 5 times? If we are testing on a standalone machine with database in single user mode, real table should be as fast?

Because SQL Server is smart enough not to actually persist the table variable/temp table to disk in those cases, because it knows how much available memory it has and that it can avoid the penalty by using spare capacity in RAM for short lived objects. In computing terms, writing to disk is fantastically slow.

Agreed, writing to disk is slower than reading from disk. Real table has a physical storage on disk in user database, temp table and table variable have physical storage on disk in tempdb database. Writing to a table in user database (in single user mode) on a standalone machine should be as quick to writing to a table in tempdb database. tempdb is a shared resource, so lets have only one connection to server.

Lets run the test created by Grant like this:
1) Standalone machine.
2) Only one instance of SQL Server (default instance).
3) Only one connection to the server (close all other others), so that tempdb contention is not there.
4) User database is in single user mode.
5) Connect to the user database.
6) Run the test created by Grant.

Writing to real table should be as quick as writing to table in tempdb, as there is no tempdb contention. If its not, then why?

You know for certain that Microsoft WILL NOT REWARD YOU for disclosing whatever you've found.
Are there any other reasons why you might want to withhold your view definition?

Lets leave my test aside. Lets leave the view aside. A new question has arisen from test created by Grant. Grant ran his test and then quoted: Insert takes "944k microsecond on the table variable and 1,011k microseconds for the temp table compared to 5 seconds for the real table." As we can see temp table and table variable are close (1,011k microseconds = 1,011,000 microseconds = 1.011 seconds). Question is, why is real table 5 seconds, approximately 5 times? If we are testing on a standalone machine with database in single user mode, real table should be as fast?

SQL Server basics, already answered:
https://www.sqlservercentral.com/Forums/FindPost2002447.aspx

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Brandie Tarvin
Brandie Tarvin
SSC Guru
SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)

Group: General Forum Members
Points: 155281 Visits: 9804
Sharat Gupta-482974 - Thursday, October 11, 2018 10:08 PM
Hi Everyone,

I am planning to share the view definition with all of you in Encrypted form, how to do this?
Also as a next step, I will test on a Virtual Machine and share the results with all of you.

Regards,
Sharat

How does sharing the view in encrypted form help us? Encryption obscures everything. We wouldn't be able to see a thing, let along verify if there is a problem with your code.

Unless you also give us the key to unencrypt it. In which case, you might as well share the view in plain text with everyone.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Brandie Tarvin
Brandie Tarvin
SSC Guru
SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)

Group: General Forum Members
Points: 155281 Visits: 9804
Sharat Gupta-482974 - Friday, October 12, 2018 3:35 AM
ChrisM@Work - Friday, October 12, 2018 3:25 AM
Sharat Gupta-482974 - Friday, October 12, 2018 3:16 AM
ChrisM@Work - Friday, October 12, 2018 3:02 AM
Sharat Gupta-482974 - Thursday, October 11, 2018 11:37 PM
andycadley - Thursday, October 11, 2018 10:40 PM
Sharat Gupta-482974 - Thursday, October 11, 2018 10:32 PM

You have quoted, "944k microsecond on the table variable and 1,011k microseconds for the temp table compared to 5 seconds for the real table." As we can see temp table and table variable are close (1,011k microseconds = 1,011,000 microseconds = 1.011 seconds). Question is, why is real table 5 seconds, approximately 5 times? If we are testing on a standalone machine with database in single user mode, real table should be as fast?

Because SQL Server is smart enough not to actually persist the table variable/temp table to disk in those cases, because it knows how much available memory it has and that it can avoid the penalty by using spare capacity in RAM for short lived objects. In computing terms, writing to disk is fantastically slow.

Agreed, writing to disk is slower than reading from disk. Real table has a physical storage on disk in user database, temp table and table variable have physical storage on disk in tempdb database. Writing to a table in user database (in single user mode) on a standalone machine should be as quick to writing to a table in tempdb database. tempdb is a shared resource, so lets have only one connection to server.

Lets run the test created by Grant like this:
1) Standalone machine.
2) Only one instance of SQL Server (default instance).
3) Only one connection to the server (close all other others), so that tempdb contention is not there.
4) User database is in single user mode.
5) Connect to the user database.
6) Run the test created by Grant.

Writing to real table should be as quick as writing to table in tempdb, as there is no tempdb contention. If its not, then why?

You know for certain that Microsoft WILL NOT REWARD YOU for disclosing whatever you've found.
Are there any other reasons why you might want to withhold your view definition?

Lets leave my test aside. Lets leave the view aside. A new question has arisen from test created by Grant. Grant ran his test and then quoted: Insert takes "944k microsecond on the table variable and 1,011k microseconds for the temp table compared to 5 seconds for the real table." As we can see temp table and table variable are close (1,011k microseconds = 1,011,000 microseconds = 1.011 seconds). Question is, why is real table 5 seconds, approximately 5 times? If we are testing on a standalone machine with database in single user mode, real table should be as fast?

SQL Server basics, already answered:
https://www.sqlservercentral.com/Forums/FindPost2002447.aspx

Okay. What you are saying is: On a standalone machine, with database in single-user mode, writing to a real table in the user database is 5 times slower than writing to a temp table/table variable in the tempdb?

No. "Five times" is what Grant's query was. Actual time varies from box to box and circumstances to circumstances. Sometimes it might be 1 time slower, sometimes 10 times slower. It depends.


Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)

Group: General Forum Members
Points: 165416 Visits: 21618
Sharat Gupta-482974 - Friday, October 12, 2018 3:35 AM
ChrisM@Work - Friday, October 12, 2018 3:25 AM
Sharat Gupta-482974 - Friday, October 12, 2018 3:16 AM
ChrisM@Work - Friday, October 12, 2018 3:02 AM
Sharat Gupta-482974 - Thursday, October 11, 2018 11:37 PM
andycadley - Thursday, October 11, 2018 10:40 PM
Sharat Gupta-482974 - Thursday, October 11, 2018 10:32 PM

You have quoted, "944k microsecond on the table variable and 1,011k microseconds for the temp table compared to 5 seconds for the real table." As we can see temp table and table variable are close (1,011k microseconds = 1,011,000 microseconds = 1.011 seconds). Question is, why is real table 5 seconds, approximately 5 times? If we are testing on a standalone machine with database in single user mode, real table should be as fast?

Because SQL Server is smart enough not to actually persist the table variable/temp table to disk in those cases, because it knows how much available memory it has and that it can avoid the penalty by using spare capacity in RAM for short lived objects. In computing terms, writing to disk is fantastically slow.

Agreed, writing to disk is slower than reading from disk. Real table has a physical storage on disk in user database, temp table and table variable have physical storage on disk in tempdb database. Writing to a table in user database (in single user mode) on a standalone machine should be as quick to writing to a table in tempdb database. tempdb is a shared resource, so lets have only one connection to server.

Lets run the test created by Grant like this:
1) Standalone machine.
2) Only one instance of SQL Server (default instance).
3) Only one connection to the server (close all other others), so that tempdb contention is not there.
4) User database is in single user mode.
5) Connect to the user database.
6) Run the test created by Grant.

Writing to real table should be as quick as writing to table in tempdb, as there is no tempdb contention. If its not, then why?

You know for certain that Microsoft WILL NOT REWARD YOU for disclosing whatever you've found.
Are there any other reasons why you might want to withhold your view definition?

Lets leave my test aside. Lets leave the view aside. A new question has arisen from test created by Grant. Grant ran his test and then quoted: Insert takes "944k microsecond on the table variable and 1,011k microseconds for the temp table compared to 5 seconds for the real table." As we can see temp table and table variable are close (1,011k microseconds = 1,011,000 microseconds = 1.011 seconds). Question is, why is real table 5 seconds, approximately 5 times? If we are testing on a standalone machine with database in single user mode, real table should be as fast?

SQL Server basics, already answered:
https://www.sqlservercentral.com/Forums/FindPost2002447.aspx

Okay. What you are saying is: On a standalone machine, with database in single-user mode, writing to a real table in the user database is 5 times slower than writing to a temp table/table variable in the tempdb?

Yes, because a real table has to be written to disk. I'm surprised you are unaware of this. There's also a simple explanation for the write to table variable being faster than the write to temp table.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)

Group: General Forum Members
Points: 165416 Visits: 21618
Sharat Gupta-482974 - Friday, October 12, 2018 3:52 AM
ChrisM@Work - Friday, October 12, 2018 3:43 AM
Sharat Gupta-482974 - Friday, October 12, 2018 3:35 AM
ChrisM@Work - Friday, October 12, 2018 3:25 AM
Sharat Gupta-482974 - Friday, October 12, 2018 3:16 AM
ChrisM@Work - Friday, October 12, 2018 3:02 AM
Sharat Gupta-482974 - Thursday, October 11, 2018 11:37 PM
andycadley - Thursday, October 11, 2018 10:40 PM
Sharat Gupta-482974 - Thursday, October 11, 2018 10:32 PM

You have quoted, "944k microsecond on the table variable and 1,011k microseconds for the temp table compared to 5 seconds for the real table." As we can see temp table and table variable are close (1,011k microseconds = 1,011,000 microseconds = 1.011 seconds). Question is, why is real table 5 seconds, approximately 5 times? If we are testing on a standalone machine with database in single user mode, real table should be as fast?

Because SQL Server is smart enough not to actually persist the table variable/temp table to disk in those cases, because it knows how much available memory it has and that it can avoid the penalty by using spare capacity in RAM for short lived objects. In computing terms, writing to disk is fantastically slow.

Agreed, writing to disk is slower than reading from disk. Real table has a physical storage on disk in user database, temp table and table variable have physical storage on disk in tempdb database. Writing to a table in user database (in single user mode) on a standalone machine should be as quick to writing to a table in tempdb database. tempdb is a shared resource, so lets have only one connection to server.

Lets run the test created by Grant like this:
1) Standalone machine.
2) Only one instance of SQL Server (default instance).
3) Only one connection to the server (close all other others), so that tempdb contention is not there.
4) User database is in single user mode.
5) Connect to the user database.
6) Run the test created by Grant.

Writing to real table should be as quick as writing to table in tempdb, as there is no tempdb contention. If its not, then why?

You know for certain that Microsoft WILL NOT REWARD YOU for disclosing whatever you've found.
Are there any other reasons why you might want to withhold your view definition?

Lets leave my test aside. Lets leave the view aside. A new question has arisen from test created by Grant. Grant ran his test and then quoted: Insert takes "944k microsecond on the table variable and 1,011k microseconds for the temp table compared to 5 seconds for the real table." As we can see temp table and table variable are close (1,011k microseconds = 1,011,000 microseconds = 1.011 seconds). Question is, why is real table 5 seconds, approximately 5 times? If we are testing on a standalone machine with database in single user mode, real table should be as fast?

SQL Server basics, already answered:
https://www.sqlservercentral.com/Forums/FindPost2002447.aspx

Okay. What you are saying is: On a standalone machine, with database in single-user mode, writing to a real table in the user database is 5 times slower than writing to a temp table/table variable in the tempdb?

Yes, because a real table has to be written to disk. I'm surprised you are unaware of this. There's also a simple explanation for the write to table variable being faster than the write to temp table.

Real table has to written to disk, is it not required for temp table/table variable to be written to disk? All three have physical storage. Real table in user database and temp table/table variable in tempdb.

If memory is not limiting, neither table variable nor temp table will be written to disk - but a placeholder for temp table will be created. Table variable changes are also not logged.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
andycadley
andycadley
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2576 Visits: 1611
[quote]
Sharat Gupta-482974 - Friday, October 12, 2018 3:52 AM

Real table has to written to disk, is it not required for temp table/table variable to be written to disk? All three have physical storage. Real table in user database and temp table/table variable in tempdb.

No, it isn't required. SQL Server doesn't necessarily persist temporary objects to disk (and doesn't guarantee not to either). On a box with nothing else running and plenty of free RAM, it's incredibly likely it won't bother as it doesn't gain anything from doing so.

This is why all your problems point to a disk contention issue. You've not configured the max RAM available to SQL Server so it's probably fighting the OS disk cache for free memory. Your query is probably causing a lot of spillage into tempdb and causing a lot of unnecessary writes to disk already (hence the poor performance for the select alone). Adding the insert is quite possibly causing the machine to thrash, constantly swapping data in and out of RAM and killing the performance of your system overall.

All of these are things people could have helped you with if you just shared your view code. It's not going to be worth the vast sums of money you imagine. It really depends on whether you want people to help solve your actual problem or not.
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)

Group: General Forum Members
Points: 165416 Visits: 21618
Sharat Gupta-482974 - Friday, October 12, 2018 3:50 AM
Brandie Tarvin - Friday, October 12, 2018 3:39 AM
Sharat Gupta-482974 - Thursday, October 11, 2018 10:08 PM
Hi Everyone,

I am planning to share the view definition with all of you in Encrypted form, how to do this?
Also as a next step, I will test on a Virtual Machine and share the results with all of you.

Regards,
Sharat

How does sharing the view in encrypted form help us? Encryption obscures everything. We wouldn't be able to see a thing, let along verify if there is a problem with your code.

Unless you also give us the key to unencrypt it. In which case, you might as well share the view in plain text with everyone.

I have already shared the interface of the view, it has same columns as table "BhavcopyAll". I want to upload one encrypted file that will create an encrypted view in the database. Then you can run the test. How to create such an encrypted file, any idea (google was not of any help)?

A Question has arisen from the test created by Grant. Grant said, Insert takes ""944k microsecond on the table variable and 1,011k microseconds for the temp table compared to 5 seconds for the real table." As we can see temp table and table variable are close (1,011k microseconds = 1,011,000 microseconds = 1.011 seconds). Question is, why is real table 5 seconds, approximately 5 times? If we are testing on a standalone machine with database in single user mode, real table should be as fast?

Sharing only a part of the problem domain is as useless as sharing nothing at all. You have nothing to gain from Microsoft, nothing to lose by sharing the whole problem domain. Two things will change when you do:
1. You will get an explanation.
2. The number of people thinking you are a fool, a cheat or a troll will diminish.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search