Fast SQL’08 Geometry Insertion

If you’re inserting SQL’08 Native Spatial Datatypes into a brand new table, favor SqlBulkCopy over Insert. Its ridiculously faster. Below is some reduced code to show the basic idea:

// use minimal transaction logging during upload
new SqlCommand("alter database [" + dbName + "] SET RECOVERY BULK_LOGGED", connection).ExecuteNonQuery();

// create and a datatable of points
DataTable dt = new DataTable();
dt.Columns.Add("Shape", typeof(SqlGeometry));

// populate your SqlGeometry
dt = magicFunction_fillMyDataTableWithGeometry();

// copy data, use batches and tablelock to ensure small minimal transaction log sizes
using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString(dbName), SqlBulkCopyOptions.TableLock))
{
    sbc.DestinationTableName = tableName;
    sbc.BatchSize = 10000;
    sbs.ColumnMappings.Add("Shape", "Shape");
    sbc.WriteToServer(dt);
    sbc.Close();
}

// use normal transaction logging after upload
new SqlCommand("alter database [" + dbName + "] SET RECOVERY FULL", connection).ExecuteNonQuery();