Insert VS Large Table

Question

Post reply

Insert VS Large Table

vignesh.ms

Hall of Fame

Points: 3456
More actions
October 29, 2014 at 7:18 am

#300525

Hi There,

Will table size affect the performance of the insert statement? If so how?

Help me to understand,

Inserting data into large table VS Inserting data into small sized or empty table

Share me the links to to understand this scenario.

Thanks
October 29, 2014 at 8:00 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

I don't think that the table's size is important as other factors. For example If I have a huge table with clustered index that is based on an identity column or the inserted time, then the insert will be faster then insert into much smaller table that has many page splits. Another factor that I think is more important is how many indexes the table has. There is a good chance that other readers will come up with other factors that are more important.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Phil Parkin SSC Guru Points: 245813 More actions · Answer 2

Yes – the clustered index or PK will be an important consideration. Will existing data pages need to be reshuffled on disk to accommodate the data being inserted?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 3

I guess it depends most if the table is a heap or if it has a clustered index.

Additional indexes will slow down the insert even more, especially in larger indexes.

Other factors:

* page splits and fragmentation

* data file growth

* logging

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

Everyone else has largely nailed it. For the most part, adding a row to a table costs the same if it's an empty table or one that has one million rows.... DEPENDING on all the other factors around the table, foreign key constraints, indexes, etc., etc. So to really fully answer the question, you'd need to define what all those other factors are before you simply said that bigger is slower.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Bill Talada SSChampion Points: 11956 More actions · Answer 5

I just learned the hard way how to "move" rows from one table to another 3x faster. I'm migrating rows from a heap table with a nonclustered PK on a guid. I was doing a "select top(100) guidkey from Attachments", and using the set of keys to perform the convertAndInsertIntoNewTable/deleteFromOldTable process. Turns out the select top(100) was in guid order instead of heap row order. The disk head was jumping all over the heap to get the next 100 rows. I added a nonPK column to the SELECT to force heap order and now its fast.

My mindset was on "Heap" and I totally forgot sqlserver would choose a covering index even though I had no ORDER BY clause.