Import multiple rows into a temp table in one round trip via SqlDataRecord

Question

This problem is just starting to show up into the profiler. We have something that looks like this:

using (var cmd = connection.CreateCommand()) 
{
    cmd.CommandText = "INSERT INTO #Ids VALUES (@0);";
    cmd.Parameters.Add(cmd.CreateParameter() { ParameterName = "@0", DbType = GetDbType() });

    cmd.Prepare();

    foreach (var value in values)
    {
        ((IDbDataParameter)cmd.Parameters[0]).Value = value;
        cmd.ExecuteNonQuery();
    }
}

We're looking at the possibility of speeding this up by decreasing the number of round trips. Using DataTable here would be a monstrosity but there's this type SqlDataRecord that seems to fit, so we should be able to do something like this:

using (var cmd = connection.CreateCommand()) 
{
    cmd.CommandText = "INSERT INTO #Ids SELECT * FROM @0;";

    var param  = cmd.CreateParameter();
    param.ParameterName = "@0";
    // FIXME more properties
    cmd.Parameters.Add(param);
    param.Value = values.Cast<T>().Select((x) => {
                             // FIXME Create new SqlDataRecord
                         }.ToList();

    cmd.ExecuteNonQuery();
}

The main difficulty here that makes this different from the other samples is the T; this code is dynamic and receives a huge number of possible types; thus CREATE TYPE won't work. There's no way creating a type for every possible VARCHAR length is happening.

How do I fill in the FIXME comments above?

Related question, no good answers: How would I create a temp table in SQL Server when I have a big list of ID's

In comments there's an ask for the bigger picture. There really isn't a very good way to get more information by zooming out here. There's exactly one copy of the routine that sets arguments to all SQL calls (read: stored procedures) in the codebase, and this is it.

The scale factor for number of rows is fat tailed; the average number of rows is less than 10 but the maximum size is very large and calls with over a thousand happen regularly. Thus we load a temp table; and on conversion to do something other than a foreach loop we still load a temp table.

Answer 1

It sounds like a TVP might not be the right fit for you.

But you can use a temp table with SqlBulkCopy, which will be much much faster than individual inserts.

You can pass SqlBulkCopy either a DataTable or a DbDataReader. The former is simple to create, but can be memory-heavy. You can create a reader simply using the free FastMember library.

using var conn = new SqlConnection(GetConnectionStringFromSomewhere());
await conn.OpenAsync();
var createTable = $"CREATE TABLE #ids (Id {columnTypeHere} PRIMARY KEY);";
using (var cmd = new SqlCommand(createTable, conn)) 
{
    // do NOT pass any parameters for this command, otherwise it gets wrapped as sp_executesql and the table will be dropped immediately
    // cmd.Prepare() is unnecessary and counter-productive

    await cmd.ExecuteNonQueryAsync();
}

using (var bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock))
using (var reader = ObjectReader.Create(data)) 
{
    bulk.ColumnMappings.Add("Id", "Id");
    bulk.DestinationTableName = "#ids";
    await bulk.WriteToServerAsync(reader);
}

using (var cmd = new SqlCommand("someStoredProcedure", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(  // etc
    await cmd.ExecuteNonQueryAsync();
}