Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

I have a class that uses the SqlBulkCopy to bulk insert data into a SQL Server database. My original implementation just passed the m_buffer (which is a reference to a class that implements IDataReader) directly into the WriteToServer. Now a new requirement has been added that, upon failure, the process should retry the Bulk Insert command. Why you ask? Because one of our clients get intermittent SQL exceptions because they have crappy hardware.

Okay, I digress, since IDataReader is read-once I needed to come up with something different. I chose to load the IDataReader into a DataTable first and then pass the DataTable into the WriteToServer method. Now my problem is that I will be using more memory and since I do not know anything about the data (the number of columns nor the amount of data in each row) I wanted to attempt to make the code safe from an OutOfMemoryException.

Unfortunately I could not find a good way to determine the amount of available memory, so instead I chose to dump the data every 50,000 rows or when the memory allocation reaches an arbitrary number (1 gig), whichever comes first.

Does this sound like a sound approach or is there a better way?

// m_buffer is a read-once cache (implements IDataReader) that pulls 
// data from an external source as needed so it uses very little memory.
// My original implementation just used m_buffer as the parameter of 
// WriteToServer but now I have to add retry logic into the process.
// The retry logic is in the PutDataIntoDatabase method.

const long MAX_MEMORY_TO_USE = 1073741824;  // 1 gig

DataTable dataTable = new DataTable(m_tableName);
foreach (DataField d in m_buffer.GetColumns())
    dataTable.Columns.Add(new DataColumn(d.FieldName, d.FieldType));

while (m_buffer.Read())
{
    DataRow row = dataTable.NewRow();
    for (int i = 0; i < m_buffer.FieldCount; i++)
        row[i] = m_buffer.GetValue(i);

    dataTable.Rows.Add(row);

    long totalMemory = GC.GetTotalMemory(false);
    if (rowCount++ > 50000 || totalMemory > MAX_MEMORY_TO_USE)
    {
        PutDataIntoDatabase(dataTable);
        dataTable.Clear();
        rowCount = 0;
    }
}

if (dataTable.Rows.Count > 0)
    PutDataIntoDatabase(dataTable);
share|improve this question
    
What if you could tell how much RAM is available? If taskmgr.exe knows that info, then there must be a way. veskokolev.blogspot.com/2008/03/… –  Leonid Dec 5 '12 at 17:57
    
How does the source (that implements IDataReader) look like? Is a network source, a file source, or anything else? –  almaz Dec 5 '12 at 18:49
    
@Leonid I would need to know the amount of memory available to that process. It would not have to be exact either, a rough estimate would work. But it does not seem that that is even possible. –  Gene S Dec 5 '12 at 19:31
    
@almaz It could be anything although it is unlikely to be a network source but you never know (I am constantly amazed at the "crazy" things clients want to do). Most often it will be a database but could also be a file. –  Gene S Dec 5 '12 at 19:34
    
I would start the chunk size at 64k and then change it adaptively - e.g. divide it by two if you do encounter an out of memory error and try to double it randomly once in a while. There must be existing known algorithms for this sort of thing; I just do not know what they are called. –  Leonid Dec 5 '12 at 19:40
add comment

1 Answer

up vote 2 down vote accepted

Based on your comments it looks like your code is actually some sort of library used by different clients in different environments. If that's the case I would rather let clients define the batch size they want rather than writing tricks around memory management on your own.

If you do need to manage the memory - I would suggest to reduce the frequency of GC.GetTotalMemory calls, e.g. check it once per 1000 rows or so. Bear in mind that since your component is not the only one in the application it may turn out that application has already consumed 1GB of RAM thus causing you to reduce the batch size to minimum.

share|improve this answer
    
You could also detect how much RAM the user has at run time and drop the batch size if it is below some threshold. Not as effective as a setting, but means one less setting. –  Brian Dec 6 '12 at 17:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.