SQL Server – Why SqlBulkCopy Slows with Table Row Count

bulk-insertsql server

I'm using SqlBulkCopy to insert a lot of data (10000/second at peek) into a database. And when the table starts empty, each bulk insertion takes like 300 milliseconds. But after a day when the records count become more than 20 millions, the insertion time becomes 2.5 seconds per 10K records. And this time increases with the count of records.

I have only a simple table, with no indexes except an identity clustered primary key.

I thought that this behavior is an append-only behavior and won't be affected by the table size. Like throwing a pile of litter on yet a huge pile of litter. That shouldn't be affected by the size.

What do I miss here?

Update: Table's script created via SSMS is:

CREATE TABLE [pardis].[DeliveryReports](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [PardisId] [bigint] NOT NULL,
    [Status] [bigint] NOT NULL,
    [PartId] [bigint] NOT NULL,
    [ShortCode] [varchar](100) NOT NULL,
    [PhoneNumber] [varchar](100) NOT NULL,
    [DateTimeReceived] [datetime] NOT NULL,
    [PersianDateTimeReceived] [varchar](100) NOT NULL,
    [DateTimeSentToCp] [datetime] NULL,
    [PersianDateTimeSentToCp] [varchar](100) NULL,
    [DeliveringToCpResult] [nvarchar](max) NULL,
    [IsProcessing] [bit] NOT NULL CONSTRAINT [DF_DeliveryReports_IsProcessing_1]  DEFAULT ((0)),
    [IsSentToCp] [bit] NOT NULL CONSTRAINT [DF_DeliveryReports_IsSentToCp]  DEFAULT ((0)),
 CONSTRAINT [PK_DeliveryReports] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Update 2: This is the execution plan for inserting 1 record via SSMS. As you can see, no hidden cost exists in inserting records in the table. Please note that this execution plan is after deleting the primary key index to prevent any index from affecting the performance.

enter image description here

Best Answer

Why you write that you don't make any index ? What is PK_DeliveryReports in your opinion ? As for me - the clu of your problem is just rebuilding of PK. Try to use partitions to your table - or drop this index (PK) and rebuild it after the load - should be quicker (of course if you don't need index during the load for other purposes).

Related Question