如何从DataTable获取行大小及动态确定批量复制批次大小以避免超时？

Great question—dealing with timeout issues during bulk inserts is super common, especially when working with rows that have highly variable sizes. Let's break down solutions to your two core problems:

First, let's clear up why your initial approach failed: Marshal.SizeOf and sizeof only work with unmanaged value types. An object[] is a managed array, so there's no meaningful "unmanaged size" to compute. Instead, you need to manually calculate the byte size of each row by summing up the size of every column's value.

Here's a practical method to do this, accounting for common data types:

private long CalculateDataRowSize(DataRow row)
{
    long totalSize = 0;
    foreach (DataColumn col in row.Table.Columns)
    {
        var value = row[col];
        if (value == DBNull.Value)
        {
            // Null values take minimal space (varies by database), so we'll skip counting them here
            continue;
        }

        switch (col.DataType.FullName)
        {
            case "System.Int32":
                totalSize += 4;
                break;
            case "System.Int64":
                totalSize += 8;
                break;
            case "System.DateTime":
                totalSize += 8;
                break;
            case "System.Boolean":
                totalSize += 1;
                break;
            case "System.String":
                string strValue = (string)value;
                // Use UTF-16 (default for .NET strings) or switch to Encoding.UTF8.GetByteCount(strValue) if your DB uses UTF-8
                totalSize += strValue.Length * 2;
                break;
            case "System.Byte[]":
                byte[] byteValue = (byte[])value;
                totalSize += byteValue.Length;
                break;
            // Add cases for other types (like decimal, float) as needed for your schema
            default:
                // Fallback for unknown types—you can add an estimate here if needed
                break;
        }
    }
    return totalSize;
}

There are a few battle-tested approaches to adjust batch size automatically based on your data and system:

1. Adaptive Retry with Batch Reduction #

Start with a reasonable initial batch size (e.g., 1000 rows), and if a timeout occurs, halve the batch size and retry. Once you find a size that works, stick with it for subsequent batches. This is simple and effective for unpredictable data:

public void BulkInsertWithAdaptiveBatch(DataTable dataTable, string connectionString)
{
    int batchSize = 1000; // Starting guess
    const int minBatchSize = 10; // Don't go smaller than this to avoid excessive round-trips

    while (dataTable.Rows.Count > 0)
    {
        try
        {
            int rowsToInsert = Math.Min(batchSize, dataTable.Rows.Count);
            DataTable batch = dataTable.Clone();
            
            // Populate the batch
            for (int i = 0; i < rowsToInsert; i++)
            {
                batch.ImportRow(dataTable.Rows[0]);
                dataTable.Rows.RemoveAt(0);
            }

            // Execute bulk insert
            using (var bulkCopy = new SqlBulkCopy(connectionString))
            {
                bulkCopy.DestinationTableName = "YourTargetTable";
                bulkCopy.WriteToServer(batch);
            }
        }
        catch (SqlException ex) when (ex.Message.Contains("Timeout period elapsed"))
        {
            if (batchSize <= minBatchSize)
                throw; // Can't reduce further—rethrow the error to handle upstream

            batchSize /= 2;
            Console.WriteLine($"Timeout hit! Reducing batch size to {batchSize}");
        }
    }
}

2. Size-Capped Batches #

Calculate the average row size (using the method above), then set a maximum total batch size (e.g., 10MB) and compute how many rows fit into that limit. This ensures you don't send overly large payloads:

public int CalculateOptimalBatchSize(DataTable dataTable, long maxBatchSizeBytes = 10 * 1024 * 1024) // 10MB limit
{
    if (dataTable.Rows.Count == 0)
        return 0;

    // Use a sample of rows to calculate average size (avoids processing all rows upfront)
    int sampleSize = Math.Min(100, dataTable.Rows.Count);
    long totalSampleSize = 0;
    for (int i = 0; i < sampleSize; i++)
    {
        totalSampleSize += CalculateDataRowSize(dataTable.Rows[i]);
    }
    long averageRowSize = totalSampleSize / sampleSize;

    if (averageRowSize == 0)
        return 1000; // Fallback if all sampled rows are empty/null

    int optimalBatchSize = (int)(maxBatchSizeBytes / averageRowSize);
    // Ensure batch size is within sensible bounds
    return Math.Clamp(optimalBatchSize, 1, dataTable.Rows.Count);
}

3. System Resource-Based Adjustment #

You can also tweak batch size based on available system resources (like memory) to avoid overwhelming the server or client:

public int GetResourceAdjustedBatchSize(int baseBatchSize)
{
    var memoryInfo = new Microsoft.VisualBasic.Devices.ComputerInfo();
    long availablePhysicalMemory = memoryInfo.AvailablePhysicalMemory;

    // If available memory is under 512MB, cut the batch size in half
    if (availablePhysicalMemory < 512 * 1024 * 1024)
    {
        return Math.Max(baseBatchSize / 2, 10);
    }
    return baseBatchSize;
}

For best results, combine these methods: start with a size-capped batch, then use adaptive retry to adjust if timeouts still occur.

内容的提问来源于stack exchange，提问作者Akbar Badhusha