Async methods: Added QueryMultipleAsync and ExecuteAsync.

[kwaclaw]

It seems to me that the methods QueryMultipleAsync and ExecuteAsync need to be included when one wants to use Dapper asynchronously.

[roend83]

Is there something specific holding this pull request up? I'd love to be able to use this in my projects.

[kwaclaw]

The original project seems to be somehow inactive - there have been no commits for almost 5 months.

[mgravell]

Simply: time, availability, etc; it is very much on my agenda to clear the backlog, and some change requests from internally came in this week, which is very much motivation to dust it off.

[roend83]

Let me know if there's any way I can help.

[rmbrunet]

While testing ExecuteAsync with an IEnumerable param I was getting exceptions "The command execution cannot proceed due to a pending asynchronous operation already in progress". I had to wait each command before issuing the next. As in :

total += await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);

Instead of:

cmdTasks.Add(cmd.ExecuteNonQueryAsync());
...

foreach (var cmdTask in cmdTasks)
total += await cmdTask.ConfigureAwait(false);

[kwaclaw]

You probably need to enable multiple active queries in the driver
(connection string).
Example:

 <add name="MyConnection"
      providerName="System.Data.SqlClient"

connectionString="Server=MyServer;Database=MyDb;UID=john;pwd=doe;MultipleActiveResultSets=True;"
/>

Karl

On 2/17/2014 1:08 PM, Roberto Martinez-Brunet wrote:

While testing ExecuteAsync with an IEnumerable param I was getting
exceptions "The command execution cannot proceed due to a pending
asynchronous operation already in progress". I had to wait each
command before issuing the next. As in :

total += await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);

Instead of:

cmdTasks.Add(cmd.ExecuteNonQueryAsync());
...

foreach (var cmdTask in cmdTasks)
total += await cmdTask.ConfigureAwait(false);

—
Reply to this email directly or view it on GitHub
#105 (comment).

[rmbrunet]

Hello Karl,

Nope, tried that (and re-checked now) with two different servers. The code I'm runnung is:

    public async void TestExecuteRepeatedAsync() {

        string sqlCreate = @"CREATE TABLE #Cities (ID int, Name varchar(16))";

        string sqlInsert = @"INSERT INTO #Cities (ID, Name) VALUES (@Id, @Name)";

        using (IDbConnection conn = await Program.GetOpenConnectionAsync()) {

            var n = await conn.ExecuteAsync(sqlCreate);

            List<dynamic> cities = new List<dynamic>();

            cities.Add( new { Id = 1, Name = "New York"} );
            cities.Add( new { Id = 2, Name = "Los Angeles"} );
            cities.Add( new { Id = 3, Name = "Chicago"} );

            n = await conn.ExecuteAsync(sqlInsert, cities);

            var results = await conn.QueryAsync<int>("SELECT COUNT(*) FROM #Cities");

            n = results.Single(); ;

            n.IsEqualTo(3);

            var m = await conn.ExecuteAsync("DROP TABLE #Cities");

        }

[kwaclaw]

I can duplicate this, though I have been using this code for a while now
without problems.
From what I can google it seems that one SqlCommand can only have one
async operation in progress at any time.
This looks like a bug inExecuteAsync().

On 2/17/2014 3:54 PM, Roberto Martinez-Brunet wrote:

| public async void TestExecuteRepeatedAsync() {

     string sqlCreate = @"CREATE TABLE #Cities (ID int, Name varchar(16))";

     string sqlInsert = @"INSERT INTO #Cities (ID, Name) VALUES (@Id, @Name)";

     using (IDbConnection conn = await Program.GetOpenConnectionAsync()) {

         var n = await conn.ExecuteAsync(sqlCreate);

         List<dynamic> cities = new List<dynamic>();

         cities.Add( new { Id = 1, Name = "New York"} );
         cities.Add( new { Id = 2, Name = "Los Angeles"} );
         cities.Add( new { Id = 3, Name = "Chicago"} );

         n = await conn.ExecuteAsync(sqlInsert, cities);

         var results = await conn.QueryAsync<int>("SELECT COUNT(*) FROM #Cities");

         n = results.Single(); ;

         n.IsEqualTo(3);

         var m = await conn.ExecuteAsync("DROP TABLE #Cities");

     }|

Karl Waclawek
Chief Architect
QLine Solutions Inc.

[kwaclaw]

Hi Roberto,

I committed a fix to my fork. Seems to work for me.

Karl

On 2/17/2014 3:54 PM, Roberto Martinez-Brunet wrote:

Hello Karl,

Nope, tried that (and re-checked now) with two different servers. The
code I'm runnung is:

| public async void TestExecuteRepeatedAsync() {

     string sqlCreate = @"CREATE TABLE #Cities (ID int, Name varchar(16))";

     string sqlInsert = @"INSERT INTO #Cities (ID, Name) VALUES (@Id, @Name)";

     using (IDbConnection conn = await Program.GetOpenConnectionAsync()) {

         var n = await conn.ExecuteAsync(sqlCreate);

         List<dynamic> cities = new List<dynamic>();

         cities.Add( new { Id = 1, Name = "New York"} );
         cities.Add( new { Id = 2, Name = "Los Angeles"} );
         cities.Add( new { Id = 3, Name = "Chicago"} );

         n = await conn.ExecuteAsync(sqlInsert, cities);

         var results = await conn.QueryAsync<int>("SELECT COUNT(*) FROM #Cities");

         n = results.Single(); ;

         n.IsEqualTo(3);

         var m = await conn.ExecuteAsync("DROP TABLE #Cities");

     }

|
|

—
Reply to this email directly or view it on GitHub
#105 (comment).

Karl Waclawek
Chief Architect
QLine Solutions Inc.

[rmbrunet]

Hello Karl,

I ran my test codes through your new implementation of ExecuteAsync and it works for me too. I have some comments though.

1.The difference between what you are doing and what I am (was) doing is that in my case the commands are processed sequentially while in yours they are going in parallel. Then, in the first case a MARS enable connection is not necessary while in the second it is. Considering that a MARS enabled connection is costly, I think there might be cases when the sequential approach is faster/better. Nevertheless, the second approach feels "more" async.

1. In your implementation, it worries me that the commands are disposed before the corresponding task is awaited.
2. Instead of calling await on the tasks in a loop maybe Task.WhenAll(tasks).ConfigureAwait(false) would be more convinient.
3. Repeatedly calling SetupCommand is probably unnecessary as the sql string is parsed and modified in the first pass.

Consiodering these comments I created an ExcuteAsync implementation that seems to work but need more testing:

Best Regards,

Roberto

    public static async Task<int> ExecuteAsync(this IDbConnection cnn, string sql, dynamic param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null) {
        IEnumerable multiExec = (object)param as IEnumerable;
        Identity identity;
        CacheInfo info = null;

        if (multiExec != null && !(multiExec is string)) {
            bool isFirst = true;
            int total = 0;

            var cmdTasks = new List<Task<int>>();
            var cmds = new List<DbCommand>();

            foreach (var obj in multiExec) {
                DbCommand cmd = null;

                if (isFirst) {
                    cmd = (DbCommand)SetupCommand(cnn, transaction, sql, null, null, commandTimeout, commandType);
                    isFirst = false;
                    identity = new Identity(sql, cmd.CommandType, cnn, null, obj.GetType(), null);
                    info = GetCacheInfo(identity);
                }
                else {
                    //Clone the first command
                    cmd = (DbCommand)cnn.CreateCommand();
                    cmd.CommandText = cmds[0].CommandText;
                    cmd.CommandType = cmds[0].CommandType;
                    cmd.CommandTimeout = cmds[0].CommandTimeout;
                    cmd.Transaction = cmds[0].Transaction;
                }

                info.ParamReader(cmd, obj);
                cmdTasks.Add(cmd.ExecuteNonQueryAsync());
                cmds.Add(cmd);
            }

            int[] results = await Task.WhenAll(cmdTasks.ToArray()).ConfigureAwait(false);
            total = results.Sum();

            foreach (var cmd in cmds) {
                cmd.Dispose();
            }

            return total;
        }

[kwaclaw]

Thanks for the feedback, I modified my fix. Further comments below.

Best Regards,

Karl

On 2/18/2014 9:38 AM, Roberto Martinez-Brunet wrote:

Hello Karl,

I ran my test codes through your new implementation of ExecuteAsync
and it works for me too. I have some comments though.

1.The difference between what you are doing and what I am (was) doing
is that in my case the commands are processed sequentially while in
yours they are going in parallel. Then, in the first case a MARS
enable connection is not necessary while in the second it is.
Considering that a MARS enabled connection is costly, I think there
might be cases when the sequential approach is faster/better.
Nevertheless, the second approach feels "more" async.

I would say that the implementation should not force sequential
processing - after all, its name ends in "Async".

In your implementation, it worries me that the commands are
disposed before the corresponding task is awaited.

It worries me too - my mistake. I think it only works because it has no
effect, even Microsoft's sample code does not dispose the command
instance consistently, see
http://msdn.microsoft.com/en-us/library/hh211418%28v=vs.110%29.aspx.

In my modification I have added a task continuation where the command is
disposed. Under normal circumstances the schedulker will try to run this
synchronously.

Instead of calling await on the tasks in a loop maybe
Task.WhenAll(tasks).ConfigureAwait(false) would be more convinient.

Maybe yes, but the existing approach seems just as simple.

Repeatedly calling SetupCommand is probably unnecessary as the sql
string is parsed and modified in the first pass.

True.

Consiodering these comments I created an ExcuteAsync implementation
that seems to work but need more testing:

Best Regards,

Roberto

| public static async Task ExecuteAsync(this IDbConnection cnn, string sql, dynamic param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null) {
IEnumerable multiExec = (object)param as IEnumerable;
Identity identity;
CacheInfo info = null;

     if (multiExec != null && !(multiExec is string)) {
         bool isFirst = true;
         int total = 0;

         var cmdTasks = new List<Task<int>>();
         var cmds = new List<DbCommand>();

         foreach (var obj in multiExec) {
             DbCommand cmd = null;

             if (isFirst) {
                 cmd = (DbCommand)SetupCommand(cnn, transaction, sql, null, null, commandTimeout, commandType);
                 isFirst = false;
                 identity = new Identity(sql, cmd.CommandType, cnn, null, obj.GetType(), null);
                 info = GetCacheInfo(identity);
             }
             else {
                 //Clone the first command
                 cmd = (DbCommand)cnn.CreateCommand();
                 cmd.CommandText = cmds[0].CommandText;
                 cmd.CommandType = cmds[0].CommandType;
                 cmd.CommandTimeout = cmds[0].CommandTimeout;
                 cmd.Transaction = cmds[0].Transaction;
             }

             info.ParamReader(cmd, obj);
             cmdTasks.Add(cmd.ExecuteNonQueryAsync());
             cmds.Add(cmd);
         }

         int[] results = await Task.WhenAll(cmdTasks.ToArray()).ConfigureAwait(false);
         total = results.Sum();

         foreach (var cmd in cmds) {
             cmd.Dispose();
         }

         return total;
     }

|
|

—
Reply to this email directly or view it on GitHub
#105 (comment).

Karl Waclawek
Chief Architect
QLine Solutions Inc.

[rmbrunet]

Good, lets hope that Marc gets motivated to review all this stuff :-)

[otac0n]

I would not expect the commands to run in parallel for one pretty big reason: I never want one of the commands to be killed as the deadlock victim of another. Recovering from such a failure (outside of a transaction, maybe) with this model would be very difficult, when normally a deadlock victim command could usually just be retried.

I would expect it to behave identically to the normal multi-exec, except that it would notify me when it is finished asynchronously.

[rmbrunet]

Yes, agree, that makes sense.

[kwaclaw]

Although I don't think this is an issue as it is the very nature of a
SQL server to run multiple SQL statements concurrently, I have added
another async method to my fork that enforces serialized execution.

It is called ExecuteAsyncSerialized. It assumes that the parameter is
always an IEnumerable.

Karl

On 3/3/2014 11:44 PM, John Gietzen wrote:

I would not expect the commands to run in parallel for one pretty big
reason: I never want one of the commands to be killed as the deadlock
victim of another. Recovering from such a failure (outside of a
transaction, maybe) with this model would be very difficult, when
normally a deadlock victim command could usually just be retried.

I would expect it to behave identically to the normal multi-exec,
except that it would notify me when it is finished asynchronously.

—
Reply to this email directly or view it on GitHub
#105 (comment).

Karl Waclawek
Chief Architect
QLine Solutions Inc.

[garyhuntddn]

Karl - would you consider reversing your decision above so that running them sequentially is the norm and that running them in parallel is done via ExecuteConcurrentlyAsync.

I agree with your comment that statements are often run concurrently in SQL but AFAIK it's quite rare against the same connection.

My particular example is inserting rows that form a self-referrencing hierarchy and therefore have to be inserted in the order I have dictated otherwise foreign-key errors will be introduced.

cheers,

Gary

[kwaclaw]

Gary,

I don't have a religious prejudice one way or another, so I can switch things around if this is the general consensus. At the end this is not my call anyway, since Marc will determine what ends up in his repository.

Best thing would be if Marc lets us know...

Btw, In my experience, with the async approach you will likely have multiple active statements on your connection unless you pay careful attention to your code. This is especially true when you send of multiple unrelated queries and want to shorten the overall latency, as in that case you don't want to send the next query off only after you received the results from the previous one.

Karl

[mgravell]

I confess I don't know the full implications of calling exec-async concurrently like this; do they get stacked and pipelined by the provider? or does it end up running MARS-style? The latter sounds undesirable; the former probably isn't a problem.

[kwaclaw]

I think they end up running MARS style, but MARS does not imply parallel execution, especially not for updates and inserts.

For my use case I would like multiple statements to be sent over the wire right after each other, without waiting for the results before sending the next one. If there are many statements, the aggregate latency might become noticeable.

The proposed ExecuteAsync implementation does not really start the commands concurrently (no Parallel.For), they are submitted/started on the same thread and connection in sequence. So I assume the provider will send each statement over the wire right away in the order it is submitted.

[otac0n]

Marc: MSDN says they run MARS-style.

Karl:

I would vote for in-series to be the norm for these reasons:

• Priciple of Least Surprise aka "Pit of Success" (Code will behave correctly, regardless of what the programmer assumes will actually happen.)
• Significantly reduced likelihood of deadlocks.
• No MARS errors.

In addition, I would say that the "Dapper" way of doing this would be a boolean flag, rather than a method overload. (e.g. bool buffered = true)

[rmbrunet]

I would try to achieve only asynchronicity not parallelism. I compiled the dll in both cases and tested with a bunch of inserts. When there were ~500 inserts all seemed to work fine in both cases. With 12K inserts it also worked fine.. most of the time; but sometime it was way slower in parallel. I cannot conclude that the problem was in the parallelism but a) such a large qty of tasks and b) a bunch of concurrent inserts fighting for the same table, makes me nervous. Then I reverted to only use the async-sequential approach without problems.

[mgravell]

but... if this is just pipelining non-query ops, I can't see a problem with running them async by default - it would also reduce latency costs. I'm a big fan of pipelining. It kinda makes sense for execute-non-query. However: I would not advocate the same for anything that returned grid data (execute-query / execute-scalar), because that would impose MARS. That would probably need to be syncronous and consumed.

[kwaclaw]

As far as I know there is no parallel execution here, so you are fine.
On Mar 22, 2014 6:25 PM, "Roberto Martinez-Brunet" notifications@github.com
wrote:

I would try to achieve only asynchronicity not parallelism. I compiled the
dll in both cases and tested with a bunch of inserts. When there were ~500
inserts all seemed to work fine in both cases. With 12K inserts it also
worked fine.. most of the time; but sometime it was way slower in
parallel. I cannot conclude that the problem was in the parallelism but a)
such a large qty of tasks and b) a bunch of concurrent inserts fighting for
the same table, makes me nervous. Then I reverted to only use the
async-sequential approach without problems.

Reply to this email directly or view it on GitHubhttps://github.com//pull/105#issuecomment-38366131
.

[mgravell]

@rmbrunet indeed, getting async pipelining right is really very tricky - I've got around 25kloc in SE.Redis that can testify to that!

[otac0n]

@mgravell: I think it is running queries concurrently:

using (var conn = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=Scratch;Integrated Security=true"))
{
    conn.Open();

    bool isFirst = true;
    int total = 0;

    var cmdTasks = new List<Task<int>>();
    var cmds = new List<SqlCommand>();
    foreach (var i in Enumerable.Range(0, 3))
    {
        SqlCommand cmd = null;

        if (isFirst)
        {
            cmd = new SqlCommand("WAITFOR DELAY '00:00:10'", conn);
            isFirst = false;
        }
        else
        {
            cmd = new SqlCommand(null, conn);
            cmd.CommandText = cmds[0].CommandText;
            cmd.CommandType = cmds[0].CommandType;
            cmd.CommandTimeout = cmds[0].CommandTimeout;
            cmd.Transaction = cmds[0].Transaction;
        }

        cmdTasks.Add(cmd.ExecuteNonQueryAsync());
        cmds.Add(cmd);
    }

    int[] results = Task.WhenAll(cmdTasks.ToArray()).Result;
    total = results.Sum();

    foreach (var cmd in cmds)
    {
        cmd.Dispose();
    }

    total.Dump();
}

The first of the 3 queries returns fine, but this results in 2 exceptions:

The connection does not support MultipleActiveResultSets.

Also, it returns after ~10 seconds, rather than after ~30 seconds.

[kwaclaw]

If you google MARS you will see it never executes queries concurrently, it
rather interleaves them, and only some of them. In your case MARS is not
even enabled.

What you are observing is that there is a short time when a subsequent
query is started before the previous query's result is received at the
client. For this to work your connection must be configured accordingly.
On Mar 22, 2014 9:44 PM, "John Gietzen" notifications@github.com wrote:

@mgravell https://github.com/mgravell: I think it is running queries
concurrently:

using (var conn = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=Scratch;Integrated Security=true"))
{
conn.Open();

bool isFirst = true;
int total = 0;

var cmdTasks = new List<Task<int>>();
var cmds = new List<SqlCommand>();
foreach (var i in Enumerable.Range(0, 3))
{
    SqlCommand cmd = null;

    if (isFirst)
    {
        cmd = new SqlCommand("WAITFOR DELAY '00:00:10'", conn);
        isFirst = false;
    }
    else
    {
        cmd = new SqlCommand(null, conn);
        cmd.CommandText = cmds[0].CommandText;
        cmd.CommandType = cmds[0].CommandType;
        cmd.CommandTimeout = cmds[0].CommandTimeout;
        cmd.Transaction = cmds[0].Transaction;
    }

    cmdTasks.Add(cmd.ExecuteNonQueryAsync());
    cmds.Add(cmd);
}

int[] results = Task.WhenAll(cmdTasks.ToArray()).Result;
total = results.Sum();

foreach (var cmd in cmds)
{
    cmd.Dispose();
}

total.Dump();

}

The first of the 3 queries returns fine, but this results in 2 exceptions:

The connection does not support MultipleActiveResultSets.

Also, it returns after ~10 seconds, rather than after ~30 seconds.

Reply to this email directly or view it on GitHubhttps://github.com//pull/105#issuecomment-38370455
.

[otac0n]

Maybe I'm just not understanding the debate.

My point is that the queries are running concurrently. This should not be the default behavior, since it will lead to deadlocks, out-of-order operations, and extra memory usage. Add on top of that the user will have to change their connection string...

[otac0n]

Oh, wait, you're right.

It actually isn't running them concurrently, and so it really is just a connection string change. I guess it isn't so bad.

[kwaclaw]

In light of this discussion, do we still need "ExecuteAsyncSerialized()" ?
All it seems to add is latency.

However, it may help when you don't want to add "MultipleActiveResultSets = true" to your connection string.

[otac0n]

@kwaclaw: If we do keep it, I would vote to make it a boolean option, just like buffered is. (Just to match the style of the rest of the project.)

[rmbrunet]

If we want to parallelize to reduce latency when param is IEnumerable
shouldn't be using IEnumerable.AsParallel or Parallel.ForEach (of course
with MARS enabled) instead of creating as many tasks as the param
list/array count?

On Sun, Mar 23, 2014 at 2:03 PM, John Gietzen notifications@github.comwrote:

@kwaclaw https://github.com/kwaclaw: If we do keep it, I would vote to
make it a boolean option, just like buffered is. (Just to match the style
of the rest of the project.)

Reply to this email directly or view it on GitHubhttps://github.com//pull/105#issuecomment-38390604
.

[mgravell]

No, basically. Pipelining good, concurrency bad. They are very different things. If you used Parallel you should expect it to explode in a shower of sparks: it is not intended to be thread-safe. Note that TDS is naturally pipelineable, so if Async works by pipelining, it is fine: you cannot do any better on the connection than that.

[kwaclaw]

I think this would make the order of execution unpredictable, which
currently is still maintained.

On 3/23/2014 2:41 PM, Roberto Martinez-Brunet wrote:

If we want to parallelize to reduce latency when param is IEnumerable
shouldn't be using IEnumerable.AsParallel or Parallel.ForEach (of course
with MARS enabled) instead of creating as many tasks as the param
list/array count?

On Sun, Mar 23, 2014 at 2:03 PM, John Gietzen
notifications@github.comwrote:

@kwaclaw https://github.com/kwaclaw: If we do keep it, I would vote to
make it a boolean option, just like buffered is. (Just to match the
style
of the rest of the project.)

Reply to this email directly or view it on
GitHubhttps://github.com//pull/105#issuecomment-38390604
.

—
Reply to this email directly or view it on GitHub
#105 (comment).

Karl Waclawek
Chief Architect
QLine Solutions Inc.

[otac0n]

At this point I the question becomes, "is there ever going to be a time when a developer should prefer to avoid pipelining the requests?" I believe that the answer to that question is yes, so we should keep both implementations.

Also, have we considered how this behaves on Oracle, MySql, etc? We shouldn't rely on the implementation details of a particular DbConnection. Add to that the fact that most people don't have MultipleActiveResultSets=true in their connections strings, which could easily lead to boneheaded exceptions being thrown in production systems.

This all leads me to believe that the default should be to serialize the queries. (e.g. ..., bool pipeline = false) Yes, we would be sacrificing latency in the specific case, but it is for predictability and reliability in the general case.

[kwaclaw]

@john: You make good points, but turning off pipelining by default only solves the issue for callers of ExecuteAsync, but not for those who use asynchronous calls in any other way. They will always have to consider the issues around pipelining, MultipleActiveResultSets and the particular database/driver they are using.

What about this:

        public static async Task<int> MultiExecuteAsync(this IDbConnection cnn, string sql, IEnumerable param, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
          ...
        }
        public static async Task<int> MultiExecuteAsyncSerialized(this IDbConnection cnn, string sql, IEnumerable param, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
          ...
        }

        public static Task<int> ExecuteAsync(this IDbConnection cnn, string sql, dynamic param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, bool serialized = false)
        {
            IEnumerable multiExec = (object)param as IEnumerable;
            if (multiExec != null && !(multiExec is string))
            {
                if (serialized)
                    return MultiExecuteAsyncSerialized(cnn, sql, multiExec, transaction, commandTimeout, commandType);
                else
                    return MultiExecuteAsync(cnn, sql, multiExec, transaction, commandTimeout, commandType); 
            }

            // nice and simple
            CacheInfo info = null;
            if ((object)param != null)
            {
                var identity = new Identity(sql, commandType, cnn, null, (object)param == null ? null : ((object)param).GetType(), null);
                info = GetCacheInfo(identity);
            }
            return ExecuteCommandAsync(cnn, transaction, sql, (object)param == null ? null : info.ParamReader, (object)param, commandTimeout, commandType);
        }

[otac0n]

👍

[n1ghtmare]

@mgravell Mark, dude come on merge it :)

[otac0n]

Please?

[heneryville]

This is good stuff. Once we get this commited, we can move on to porting everything in Contrib to support async as well.

[mgravell]

Applied manually, along with inter-related changed; thanks

[rmbrunet]

Marc, the difference in performance between

total += await cmd.ExecuteNonQueryAsync();

and

cmdTasks.Add(cmd.ExecuteNonQueryAsync());
...
int[] results = await Task.WhenAll(cmdTasks.ToArray()).ConfigureAwait(false);
int total = results.Sum();

seems to be significant in some cases. There was a proposal of include a flag in the call to leave to the caller the responsibility to know if the connection supports pipelining. If a) the performance gains are substantial (as my informal tests seems to indicate) b) the use cases for these functionality are not exceptional and c) the usage of Dapper is mostly SQL Server (where the functionality is supported) then the use of a flag is a good alternative. The other alternative, kinda of "official answer" to the issue, would be that the use case is exceptional and should be coded in straight ado.net if needed.

[mgravell]

The idea of a caller-controller flag is viable. Because of some issues relating to overload resolution, optional parameters, and ambiguous method calls, I had to introduce a new state representation for some of the async stuff; I could move any bool flags (buffered, pipelined, etc) to a [Flags] enum, and then cope with it internally. Interesting. Will investigate.

[kwaclaw]

I would appreciate adding the flag back.

AFAIK, MARS functionality has been supported by Oracle and DB2 for along
time, so it is not such a special case.

Karl

On Thu, May 8, 2014 at 10:02 AM, Marc Gravell notifications@github.comwrote:

The idea of a caller-controller flag is viable. Because of some issues
relating to overload resolution, optional parameters, and ambiguous method
calls, I had to introduce a new state representation for some of the async
stuff; I could move any bool flags (buffered, pipelined, etc) to a [Flags]
enum, and then cope with it internally. Interesting. Will investigate.

—
Reply to this email directly or view it on GitHubhttps://github.com//pull/105#issuecomment-42553157
.

[mgravell]

The problem I have, though, is that DbCommand explicitly says " Do not
invoke other methods and properties of the DbCommand object until the
returned Task is complete.", which I can only interpret as "do not
pipeline". This makes me very reluctant to make pipelining the default
implementation.
On 8 May 2014 20:14, "Karl Waclawek" notifications@github.com wrote:

I would appreciate adding the flag back.

AFAIK, MARS functionality has been supported by Oracle and DB2 for along
time, so it is not such a special case.

Karl

On Thu, May 8, 2014 at 10:02 AM, Marc Gravell notifications@github.comwrote:

The idea of a caller-controller flag is viable. Because of some issues
relating to overload resolution, optional parameters, and ambiguous
method
calls, I had to introduce a new state representation for some of the
async
stuff; I could move any bool flags (buffered, pipelined, etc) to a
[Flags]
enum, and then cope with it internally. Interesting. Will investigate.

—
Reply to this email directly or view it on GitHub<
https://github.com/StackExchange/dapper-dot-net/pull/105#issuecomment-42553157>

.

—
Reply to this email directly or view it on GitHubhttps://github.com//pull/105#issuecomment-42593064
.

[kwaclaw]

To me that just means that you have to use a new command object for each
overlapping async invocation.

Which is why my fork is creating new command objects, and that is also how
it would happen if you implemented an iteration over an enumerable
parameter using individual Dapper calls.

On Thu, May 8, 2014 at 5:53 PM, Marc Gravell notifications@github.comwrote:

The problem I have, though, is that DbCommand explicitly says " Do not
invoke other methods and properties of the DbCommand object until the
returned Task is complete.", which I can only interpret as "do not
pipeline". This makes me very reluctant to make pipelining the default
implementation.
On 8 May 2014 20:14, "Karl Waclawek" notifications@github.com wrote:

I would appreciate adding the flag back.

AFAIK, MARS functionality has been supported by Oracle and DB2 for along
time, so it is not such a special case.

Karl

On Thu, May 8, 2014 at 10:02 AM, Marc Gravell notifications@github.comwrote:

The idea of a caller-controller flag is viable. Because of some issues
relating to overload resolution, optional parameters, and ambiguous
method
calls, I had to introduce a new state representation for some of the
async
stuff; I could move any bool flags (buffered, pipelined, etc) to a
[Flags]
enum, and then cope with it internally. Interesting. Will investigate.

—
Reply to this email directly or view it on GitHub<

https://github.com/StackExchange/dapper-dot-net/pull/105#issuecomment-42553157>

.

—
Reply to this email directly or view it on GitHub<
https://github.com/StackExchange/dapper-dot-net/pull/105#issuecomment-42593064>

.

—
Reply to this email directly or view it on GitHubhttps://github.com//pull/105#issuecomment-42611100
.

[mgravell]

This is now available; I tweaked the implementation a bit because the scenario we're using here involves millions of rows, so the list/array is not an option. Instead, it uses a queue - so it can buffer a finite amount, and then when full it will be enqueue/dequeue/await in tandem - then dequeue/await anything still outstanding at the end. It works pretty well ;p

[kwaclaw]

Looks awesome!

On Wed, May 14, 2014 at 10:42 AM, Marc Gravell notifications@github.comwrote:

This is now available; I tweaked the implementation a bit because the
scenario we're using here involves millions of rows, so the list/array
is not an option. Instead, it uses a queue - so it can buffer a finite
amount, and then when full it will be enqueue/dequeue/await in tandem -
then dequeue/await anything still outstanding at the end. It works pretty
well ;p

—
Reply to this email directly or view it on GitHubhttps://github.com//pull/105#issuecomment-43089211
.