CODEWITHSUNDEEP
c#dapper
Torben Nielsen
Torben Nielsen

Reputation: 833

Can Dapper release database connection early?

We are usinig Dapper to execute SQL in IBM's Db2 on a mainframe Our code to execute SQL is pretty standard, I think. Get a connection from the connection pool and use Dapper to execute the SQL. Then release the connection to the pool

    using var connection = GetConnection();
    {
        return await connection.QueryAsync<dynamic>(sql, new { instrumentIds });
    }

The problem is that while it only takes DB2 a couple of milliseconds to execute the SQL, Dapper will use about 100 milliseconds to unpack thousands of rows with maybe 20 columns each

So the connection is also locked while Dapper unpacks the result, and evenutally our application is starved for connections to DB2

If the connection could be released by Dapper when the SQL is executed at DB2 and before Dapper unpacks the result, we could multiply requests we can process by tenfold without increasing connections

Is that possible?

I have tried adding elapse time logging around the ExecuteReaderAsync call in Dapper, and I can confirm that this call only uses 10 milliseconds, while the entire QueryAsync call uses 140 milliseconds

    private static Task<DbDataReader> ExecuteReaderWithFlagsFallbackAsync(DbCommand cmd, bool wasClosed, CommandBehavior behavior, CancellationToken cancellationToken)
    {
        var sw = Stopwatch.StartNew();
        var task = cmd.ExecuteReaderAsync(GetBehavior(wasClosed, behavior), cancellationToken);
        sw.Stop();
        Console.WriteLine($" ### ExecuteReaderAsync took {sw.ElapsedMilliseconds}");
        if (task.Status == TaskStatus.Faulted && Settings.DisableCommandBehaviorOptimizations(behavior, task.Exception.InnerException))
        { // we can retry; this time it will have different flags
            return cmd.ExecuteReaderAsync(GetBehavior(wasClosed, behavior), cancellationToken);
        }
        return task;
    }

Upvotes: 2

Views: 369

Answers (1)

Torben Nielsen
Torben Nielsen

Reputation: 833

Thanks for the comments

As pointed out my measurement is incorrect, as ExecuteReaderAsync does not read the results

Modifying my measurements to more correctly include actually reading the result from DB2, reveals that almost the entire time is spent reading the result. Not in Dappers unpack

            if (command.Buffered)
            {
                var buffer = new List<T>();
                var convertToType = Nullable.GetUnderlyingType(effectiveType) ?? effectiveType;
                var swReadAsyncAndUnpack = Stopwatch.StartNew();
                var swReadAsync = Stopwatch.StartNew();
                while (await reader.ReadAsync(cancel).ConfigureAwait(false))
                {
                    swReadAsync.Stop();
                    object val = func(reader);
                    buffer.Add(GetValue<T>(reader, effectiveType, val));
                    swReadAsync.Start();
                }
                swReadAsync.Stop();
                swReadAsyncAndUnpack.Stop();
                Console.WriteLine($" ### All ReadAsync took {swReadAsync.ElapsedMilliseconds}");
                Console.WriteLine($" ### All ReadAsync and unpack took {swReadAsync.ElapsedMilliseconds}");
                while (await reader.NextResultAsync(cancel).ConfigureAwait(false)) { /* ignore subsequent result sets */ }
                command.OnCompleted();
                return buffer;
            }

I tthink we need to involve IBM in this, as time spent with ReadAsync in IBM.Data.DB2.Core does not match the elapse time which our DBA can measure within DB2

Upvotes: 1

Related Questions