SqlBulkCopy Sample
I'm having some network connectivity issues and it's pissing me off b/c I can't figure it out. Anyway, I've got some pretty impressive numbers so far. I'm testing it with all of the SqlBulkCopyOptions(CheckConstriants, Defualts, FireTriggers, KeepIdentity, KeepNulls, TableLock & UserInternalTransaction). I have some pretty thorough analysis and I'm trying it with both the Keys on and off on the DB Side.
Anyway, here's a quick sample:
---
//Declare a few variables...
private SqlConnection cn;
private SqlDataAdapter da;
private SqlCommand cmd;
private DataSet ds;
//Instantiate everything we need
private void Form1_Load(object sender, EventArgs e)
{
SqlConnection.ClearAllPools();//Cool new feature!
cn =
new SqlConnection("integrated security=SSPI;data source=x;initial catalog=x");
cmd =
new SqlCommand("SELECT TOP 200000 * FROM Source", cn);
da =
new SqlDataAdapter(cmd);
}
//Load the DataSet/DataTable and Bind it to a DataGridView control
private void btnLoad1_Click(object sender, EventArgs e)
{
ds =
new DataSet();
try
{
DateTime dt = DateTime.Now;
da.Fill(ds,
"MyTable");
TimeSpan ts = DateTime.Now - dt;
lblStart.Text = ts.TotalSeconds.ToString();
}
catch (SqlException ex)
{
System.Diagnostics.
Debug.Assert(false, ex.ToString());
}
finally { cn.Close(); }
}
private void btnLoadDb_Click(object sender, EventArgs e)
{
System.Data.SqlClient.
SqlBulkCopy bc = new SqlBulkCopy(cn);
bc.DestinationTableName=
"Destination";
try
{
DateTime dt = DateTime.Now;
lblStart.Text =
"Start Time: " + DateTime.Now.ToLongTimeString();
cn.Open();
bc.WriteToServer(ds.Tables[0]);
TimeSpan ts = DateTime.Now - dt;
lblDone.Text = “End Time“ + ts.TotalSeconds.ToString();
}
catch (SqlException ex)
{
System.Diagnostics.
Debug.Assert(false, ex.ToString());
}
finally { cn.Close(); }
}
For the first fill on 100k records, it was 2.65 seconds for the initial fill and 4.746 on the BulkCopy (keys off). That went up to 6.108 and 12.2576 respectively for the same with the Keys on. The difference isn't the BulkCopy though b/c the Select took a lot longer - probably running low on RAM. Gonna do a reboot now and see if I can finish up ;-)