SqlBulkCopy is slow. What should I do?
up vote
0
down vote
favorite
I want to insert 2 million rows from a dataTable into SQL Server, but it takes a lot of time.
Here is my code
DataTable tbl_xml = new DataTable();
var xmlFilePath = (Server.MapPath("~/") + FileUpload1.FileName);//Location of the XML file.
DataSet dsCameraDetails = new DataSet();
dsCameraDetails.ReadXml(xmlFilePath);// Load XML in dataset
dtl = dsCameraDetails.Tables["Row"];
dtl.Columns.Add("state", typeof(string));
int countfile = dtl.Rows.Count;
SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["azmoonak"].ConnectionString);
SqlBulkCopy bulk2 = new SqlBulkCopy(con2);
bulk2.DestinationTableName = "tbl_xml";
try
{
foreach (DataColumn col in dtl.Columns)
bulk2.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
catch(Exception eee)
{
}
con2.Open();
try
{
bulk2.BulkCopyTimeout = 1000;
bulk2.WriteToServer(dtl);
}
catch(Exception eee)
{
}
con2.Close();
Actually I want to read data from a huge xml file and insert it into SQL Server in a short time. Is there any other way to do this?
c#
edited Nov 19 at 20:35
marc_s
569k12811001250
asked Nov 19 at 19:53
sh.h
2916
add a comment |
up vote
0
down vote
favorite
I want to insert 2 million rows from a dataTable into SQL Server, but it takes a lot of time.
Here is my code
DataTable tbl_xml = new DataTable();
var xmlFilePath = (Server.MapPath("~/") + FileUpload1.FileName);//Location of the XML file.
DataSet dsCameraDetails = new DataSet();
dsCameraDetails.ReadXml(xmlFilePath);// Load XML in dataset
dtl = dsCameraDetails.Tables["Row"];
dtl.Columns.Add("state", typeof(string));
int countfile = dtl.Rows.Count;
SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["azmoonak"].ConnectionString);
SqlBulkCopy bulk2 = new SqlBulkCopy(con2);
bulk2.DestinationTableName = "tbl_xml";
try
{
foreach (DataColumn col in dtl.Columns)
bulk2.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
catch(Exception eee)
{
}
con2.Open();
try
{
bulk2.BulkCopyTimeout = 1000;
bulk2.WriteToServer(dtl);
}
catch(Exception eee)
{
}
con2.Close();
Actually I want to read data from a huge xml file and insert it into SQL Server in a short time. Is there any other way to do this?
c#
edited Nov 19 at 20:35
marc_s
569k12811001250
asked Nov 19 at 19:53
sh.h
2916
1
What is your SQL technology? and SSIS packages for SQL are great/made for large data transfers like this.
– Brad
Nov 19 at 19:58
Doi not try to do the operation in C#. Do it in SQL on the SQL server.
– Christopher
Nov 19 at 20:02
1
Avoid those empty try-catches.
– LarsTech
Nov 19 at 20:39
Loading a huge XML file into memory may have a performance impact before you even start the bulk copy. Consider using an XmlReader to read the Xml sequentially without loading it all into memory, and wrap it with a custom IDataReader implementation that you can pass to SqlBulkCopy. Google will help you get started on the IDataReader implementation.
– Joe
Nov 19 at 20:42
I would suggest multithread also divide the process in small chunks of 100k to process each time
– Americo Arvani
Nov 19 at 21:25
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I want to insert 2 million rows from a dataTable into SQL Server, but it takes a lot of time.
Here is my code
DataTable tbl_xml = new DataTable();
var xmlFilePath = (Server.MapPath("~/") + FileUpload1.FileName);//Location of the XML file.
DataSet dsCameraDetails = new DataSet();
dsCameraDetails.ReadXml(xmlFilePath);// Load XML in dataset
dtl = dsCameraDetails.Tables["Row"];
dtl.Columns.Add("state", typeof(string));
int countfile = dtl.Rows.Count;
SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["azmoonak"].ConnectionString);
SqlBulkCopy bulk2 = new SqlBulkCopy(con2);
bulk2.DestinationTableName = "tbl_xml";
try
{
foreach (DataColumn col in dtl.Columns)
bulk2.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
catch(Exception eee)
{
}
con2.Open();
try
{
bulk2.BulkCopyTimeout = 1000;
bulk2.WriteToServer(dtl);
}
catch(Exception eee)
{
}
con2.Close();
Actually I want to read data from a huge xml file and insert it into SQL Server in a short time. Is there any other way to do this?
c#
edited Nov 19 at 20:35
marc_s
569k12811001250
asked Nov 19 at 19:53
sh.h
2916
I want to insert 2 million rows from a dataTable into SQL Server, but it takes a lot of time.
Here is my code
DataTable tbl_xml = new DataTable();
var xmlFilePath = (Server.MapPath("~/") + FileUpload1.FileName);//Location of the XML file.
DataSet dsCameraDetails = new DataSet();
dsCameraDetails.ReadXml(xmlFilePath);// Load XML in dataset
dtl = dsCameraDetails.Tables["Row"];
dtl.Columns.Add("state", typeof(string));
int countfile = dtl.Rows.Count;
SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["azmoonak"].ConnectionString);
SqlBulkCopy bulk2 = new SqlBulkCopy(con2);
bulk2.DestinationTableName = "tbl_xml";
try
{
foreach (DataColumn col in dtl.Columns)
bulk2.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
catch(Exception eee)
{
}
con2.Open();
try
{
bulk2.BulkCopyTimeout = 1000;
bulk2.WriteToServer(dtl);
}
catch(Exception eee)
{
}
con2.Close();
Actually I want to read data from a huge xml file and insert it into SQL Server in a short time. Is there any other way to do this?
c#
c#
edited Nov 19 at 20:35
marc_s
569k12811001250
asked Nov 19 at 19:53
sh.h
2916
edited Nov 19 at 20:35
marc_s
569k12811001250
asked Nov 19 at 19:53
sh.h
2916
edited Nov 19 at 20:35
marc_s
569k12811001250
edited Nov 19 at 20:35
marc_s
569k12811001250
edited Nov 19 at 20:35
marc_s
569k12811001250
569k12811001250
asked Nov 19 at 19:53
sh.h
2916
asked Nov 19 at 19:53
sh.h
2916
asked Nov 19 at 19:53
sh.h
2916
2916
1
What is your SQL technology? and SSIS packages for SQL are great/made for large data transfers like this.
– Brad
Nov 19 at 19:58
Doi not try to do the operation in C#. Do it in SQL on the SQL server.
– Christopher
Nov 19 at 20:02
1
Avoid those empty try-catches.
– LarsTech
Nov 19 at 20:39
Loading a huge XML file into memory may have a performance impact before you even start the bulk copy. Consider using an XmlReader to read the Xml sequentially without loading it all into memory, and wrap it with a custom IDataReader implementation that you can pass to SqlBulkCopy. Google will help you get started on the IDataReader implementation.
– Joe
Nov 19 at 20:42
I would suggest multithread also divide the process in small chunks of 100k to process each time
– Americo Arvani
Nov 19 at 21:25
add a comment |
1
What is your SQL technology? and SSIS packages for SQL are great/made for large data transfers like this.
– Brad
Nov 19 at 19:58
Doi not try to do the operation in C#. Do it in SQL on the SQL server.
– Christopher
Nov 19 at 20:02
1
Avoid those empty try-catches.
– LarsTech
Nov 19 at 20:39
Loading a huge XML file into memory may have a performance impact before you even start the bulk copy. Consider using an XmlReader to read the Xml sequentially without loading it all into memory, and wrap it with a custom IDataReader implementation that you can pass to SqlBulkCopy. Google will help you get started on the IDataReader implementation.
– Joe
Nov 19 at 20:42
I would suggest multithread also divide the process in small chunks of 100k to process each time
– Americo Arvani
Nov 19 at 21:25
1
1
What is your SQL technology? and SSIS packages for SQL are great/made for large data transfers like this.
– Brad
Nov 19 at 19:58
What is your SQL technology? and SSIS packages for SQL are great/made for large data transfers like this.
– Brad
Nov 19 at 19:58
Doi not try to do the operation in C#. Do it in SQL on the SQL server.
– Christopher
Nov 19 at 20:02
Doi not try to do the operation in C#. Do it in SQL on the SQL server.
– Christopher
Nov 19 at 20:02
1
1
Avoid those empty try-catches.
– LarsTech
Nov 19 at 20:39
Avoid those empty try-catches.
– LarsTech
Nov 19 at 20:39
Loading a huge XML file into memory may have a performance impact before you even start the bulk copy. Consider using an XmlReader to read the Xml sequentially without loading it all into memory, and wrap it with a custom IDataReader implementation that you can pass to SqlBulkCopy. Google will help you get started on the IDataReader implementation.
– Joe
Nov 19 at 20:42
Loading a huge XML file into memory may have a performance impact before you even start the bulk copy. Consider using an XmlReader to read the Xml sequentially without loading it all into memory, and wrap it with a custom IDataReader implementation that you can pass to SqlBulkCopy. Google will help you get started on the IDataReader implementation.
– Joe
Nov 19 at 20:42
I would suggest multithread also divide the process in small chunks of 100k to process each time
– Americo Arvani
Nov 19 at 21:25
I would suggest multithread also divide the process in small chunks of 100k to process each time
– Americo Arvani
Nov 19 at 21:25
add a comment |
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53381717%2fsqlbulkcopy-is-slow-what-should-i-do%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53381717%2fsqlbulkcopy-is-slow-what-should-i-do%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
What is your SQL technology? and SSIS packages for SQL are great/made for large data transfers like this.
– Brad
Nov 19 at 19:58
Doi not try to do the operation in C#. Do it in SQL on the SQL server.
– Christopher
Nov 19 at 20:02
1
Avoid those empty try-catches.
– LarsTech
Nov 19 at 20:39
Loading a huge XML file into memory may have a performance impact before you even start the bulk copy. Consider using an XmlReader to read the Xml sequentially without loading it all into memory, and wrap it with a custom IDataReader implementation that you can pass to SqlBulkCopy. Google will help you get started on the IDataReader implementation.
– Joe
Nov 19 at 20:42
I would suggest multithread also divide the process in small chunks of 100k to process each time
– Americo Arvani
Nov 19 at 21:25