4

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

  • Provide the execution plan. Probably, there is a non-append-only component to the inserts. Maybe an index that causes randomly placed writes. Eventually, the buffer pool cannot buffer those write anymore and performance tanks big time. Append-only inserts never get slower over time. – usr Feb 17 '16 at 10:34
  • @usr, I checked the execution plan for 1 record, and it seems that no hidden cost exists. I don't know how to see the execution plan of bulk insert though. – Saeed Neamati Feb 17 '16 at 11:16
  • 1
    Two more ideas: Auto-grow and changing size of rows being inserted (becoming bigger). You need to investigate that. – usr Feb 17 '16 at 11:16
  • You can capture the bulk plan using SQL Profiler and the Showplan XML event. But it will be just as boring. The 1 row plan is sufficient here. – usr Feb 17 '16 at 11:16
-2

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).

  • Yeah, you're right. I removed primary key index altogether, but no change. I think something else might be the cause. – Saeed Neamati Feb 17 '16 at 9:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.