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(); } } |