Does the feature "execute command multiple times" result in multiple round-trips to database?

Question

Does the feature "execute command multiple times" result in multiple round-trips to database?

dapper

Question

In the Dapper documentation, it says you can use an IEnumerable parameter to execute a command multiple times. It gives the following example:

connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)",
    new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } }
).IsEqualTo(3); // 3 rows inserted: "1,1", "2,2" and "3,3"

Will this result in multiple round-trips to the database (i.e. one for each T in the IEnumerable<T>)? Or is Dapper smart enough to transform the multiple queries into a batch and just do one round-trip? The documentation says an example usage is batch loading, so I suspect it only does one round-trip, but I want to be sure before I use it for performance-sensitive code.

As a follow-up question, and depending on the answer to the first, I'd be curious how transactions are handled? That is, is there one transaction for the whole set of Ts, or one transaction per T?

neizan

View more on Stack Overflow

neizan · Accepted Answer · 10/2/2017 7:43:58 PM

I finally got around to looking at this again. Looking at the source code (in \Dapper\SqlMapper.cs), I found the following snippet in method ExecuteImpl:

// ...
foreach (var obj in multiExec)
{
    if (isFirst)
    {
        masterSql = cmd.CommandText;
        isFirst = false;
        identity = new Identity(command.CommandText, cmd.CommandType, cnn, null, obj.GetType(), null);
        info = GetCacheInfo(identity, obj, command.AddToCache);
    }
    else
    {
        cmd.CommandText = masterSql; // because we do magic replaces on "in" etc
        cmd.Parameters.Clear(); // current code is Add-tastic
    }
    info.ParamReader(cmd, obj);
    total += cmd.ExecuteNonQuery();
}
// ...

The interesting part is on the second-last line where ExecuteNonQuery is called. That method is being called on each iteration of the for loop, so I guess it is not being batched in the sense of a set-based operation. Therefore, multiple round-trips are required. However, it is being batched in the sense that all operations are performed on the same connection, and within the same transaction if so specified.

The only way I can think of to do a set-based operation is to create a custom table-valued type (in the database) for the object of interest. Then, in the .NET code pass a DataTable object containing matching names and types as a command parameter. If there were a way to do this without having to create a table-valued type for every object, I'd love to hear about it.

Does the feature "execute command multiple times" result in multiple round-trips to database?

Question

Fastest Dapper Plus Extensions

Accepted Answer

Performance

Expression Evaluator