SqlBulkCopy slow as molasses
Posted
by Chris
on Stack Overflow
See other posts from Stack Overflow
or by Chris
Published on 2010-04-22T16:27:41Z
Indexed on
2010/04/22
16:53 UTC
Read the original article
Hit count: 361
c#
|sqlbulkcopy
I'm looking for the fastest way to load bulk data via c#. I have this script that does the job but slow. I read testimonies that SqlBulkCopy is the fastest.
1000 records 2.5 seconds. files contain anywhere near 5000 records to 250k
What are some of the things that can slow it down?
Table Def:
CREATE TABLE [dbo].[tempDispositions](
[QuotaGroup] [varchar](100) NULL,
[Country] [varchar](50) NULL,
[ServiceGroup] [varchar](50) NULL,
[Language] [varchar](50) NULL,
[ContactChannel] [varchar](10) NULL,
[TrackingID] [varchar](20) NULL,
[CaseClosedDate] [varchar](25) NULL,
[MSFTRep] [varchar](50) NULL,
[CustEmail] [varchar](100) NULL,
[CustPhone] [varchar](100) NULL,
[CustomerName] [nvarchar](100) NULL,
[ProductFamily] [varchar](35) NULL,
[ProductSubType] [varchar](255) NULL,
[CandidateReceivedDate] [varchar](25) NULL,
[SurveyMode] [varchar](1) NULL,
[SurveyWaveStartDate] [varchar](25) NULL,
[SurveyInvitationDate] [varchar](25) NULL,
[SurveyReminderDate] [varchar](25) NULL,
[SurveyCompleteDate] [varchar](25) NULL,
[OptOutDate] [varchar](25) NULL,
[SurveyWaveEndDate] [varchar](25) NULL,
[DispositionCode] [varchar](5) NULL,
[SurveyName] [varchar](20) NULL,
[SurveyVendor] [varchar](20) NULL,
[BusinessUnitName] [varchar](25) NULL,
[UploadId] [int] NULL,
[LineNumber] [int] NULL,
[BusinessUnitSubgroup] [varchar](25) NULL,
[FileDate] [datetime] NULL
) ON [PRIMARY]
and here's the code
private void BulkLoadContent(DataTable dt)
{
OnMessage("Bulk loading records to temp table");
OnSubMessage("Bulk Load Started");
using (SqlBulkCopy bcp = new SqlBulkCopy(conn))
{
bcp.DestinationTableName = "dbo.tempDispositions";
bcp.BulkCopyTimeout = 0;
foreach (DataColumn dc in dt.Columns)
{
bcp.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
}
bcp.NotifyAfter = 2000;
bcp.SqlRowsCopied += new SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
bcp.WriteToServer(dt);
bcp.Close();
}
}
© Stack Overflow or respective owner