Slowness when using large number of dynamic parameters

[quietkatalyst]

When using a large number of query parameters, Dapper appears to take an extremely long time to add the parameters to the command before executing it. It took ~40 seconds to add 11k parameters.

I verified it was not an issue with casting or the query itself by grabbing the plan explanation for the query out of the logs. (I am using Postgres and used the auto explain module to generate it, which hopefully removes .) Running the query directly on the database executes fairly quickly (< 1 second)

I also used the debugger to verify it was an issue with Dapper itself - it looks like the majority of time is spent in the AddParameters method in the DynamicParameters class.

I also tested out explicitly specifying the db type of the parameter (DbString), as well as the length and input direction, but that did not seem to have any significant effect on the performance.

I do have a workaround, as this particular query can be split up into batches, I but just wanted to verify that this a limitation of Dapper, and make sure there isn't a different way to approach the problem.

Edit: Forgot to mention, I'm seeing this behavior with version 2.0.35.

[jehhynes]

I am having the same issue using SQL Server. I narrowed this down to the following line in DynamicParameters:

bool add = !command.Parameters.Contains(name);

This is called for every single parameter, and the implementation (Microsoft.Data.SqlClient.SqlParameterCollection) is a loop of all parameters:

      public override bool Contains(string value) => IndexOf(value) != -1;

       public override int IndexOf(string parameterName)
       {
           List<SqlParameter> items = InnerList;
           if (items != null)
           {
               int i = 0;

               foreach (SqlParameter parameter in items)
               {
                   if (parameterName == parameter.ParameterName)
                   {
                       return i;
                   }
                   ++i;
               }
               i = 0;

               foreach (SqlParameter parameter in items)
               {
                   if (CultureInfo.CurrentCulture.CompareInfo.Compare(parameterName, parameter.ParameterName, ADP.DefaultCompareOptions) == 0)
                   {
                       return i;
                   }
                   ++i;
               }
           }
           return -1;
       }

This combination makes this a n-squared algorithm which explains the slowness.

As a workaround, you can use a custom implementation of IDynamicParameters to suit your needs without using the Contains() method this way. Here is my implementation:

#nullable enable
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Reflection.Emit;

namespace Dapper
{
    /// <summary>
    /// A hacked-down version of Dapper.DynamicParameters which solves some performance issues.
    /// </summary>
    public class SimpleDynamicParameters : SqlMapper.IDynamicParameters, SqlMapper.IParameterLookup
    {
        private readonly Dictionary<string, ParamInfo> parameters = new Dictionary<string, ParamInfo>();

        object? SqlMapper.IParameterLookup.this[string name] =>
            parameters.TryGetValue(name, out ParamInfo? param) ? param.Value : null;

        /// <summary>
        /// Add a parameter to this dynamic parameter list.
        /// </summary>
        /// <param name="name">The name of the parameter.</param>
        /// <param name="value">The value of the parameter.</param>
        /// <param name="dbType">The type of the parameter.</param>
        /// <param name="direction">The in or out direction of the parameter.</param>
        /// <param name="size">The size of the parameter.</param>
        public void Add(string name, object? value, DbType? dbType, ParameterDirection? direction, int? size)
        {
            parameters[Clean(name)] = new ParamInfo
            {
                Name = name,
                Value = value,
                ParameterDirection = direction ?? ParameterDirection.Input,
                DbType = dbType,
                Size = size
            };
        }

        /// <summary>
        /// Add a parameter to this dynamic parameter list.
        /// </summary>
        /// <param name="name">The name of the parameter.</param>
        /// <param name="value">The value of the parameter.</param>
        /// <param name="dbType">The type of the parameter.</param>
        /// <param name="direction">The in or out direction of the parameter.</param>
        /// <param name="size">The size of the parameter.</param>
        /// <param name="precision">The precision of the parameter.</param>
        /// <param name="scale">The scale of the parameter.</param>
        public void Add(string name, object? value = null, DbType? dbType = null, ParameterDirection? direction = null, int? size = null, byte? precision = null, byte? scale = null)
        {
            parameters[Clean(name)] = new ParamInfo
            {
                Name = name,
                Value = value,
                ParameterDirection = direction ?? ParameterDirection.Input,
                DbType = dbType,
                Size = size,
                Precision = precision,
                Scale = scale
            };
        }

        private static string Clean(string name)
        {
            if (!string.IsNullOrEmpty(name))
            {
                switch (name[0])
                {
                    case '@':
                    case ':':
                    case '?':
                        return name.Substring(1);
                }
            }
            return name;
        }

        void SqlMapper.IDynamicParameters.AddParameters(IDbCommand command, SqlMapper.Identity identity)
        {
            AddParameters(command, identity);
        }

        internal static bool ShouldSetDbType(DbType? dbType)
            => dbType.HasValue;

        internal static bool ShouldSetDbType(DbType dbType)
            => true;

        /// <summary>
        /// Add all the parameters needed to the command just before it executes
        /// </summary>
        /// <param name="command">The raw command prior to execution</param>
        /// <param name="identity">Information about the query</param>
        protected void AddParameters(IDbCommand command, SqlMapper.Identity identity)
        {
            Microsoft.Data.SqlClient.SqlCommand

            foreach (var param in parameters.Values)
            {
                var dbType = param.DbType;
                var val = param.Value;
                string name = Clean(param.Name);
                var isCustomQueryParameter = val is SqlMapper.ICustomQueryParameter;

                SqlMapper.ITypeHandler? handler = null;
                if (dbType is null && val is not null && !isCustomQueryParameter)
                {
#pragma warning disable 618
                    dbType = SqlMapper.LookupDbType(val.GetType(), name, true, out handler);
#pragma warning disable 618
                }

                IDbDataParameter p = command.CreateParameter();
                p.ParameterName = name;

                p.Direction = param.ParameterDirection;
                if (handler is null)
                {
#pragma warning disable 0618
                    p.Value = SqlMapper.SanitizeParameterValue(val);
#pragma warning restore 0618
                    if (ShouldSetDbType(dbType) && p.DbType != dbType.GetValueOrDefault())
                    {
                        p.DbType = dbType.GetValueOrDefault();
                    }
                    var s = val as string;
                    if (s?.Length <= DbString.DefaultLength)
                    {
                        p.Size = DbString.DefaultLength;
                    }
                    if (param.Size is not null) p.Size = param.Size.Value;
                    if (param.Precision is not null) p.Precision = param.Precision.Value;
                    if (param.Scale is not null) p.Scale = param.Scale.Value;
                }
                else
                {
                    if (ShouldSetDbType(dbType)) p.DbType = dbType.GetValueOrDefault();
                    if (param.Size is not null) p.Size = param.Size.Value;
                    if (param.Precision is not null) p.Precision = param.Precision.Value;
                    if (param.Scale is not null) p.Scale = param.Scale.Value;
                    handler.SetValue(p, val ?? DBNull.Value);
                }

                command.Parameters.Add(p);
                param.AttachedParam = p;
            }
        }

        /// <summary>
        /// All the names of the param in the bag, use Get to yank them out
        /// </summary>
        public IEnumerable<string> ParameterNames => parameters.Select(p => p.Key);

        /// <summary>
        /// Get the value of a parameter
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="name"></param>
        /// <returns>The value, note DBNull.Value is not returned, instead the value is returned as null</returns>
        public T Get<T>(string name)
        {
            var paramInfo = parameters[Clean(name)];
            var attachedParam = paramInfo.AttachedParam;
            object? val = attachedParam is null ? paramInfo.Value : attachedParam.Value;
            if (val == DBNull.Value)
            {
                if (default(T) is not null)
                {
                    throw new ApplicationException("Attempting to cast a DBNull to a non nullable type! Note that out/return parameters will not have updated values until the data stream completes (after the 'foreach' for Query(..., buffered: false), or after the GridReader has been disposed for QueryMultiple)");
                }
                return default!;
            }
            return (T)val!;
        }

        private sealed class ParamInfo
        {
            public string Name { get; set; } = null!;
            public object? Value { get; set; }
            public ParameterDirection ParameterDirection { get; set; }
            public DbType? DbType { get; set; }
            public int? Size { get; set; }
            public IDbDataParameter AttachedParam { get; set; } = null!;

            public byte? Precision { get; set; }
            public byte? Scale { get; set; }
        }
 
   }
}

[jehhynes]

PR created #2037