Reputation: 829
We have a big list around 100000 records and want to insert it into a sql table.
What are we doing is; converting that list into data table and passing datatable to SqlBulkcopy method.
This conversion from list to Datatable taking more time. Tried using Parallel but as Datatable is not thread safe so avoided that.
Adding sample poc code which generates integer list and insert it into temp table
static void Main(string[] args)
{
List<int> valueList = GenerateList(100000);
Console.WriteLine("Starting with Bulk Insert ");
DateTime startTime = DateTime.Now;
int recordCount = BulkInsert(valueList);
TimeSpan ts = DateTime.Now.Subtract(startTime);
Console.WriteLine("Bulk insert for {0} records in {1} miliseconds.-> ", recordCount, ts.Milliseconds);
Console.WriteLine("Done.");
Console.ReadLine();
}
private static int BulkInsert(List<int> valueList)
{
SqlBulkHelper sqlBulkHelper = new SqlBulkHelper();
var eventIdDataTable = CreateIdentityDataTable(valueList, "SqlTable", "Id");
return FillBulkPoundTable(eventIdDataTable, "#SqlTable");
}
private static List<int> GenerateList(int size)
{
return Enumerable.Range(0, size).ToList();
}
private static DataTable CreateIdentityDataTable(List<int> ids, string dataTableName, string propertyName)
{
if (ids == null) return null;
using (var dataTable = new DataTable(dataTableName))
{
dataTable.Locale = CultureInfo.CurrentCulture;
var dtColumn = new DataColumn(propertyName, Type.GetType("System.Int32"));
dataTable.Columns.Add(dtColumn);
foreach (int id in ids)
{
DataRow row = dataTable.NewRow();
row[propertyName] = id;
dataTable.Rows.Add(row);
}
return dataTable;
}
}
private static int FillBulkPoundTable(DataTable dataTable, string destinationTableName)
{
int totalInsertedRecordCount = 0;
using (SqlConnection _connection = new SqlConnection(CongifUtil.sqlConnString))
{
string sql =
@"If object_Id('tempdb..#EventIds') is not null drop table #EventIds
CREATE TABLE #EventIds(EvId int) ";
_connection.Open();
using (var command = new SqlCommand(sql, _connection))
{
command.ExecuteNonQuery();
}
using (var sqlBulkCopy = new SqlBulkCopy(_connection))
{
sqlBulkCopy.BulkCopyTimeout = 0;
sqlBulkCopy.DestinationTableName = destinationTableName;
sqlBulkCopy.WriteToServer(dataTable);
}
using (var command = new SqlCommand(sql, _connection))
{
command.CommandText = "Select Count(1) as RecordCount from #EventIds";
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
totalInsertedRecordCount = Convert.ToInt32(reader["RecordCount"]);
}
}
}
}
return totalInsertedRecordCount;
}
Currently it is taking around 8 seconds but we need to make it more faster. Reason is our target is to insert 900,000 records which will be devided into 100,000 batch each.
Can you give us any hint how can we make it perfect and faster?
PS. Tried with Dapper insert too but it is not faster than BulkCopy.
Upvotes: 0
Views: 2351
Reputation: 11347
From what I understand, you try to insert with a BatchSize of 100000
. Higher is not always better.
Try to lower this amount to 5,000
instead and check for the performance difference.
You increase the amount of database round-trip but it may also go faster (Too much factor such as the row size are involved here)
Using the SqlBulkCopyOptions.TableLock
will improve your insert performance.
using (var sqlBulkCopy = new SqlBulkCopy(_connection, SqlBulkCopyOptions.KeepIdentity))
Upvotes: 0
Reputation: 481
First Covert your list into XML something like
List<int> Branches = new List<int>();
Branches.Add(1);
Branches.Add(2);
Branches.Add(3);
XElement xmlElements = new XElement("Branches", Branches.Select(i => new
XElement("branch", i)));
Then pass the xml to a SP as parameter and insert it directly to your table, Example :
DECLARE @XML XML
SET @XML = '<Branches>
<branch>1</branch>
<branch>2</branch>
<branch>3</branch>
</Branches>'
DECLARE @handle INT
DECLARE @PrepareXmlStatus INT
EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @XML
SELECT * FROM OPENXML(@handle, '/Branches/branch', 2)
WITH (
branch varchar
)
EXEC sp_xml_removedocument @handle
Upvotes: 0