October 29, 2014 at 7:18 am
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 7:28 am
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/
October 29, 2014 at 7:59 am
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?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 29, 2014 at 8:00 am
This was removed by the editor as SPAM
October 29, 2014 at 8:03 am
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
October 29, 2014 at 8:37 am
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 30, 2014 at 1:17 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply