Does the size of a table impact the performance of insert query in SQL Server?

Question

I need to improve performance of INSERT INTO query to a table which has 1 billion rows. This table contains a clustered index primary key.

One suggestion is to reduce the table data by deleting (copying to archive table) old records and keep most recent records in the table. This will reduce data from 1 billion to 2 million. Will this approach increase data write process?

Are there any other way to increase record insert process?

Note: this INSERT INTO query is in a complex stored procedure and the execution plan points to this INSERT statement taking a certain amount of time.

Answer 1

Simplistically, reducing the size of the table will not have much impact on performance. There are some cases where it could make a difference.

If the clustered index primary key is not ordered, then you have a fragmentation problem. That means that inserts are likely to be splitting pages and rewriting them.

The "good" news is that in the existing table, your pages are probably already fragmented, so you probably have few full pages. So splitting is less likely. This is "good" in quotes because it means that you have lots of wasted space, which is inefficient for queries.

If you remove the excess rows and compact (defrag) the table, then you will have some advantages. The biggest is that the data will probably fit into memory -- a big performance advantage.

I would recommend that you fix the table because the extra rows are probably hurting query performance. Given the volume of data, I would suggest a truncate/re-insert approach:

select t.*
into temp_t
from t
where <rows to keep logic here>;

truncate table t;   -- be sure you have a backup!

insert into t
    select *
    from temp_t;

This will be much faster than trying to delete 99.9% of the rows (unless you happen to have a partitioned table where you can simply drop partitions).

If you want to keep the old data, you might find a way to partition the table. Of course, your queries would have to use the partitioning key to access the "valid" rows rather than the archive.