单条记录插入与临时批量复制的高效实现方案问询

Hey there! Let's break down efficient approaches for both single-record inserts and temporary bulk copies in SQL Server using your .NET code. I'll cover optimized single inserts first, then the go-to methods for bulk operations.

Your current parameterized query is already a solid start (great job avoiding SQL injection!), but we can tweak it for better performance and reliability:

Key Optimizations: #

• Reuse connections and commands: Leverage SQL Server's connection pool by wrapping connections/commands in using blocks (this auto-releases resources and lets the pool reuse connections).
• Explicit parameter sizing: For string types like NVarChar, specify a fixed length (matching your table schema) instead of letting SQL Server infer it—this cuts down on type conversion overhead.
• Avoid AddWithValue: Stick to explicit parameter type definitions to prevent unexpected type mismatches.

Optimized Single-Insert Code Example: #

using (var connection = new SqlConnection(yourConnectionString))
{
    connection.Open();
    // Explicitly list columns (safer if table schema changes later)
    string commandText = @"INSERT INTO [DATA].[Jack].[Built] 
                          (utcDT, Symbol, Type, Value, Size, Mid, Spread, Date, SeqNumber)
                          VALUES( @utcDT, @Symbol, @Type, @Value, @Size, @Mid, @Spread, @Date, @SeqNumber)";
    
    using (var insertCommand = new SqlCommand(commandText, connection))
    {
        // Match parameter types/sizes to your table's schema exactly
        insertCommand.Parameters.Add("@utcDT", SqlDbType.DateTime2);
        insertCommand.Parameters.Add("@Symbol", SqlDbType.NVarChar, 50); // Adjust length to match your table
        insertCommand.Parameters.Add("@Type", SqlDbType.NVarChar, 20);
        insertCommand.Parameters.Add("@Value", SqlDbType.Float);
        insertCommand.Parameters.Add("@Size", SqlDbType.Int); // Update if your Size column is a different type
        insertCommand.Parameters.Add("@Mid", SqlDbType.Float);
        insertCommand.Parameters.Add("@Spread", SqlDbType.Float);
        insertCommand.Parameters.Add("@Date", SqlDbType.Date);
        insertCommand.Parameters.Add("@SeqNumber", SqlDbType.BigInt); // Adjust if needed

        // Assign values
        insertCommand.Parameters["@utcDT"].Value = DateTime.UtcNow;
        insertCommand.Parameters["@Symbol"].Value = "MSFT";
        insertCommand.Parameters["@Type"].Value = "Trade";
        // ... Populate remaining parameters

        insertCommand.ExecuteNonQuery();
    }
}

For bulk inserts (even temporary ones), SqlBulkCopy is the most performant option in .NET—it's designed to handle large datasets quickly by minimizing round-trips to the server.

Basic SqlBulkCopy Workflow: #

1. Create a DataTable (or use an IDataReader for streaming) that matches your target table's schema.
2. Populate the dataset.
3. Use SqlBulkCopy to write the data directly to the table.

Bulk Copy Example Code: #

// 1. Define a DataTable that matches your table's structure
var bulkDataTable = new DataTable();
bulkDataTable.Columns.Add("utcDT", typeof(DateTime));
bulkDataTable.Columns.Add("Symbol", typeof(string));
bulkDataTable.Columns.Add("Type", typeof(string));
bulkDataTable.Columns.Add("Value", typeof(double));
bulkDataTable.Columns.Add("Size", typeof(int));
bulkDataTable.Columns.Add("Mid", typeof(double));
bulkDataTable.Columns.Add("Spread", typeof(double));
bulkDataTable.Columns.Add("Date", typeof(DateTime));
bulkDataTable.Columns.Add("SeqNumber", typeof(long));

// 2. Add your batch of records to the DataTable
for (int i = 0; i < 500; i++) // Example: Insert 500 records
{
    var newRow = bulkDataTable.NewRow();
    newRow["utcDT"] = DateTime.UtcNow;
    newRow["Symbol"] = $"AAPL_{i}";
    newRow["Type"] = "Quote";
    newRow["Value"] = 195.75 + (i * 0.01);
    newRow["Size"] = 50 + i;
    newRow["Mid"] = 195.8 + (i * 0.01);
    newRow["Spread"] = 0.05;
    newRow["Date"] = DateTime.Today;
    newRow["SeqNumber"] = 10000 + i;
    bulkDataTable.Rows.Add(newRow);
}

// 3. Execute bulk copy
using (var connection = new SqlConnection(yourConnectionString))
{
    connection.Open();
    using (var bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "[DATA].[Jack].[Built]";
        
        // Optional: Map columns if DataTable names don't exactly match the table (omit if they do)
        bulkCopy.ColumnMappings.Add("utcDT", "utcDT");
        bulkCopy.ColumnMappings.Add("Symbol", "Symbol");
        // ... Map remaining columns as needed

        // Optional: Set batch size for large datasets (avoids memory overload)
        bulkCopy.BatchSize = 1000;
        
        // Optional: Use TableLock to speed up inserts (if no concurrent writes to the table)
        bulkCopy.SqlBulkCopyOptions = SqlBulkCopyOptions.TableLock;

        bulkCopy.WriteToServer(bulkDataTable);
    }
}

Bonus: Table-Valued Parameters (TVP) #

If you need to combine bulk inserts with custom SQL logic (like validation or joins), use Table-Valued Parameters instead of SqlBulkCopy:

1. First create a table type in SQL Server:

CREATE TYPE [DATA].[Jack].[BuiltBatchType] AS TABLE(
    utcDT DateTime2,
    Symbol NVarChar(50),
    Type NVarChar(20),
    Value Float,
    Size Int,
    Mid Float,
    Spread Float,
    Date Date,
    SeqNumber BigInt
)

2. Then use it in your .NET code:

using (var connection = new SqlConnection(yourConnectionString))
{
    connection.Open();
    string commandText = @"INSERT INTO [DATA].[Jack].[Built] 
                          SELECT * FROM @BulkRecords";
    
    using (var insertCommand = new SqlCommand(commandText, connection))
    {
        var tvpParameter = insertCommand.Parameters.Add("@BulkRecords", SqlDbType.Structured);
        tvpParameter.TypeName = "[DATA].[Jack].[BuiltBatchType]";
        tvpParameter.Value = bulkDataTable; // Reuse the same DataTable from earlier

        insertCommand.ExecuteNonQuery();
    }
}

TVPs are slower than SqlBulkCopy but offer more flexibility for complex operations.

Hope these methods give you the performance boost you're looking for! If you have specific constraints (like ultra-large datasets or concurrent writes), feel free to tweak the options above.

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