Inserts slow down as table grows(partitioned table).

Question

Inserts slow down as table grows(partitioned table).

mtaylor7210 63514

Mr or Mrs. 500

Points: 508
More actions
January 16, 2017 at 4:51 am

#339422

I have a table that is partitioned by month, I'm loading the table from another table in batches of 1 million.
Starting from an empty destination the inserts take about 20 seconds per 1 million rows.
However by about 60million rows inserted the time jumps to 40 seconds per 1 million rows, and by the time the table is 100million rows its closer to 2 minutes.
As another note the data being inserted is sequential based on the partition column.

Viewing 4 posts - 1 through 3 (of 3 total)

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

frederico_fonseca SSCoach Points: 16025 More actions · Answer 1

Looks like normal issues with loading onto cluster indexes.

As the table is partitioned the fastest way would be to populate staging tables per partition and then using the alter table ... switch partition to move the data onto the final table. Optionally even do the load in parallel for faster processing.

See https://www.brentozar.com/sql/table-partitioning-resources/ for some resources on this subject.

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 2

mtaylor7210 63514 - Monday, January 16, 2017 4:51 AM
I have a table that is partitioned by month, I'm loading the table from another table in batches of 1 million.
Starting from an empty destination the inserts take about 20 seconds per 1 million rows.
However by about 60million rows inserted the time jumps to 40 seconds per 1 million rows, and by the time the table is 100million rows its closer to 2 minutes.
As another note the data being inserted is sequential based on the partition column.

Is the leading column of the Clustered Index the partition column? If not, then you've probably identified your problem. When I did such a transfer (I limited by month rather than by a fixed row count) to a partitioned table (>600GB), I saw no such slowdown. I was also sure to make it so that all of the non-Clustered indexes were partition aligned.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dastagiri16 Hall of Fame Points: 3544 More actions · Answer 3

This reply was modified 3 years, 9 months ago by dastagiri16.
This reply was modified 3 years, 9 months ago by dastagiri16.
This reply was modified 3 years, 9 months ago by dastagiri16.