When loading large volumes of data—such as 1 million+ rows—into SQL Server, naive row-by-row INSERT statements quickly become a performance bottleneck. This article demonstrates efficient alternatives using BULK INSERT, parameterized batch operations, and file-based staging strategies.

Database Setup

The test environment uses a dedicated database with a simple two-column table:

-- Create test database
IF DB_ID('bulk_demo') IS NOT NULL
    DROP DATABASE bulk_demo;
GO
CREATE DATABASE bulk_demo
ON PRIMARY (
    NAME = 'bulk_demo_data',
    FILENAME = 'D:\DB\bulk_demo.mdf',
    SIZE = 2048MB,
    FILEGROWTH = 512MB
)
LOG ON (
    NAME = 'bulk_demo_log',
    FILENAME = 'D:\DB\bulk_demo.ldf',
    SIZE = 2048MB,
    FILEGROWTH = 512MB
);
GO

USE bulk_demo;
GO

-- Create target table
IF OBJECT_ID('dbo.ImportData', 'U') IS NOT NULL
    DROP TABLE dbo.ImportData;
GO
CREATE TABLE dbo.ImportData (
    id VARCHAR(50) NOT NULL,
    payload VARCHAR(50) NOT NULL
);
GO

Performance Comparison Strategy

Three ingestion methods were benchmarked using identical synthetic datasets (720,000 rows):

• Naive ENSERT: Concatenated single-statement batch (not recommended for production)
• BULK INSERT: Native SQL Server bulk load from delimited text files
• Stream-based BCP via SqlClient: Programmatic bulk copy using SqlBulkCopy

C# Implementation Highlights

1. Delimited File Generation

private static void GenerateCsvFile(string filePath, int rowCount)
{
    var sw = new StreamWriter(filePath, false);
    var stopwatch = Stopwatch.StartNew();

    for (int i = 0; i < rowCount; i++)
    {
        sw.WriteLine($"{i},{i}");
    }

    sw.Close();
    stopwatch.Stop();
    Console.WriteLine($"CSV generation ({rowCount} rows): {stopwatch.ElapsedMilliseconds} ms");
}

2. BULK INSERT Execution

private static void ExecuteBulkInsert(string connectionString, string csvPath, int rowCount)
{
    var sql = $@"
        BULK INSERT dbo.ImportData 
        FROM '{csvPath}' 
        WITH (
            FIELDTERMINATOR = ',',
            ROWTERMINATOR = '\n',
            BATCHSIZE = {rowCount / 10},
            TABLOCK
        )";

    var stopwatch = Stopwatch.StartNew();
    using var conn = new SqlConnection(connectionString);
    conn.Open();
    using var cmd = new SqlCommand(sql, conn);
    cmd.ExecuteNonQuery();
    stopwatch.Stop();
    Console.WriteLine($"BULK INSERT ({rowCount} rows): {stopwatch.ElapsedMilliseconds} ms");
}

3. SqlBulkCopy Alternative (Recommended for .NET Applications)

private static void ExecuteSqlBulkCopy(string connectionString, string csvPath, int rowCount)
{
    var dataTable = new DataTable();
    dataTable.Columns.Add("id", typeof(string));
    dataTable.Columns.Add("payload", typeof(string));

    using var reader = new StreamReader(csvPath);
    string line;
    while ((line = reader.ReadLine()) != null)
    {
        var parts = line.Split(',');
        if (parts.Length == 2)
            dataTable.Rows.Add(parts[0], parts[1]);
    }

    var stopwatch = Stopwatch.StartNew();
    using var conn = new SqlConnection(connectionString);
    conn.Open();
    using var bulk = new SqlBulkCopy(conn)
    {
        DestinationTableName = "dbo.ImportData",
        BatchSize = 10000,
        EnableStreaming = true,
        BulkCopyTimeout = 300
    };
    bulk.WriteToServer(dataTable);
    stopwatch.Stop();
    Console.WriteLine($"SqlBulkCopy ({rowCount} rows): {stopwatch.ElapsedMilliseconds} ms");
}

Key Optimization Practices

• Disable indexes and constraints before bulk load; rebuild after ingestion
• Use TABLOCK hint in BULK INSERT to reduce locking overhead
• Set appropriate BATCHSIZE (e.g., 10,000–100,000) to balance memory usage and transaction log growth
• Pre-size database files to avoid auto-growth delays during ingestion
• Prefer SqlBulkCopy over concatenated SQL for managed code scenarios—it avoids SQL injection risks and handles type conversion robustly

Performance Observations

On standard SSD hardware with default SQL Server configuration:

• Naive INSERT (140K rows): ~12,800 ms
• BULK INSERT (720K rows): ~940 ms
• SqlBulkCopy (720K rows): ~1,120 ms

The bulk methods achieve >10× speedup versus row-by-row insertion, confirming that I/O and transaction overhead—not CPU—are the primary bottlenecks in high-volume data loading.