0

I am moving data from one set of source Sql Server databases to another set of destination SQL Server databases. The initial proof of concept was copying the data over as is. Next step was processing each record to "fictionalize" the data where marked in a configuration file (e.g. First Name, Last Name, Address, SSN, etc).

The Windows virtual machine I am running on has 4GB of memory. I am seeing very long running times just trying to copy the data from a source database table to its destination table. I have a few tables that are upwards of 100 million records but most tables are under a million. It looks like my process is hitting a lot of hard faults and using up all available memory.

I could use a SqlDataReader but I need to update the data in each row before writing out to the destination database.. So I am using a DataTable that is obviously reading all records into memory. But I am not sure the right way to process records in batches (read first 1000, fictionalize the data, write to destination, then read next 1000 records, etc)? Is there a deterministic way to read records from a table via C#? After a lot of googling I could not find anything definitive.

Code is:

DataTable dataTable = new DataTable();

SqlCommand cmd = new SqlCommand("SELECT * FROM " + tableName, srcConn);

using (SqlDataAdapter da = new SqlDataAdapter(cmd))

{

    da.Fill(dataTable);

    da.Dispose();

}

CopyAndFictionalizeTable(tableName, fields, dataTable);

var transaction = destConn.BeginTransaction();
var options = SqlBulkCopyOptions.KeepNulls | SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.TableLock;
using (SqlBulkCopy bulkData = new SqlBulkCopy(destConn, options, transaction))
{

    bulkData.BatchSize = 5000;
    bulkData.BulkCopyTimeout = 0;
    bulkData.DestinationTableName = tableName;
    bulkData.WriteToServer(dataTable);
}
transaction.Commit();
dbc
  • 104,963
  • 20
  • 228
  • 340
Michael Batchelder
  • 71
  • 8
  • _"Is there a deterministic way to read records from a table"_ - `ORDER BY ID`? – stuartd Oct 23 '19 at 17:33
  • There is nothing wrong with what you are doing, but you could probably do some optimizations. Check out this answer that talks about the FastMember package. https://stackoverflow.com/questions/47207439/is-there-a-faster-way-to-use-sqlbulkcopy-than-using-a-datatable – William Xifaras Oct 23 '19 at 20:45

0 Answers0