FbParameterCollection use slow List<FbParameter> to find Parameters by Name [DNET1040]

[firebird-automations]

Submitted by: Baldur Fürchau (bfuerchau)

If i work with named FbParameter, Upadates/Inserts slow down. The reason seams to be, the often is used _parametes.IndexOf() or _Parameter.Contains(). The ILIst is slow in this functions and if you have a lot of parameters, assigning values needs too much time before the real insert happens.

When you use a FbDataAdapter with generated Update/Delete/Insert-Statements a lot of namend parameters are created. Why do you not use a Dictionary<String, int> and a Dictionary<FbParameter, int> to find the parameter depending on object or name? With a FbDataadapter i can't optimize this by myself, because the created parameters in the list for update is twace of column count, one for old value and one for new value to prevent unwanted updates (framework standard).

As a workaround for my inserts, i hold my own Dictionary<> to find namend parameters to assign values for the next ExecuteNonQuery and store unnamed parameters in the command. I can insert 2000 - 8000 Rows per second, depending on count of columns, with named parameters its only 500 - 2500.

[cincuranet]

Can you maybe show your code or some benchmark? I can introduce a dictionary, etc. but I'd like to also have some real-world numbers for comparison. Having the dictionary is not free and needs to be counterbalanced with tangible performance benefit.

@bfuerchau

[BFuerchau]

Sorry, i don't know, how to attach .cs-Files, but here the code: I have added two dictionaries, so the main features are not changed. The FBParameter raise an event, to rebuild the dictionaries. The performance is than important, if you deal with hundrets of them. This happens e..g, if you create updates with the Adapter. In my BulkCopy, as i told, i reach up to 30,000 inserts per second.

Additionally i have checked your newer builds concerning the reader. Actually the performance slow down dramtically, because the GetValues() to get all in an array does effectivaly run too much code. My change, i have posted in the past, has maximum read performance, because all additional checks are ignored.

/*
 *    The contents of this file are subject to the Initial
 *    Developer's Public License Version 1.0 (the "License");
 *    you may not use this file except in compliance with the
 *    License. You may obtain a copy of the License at
 *    https://github.com/FirebirdSQL/NETProvider/blob/master/license.txt.
 *
 *    Software distributed under the License is distributed on
 *    an "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either
 *    express or implied. See the License for the specific
 *    language governing rights and limitations under the License.
 *
 *    All Rights Reserved.
 */

//$Authors = Carlos Guzman Alvarez, Jiri Cincura (jiri@cincura.net)

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.Linq;

using FirebirdSql.Data.Common;
using System.Text;
using System.Collections;

namespace FirebirdSql.Data.FirebirdClient
{
    [ListBindable(false)]
    public sealed class FbParameterCollection : DbParameterCollection
    {
        #region Fields

        private Dictionary<string, int> _NamedIndex;
        private Dictionary<FbParameter, int> _PositionedIndex;

        private List<FbParameter> _parameters;
        private bool? _hasParameterWithNonAsciiName;

        #endregion

        #region Indexers

        [Browsable(false)]
        [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
        public new FbParameter this[string parameterName]
        {
            get { return this[IndexOf(parameterName)]; }
            set { this[IndexOf(parameterName)] = value; }
        }

        [Browsable(false)]
        [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
        public new FbParameter this[int index]
        {
            get { return _parameters[index]; }
            set { _parameters[index] = value; }
        }

        #endregion

        #region DbParameterCollection overriden properties

        [Browsable(false)]
        [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
        public override int Count
        {
            get { return _parameters.Count; }
        }

        public override bool IsFixedSize
        {
            get { return ((IList)_parameters).IsFixedSize; }
        }

        public override bool IsReadOnly
        {
            get { return ((IList)_parameters).IsReadOnly; }
        }

        public override bool IsSynchronized
        {
            get { return ((ICollection)_parameters).IsSynchronized; }
        }

        public override object SyncRoot
        {
            get { return ((ICollection)_parameters).SyncRoot; }
        }

        #endregion

        #region Internal properties

        internal bool HasParameterWithNonAsciiName
        {
            get
            {
                return _hasParameterWithNonAsciiName ?? (bool)(_hasParameterWithNonAsciiName = _parameters.Any(x => x.IsUnicodeParameterName));
            }
        }

        #endregion

        #region Constructors

        internal FbParameterCollection()
        {
            _parameters = new List<FbParameter>();
            _NamedIndex = new Dictionary<string, int>();
            _PositionedIndex = new Dictionary<FbParameter, int>();

            _hasParameterWithNonAsciiName = null;
        }

        #endregion

        #region DbParameterCollection overriden methods

        public void AddRange(IEnumerable<FbParameter> values)
        {
            foreach (var p in values)
            {
                Add(p);
            }
        }

        public override void AddRange(Array values)
        {
            AddRange(values.Cast<object>().Select(x => { EnsureFbParameterType(x); return (FbParameter)x; }));
        }

        public FbParameter AddWithValue(string parameterName, object value)
        {
            return Add(new FbParameter(parameterName, value));
        }

        public FbParameter Add(string parameterName, object value)
        {
            return Add(new FbParameter(parameterName, value));
        }

        public FbParameter Add(string parameterName, FbDbType type)
        {
            return Add(new FbParameter(parameterName, type));
        }

        public FbParameter Add(string parameterName, FbDbType fbType, int size)
        {
            return Add(new FbParameter(parameterName, fbType, size));
        }

        public FbParameter Add(string parameterName, FbDbType fbType, int size, string sourceColumn)
        {
            return Add(new FbParameter(parameterName, fbType, size, sourceColumn));
        }

        public FbParameter Add(FbParameter value)
        {
            EnsureFbParameterAddOrInsert(value);
            AddParameterToIndex(value, _parameters.Count);

            AttachParameter(value);
            _parameters.Add(value);
            return value;
        }

        private void CheckParameteName(string name)
        {
            if ($"{name}" != string.Empty)
            {
                if (_NamedIndex.ContainsKey(name))
                    throw new DuplicateNameException("Parametername already exists!");
            }
        }
        private void AddParameterToIndex(FbParameter value, int index)
        {
            CheckParameteName(value.ParameterName);
            _NamedIndex[value.ParameterName] = index;
            _PositionedIndex[value] = index;
        }

        public override int Add(object value)
        {
            return IndexOf(Add(EnsureFbParameterType( value)));
        }

        public bool Contains(FbParameter value) => _PositionedIndex.ContainsKey(value);
        //{
        //  return _parameters.Contains(value);
        //}

        public override bool Contains(object value)
        {
            return Contains(EnsureFbParameterType(value));
        }

        public override bool Contains(string parameterName)
        {
            return IndexOf(parameterName) != -1;
        }

        public int IndexOf(FbParameter value) => _PositionedIndex.TryGetValue(value, out int index) ? index : -1;

        public override int IndexOf(object value)
        {
            return IndexOf(EnsureFbParameterType(value));
        }

        public override int IndexOf(string parameterName)
        {
            if (!_NamedIndex.TryGetValue(parameterName, out int index))
                index = -1;
            return IndexOf(parameterName, -1);
        }

        internal int IndexOf(string parameterName, int luckyIndex)
        {
            if (luckyIndex < 0)
                if (!_NamedIndex.TryGetValue(parameterName, out luckyIndex))
                    luckyIndex = -1;

            var isNonAsciiParameterName = FbParameter.IsNonAsciiParameterName(parameterName);
            var usedComparison = isNonAsciiParameterName || HasParameterWithNonAsciiName
                ? StringComparison.CurrentCultureIgnoreCase
                : StringComparison.OrdinalIgnoreCase;
            var normalizedParameterName = FbParameter.NormalizeParameterName(parameterName);
            if (luckyIndex != -1 && luckyIndex < _parameters.Count)
            {
                if (_parameters[luckyIndex].InternalParameterName.Equals(normalizedParameterName, usedComparison))
                {
                    return luckyIndex;
                }
            }

            return _parameters.FindIndex(x => x.InternalParameterName.Equals(normalizedParameterName, usedComparison));
        }

        public void Insert(int index, FbParameter value)
        {
            EnsureFbParameterAddOrInsert(value);
            CheckParameteName(value.ParameterName);

            AttachParameter(value);
            _parameters.Insert(index, value);
            ReBuildIndex();
        }

        public override void Insert(int index, object value)
        {
            Insert(index, EnsureFbParameterType(value));
        }

        private void ReBuildIndex()
        {
            _NamedIndex.Clear();
            _PositionedIndex.Clear();
            for (int i = 0; i < _parameters.Count; i++) 
            {
                AddParameterToIndex(_parameters[i], i);
            }
        }

        public void Remove(FbParameter value)
        {
            if (!_parameters.Remove(value))
            {
                throw new ArgumentException("The parameter does not exist in the collection.");
            }
            ReBuildIndex();
            ReleaseParameter(value);
        }

        public override void Remove(object value)
        {
            Remove(EnsureFbParameterType(value));
        }

        public override void RemoveAt(int index)
        {
            if (index < 0 || index > Count)
            {
                throw new IndexOutOfRangeException("The specified index does not exist.");
            }

            var parameter = this[index];
            _parameters.RemoveAt(index);
            ReBuildIndex();
            ReleaseParameter(parameter);
        }

        public override void RemoveAt(string parameterName)
        {
            RemoveAt(IndexOf(parameterName));
        }

        public void CopyTo(FbParameter[] array, int index)
        {
            _parameters.CopyTo(array, index);
        }

        public override void CopyTo(Array array, int index)
        {
            ((IList)_parameters).CopyTo(array, index);
        }

        public override void Clear()
        {
            var parameters = _parameters.ToArray();
            _parameters.Clear();
            ReBuildIndex();
            foreach (var parameter in parameters)
            {
                ReleaseParameter(parameter);
            }
        }

        public override IEnumerator GetEnumerator()
        {
            return _parameters.GetEnumerator();
        }

        #endregion

        #region DbParameterCollection overriden protected methods

        protected override DbParameter GetParameter(string parameterName)
        {
            return this[parameterName];
        }

        protected override DbParameter GetParameter(int index)
        {
            return this[index];
        }

        protected override void SetParameter(int index, DbParameter value)
        {
            this[index] = (FbParameter)value;
        }

        protected override void SetParameter(string parameterName, DbParameter value)
        {
            this[parameterName] = (FbParameter)value;
        }

        #endregion

        #region Internal Methods

        internal void ParameterNameChanged(string oldName, string newName)
        {
            _hasParameterWithNonAsciiName = null;
            if (oldName != newName)
                ReBuildIndex();
        }

        #endregion

        #region Private Methods

        private string GenerateParameterName()
        {
            var index = Count + 1;
            while (true)
            {
                var name = "Parameter" + index.ToString(CultureInfo.InvariantCulture);
                if (!Contains(name))
                {
                    return name;
                }
                index++;
            }
        }

        private FbParameter EnsureFbParameterType(object value)
        {
            if (value is not FbParameter parameter)
            {
                throw new InvalidCastException($"The parameter passed was not a {nameof(FbParameter)}.");
            }
            return parameter;
        }

        private void EnsureFbParameterAddOrInsert(FbParameter value)
        {
            if (value == null)
            {
                throw new ArgumentNullException();
            }
            if (value.Parent != null)
            {
                throw new ArgumentException($"The {nameof(FbParameter)} specified in the value parameter is already added to this or another {nameof(FbParameterCollection)}.");
            }
            if (value.ParameterName == null || value.ParameterName.Length == 0)
            {
                value.ParameterName = GenerateParameterName();
            }
            else
            {
                if (Contains(value.ParameterName))
                {
                    throw new ArgumentException($"{nameof(FbParameterCollection)} already contains {nameof(FbParameter)} with {nameof(FbParameter.ParameterName)} '{value.ParameterName}'.");
                }
            }
        }

        private void AttachParameter(FbParameter parameter)
        {
            parameter.Parent = this;
        }

        private void ReleaseParameter(FbParameter parameter)
        {
            parameter.Parent = null;
        }

        #endregion
    }
}

``/*
 *    The contents of this file are subject to the Initial
 *    Developer's Public License Version 1.0 (the "License");
 *    you may not use this file except in compliance with the
 *    License. You may obtain a copy of the License at
 *    https://github.com/FirebirdSQL/NETProvider/blob/master/license.txt.
 *
 *    Software distributed under the License is distributed on
 *    an "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either
 *    express or implied. See the License for the specific
 *    language governing rights and limitations under the License.
 *
 *    All Rights Reserved.
 */

//$Authors = Carlos Guzman Alvarez, Jiri Cincura (jiri@cincura.net)

using System;
using System.Data;
using System.Data.Common;
using System.ComponentModel;

using FirebirdSql.Data.Common;
using System.Text;

namespace FirebirdSql.Data.FirebirdClient
{
    [ParenthesizePropertyName(true)]
    public sealed class FbParameter : DbParameter, ICloneable
    {
        #region Fields

        private FbParameterCollection _parent;
        private FbDbType _fbDbType;
        private ParameterDirection _direction;
        private DataRowVersion _sourceVersion;
        private FbCharset _charset;
        private bool _isNullable;
        private bool _sourceColumnNullMapping;
        private byte _precision;
        private byte _scale;
        private int _size;
        private object _value;
        private string _parameterName;
        private string _sourceColumn;
        private string _internalParameterName;
        private bool _isUnicodeParameterName;

        #endregion

        #region DbParameter properties

        [DefaultValue("")]
        public override string ParameterName
        {
            get { return _parameterName; }
            set
            {
                string oldName = _parameterName;
                _parameterName = value;
                _internalParameterName = NormalizeParameterName(_parameterName);
                _isUnicodeParameterName = IsNonAsciiParameterName(_parameterName);
                _parent?.ParameterNameChanged(oldName, _parameterName);
            }
        }

        [Category("Data")]
        [DefaultValue(0)]
        public override int Size
        {
            get
            {
                return (HasSize ? _size : RealValueSize ?? 0);
            }
            set
            {
                if (value < 0)
                    throw new ArgumentOutOfRangeException();

                _size = value;

                // Hack for Clob parameters
                if (value == 2147483647 &&
                    (FbDbType == FbDbType.VarChar || FbDbType == FbDbType.Char))
                {
                    FbDbType = FbDbType.Text;
                }
            }
        }

        [Category("Data")]
        [DefaultValue(ParameterDirection.Input)]
        public override ParameterDirection Direction
        {
            get { return _direction; }
            set { _direction = value; }
        }

        [Browsable(false)]
        [DesignOnly(true)]
        [DefaultValue(false)]
        [EditorBrowsable(EditorBrowsableState.Advanced)]
        public override bool IsNullable
        {
            get { return _isNullable; }
            set { _isNullable = value; }
        }

        [Category("Data")]
        [DefaultValue("")]
        public override string SourceColumn
        {
            get { return _sourceColumn; }
            set { _sourceColumn = value; }
        }

        [Category("Data")]
        [DefaultValue(DataRowVersion.Current)]
        public override DataRowVersion SourceVersion
        {
            get { return _sourceVersion; }
            set { _sourceVersion = value; }
        }

        [Browsable(false)]
        [Category("Data")]
        [RefreshProperties(RefreshProperties.All)]
        [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
        public override DbType DbType
        {
            get { return TypeHelper.GetDbTypeFromDbDataType((DbDataType)_fbDbType); }
            set { FbDbType = (FbDbType)TypeHelper.GetDbDataTypeFromDbType(value); }
        }

        [RefreshProperties(RefreshProperties.All)]
        [Category("Data")]
        [DefaultValue(FbDbType.VarChar)]
        public FbDbType FbDbType
        {
            get { return _fbDbType; }
            set
            {
                _fbDbType = value;
                IsTypeSet = true;
            }
        }

        [Category("Data")]
        [TypeConverter(typeof(StringConverter)), DefaultValue(null)]
        public override object Value
        {
            get { return _value; }
            set
            {
                if (value == null)
                {
                    value = DBNull.Value;
                }

                if (FbDbType == FbDbType.Guid && value != null &&
                    value != DBNull.Value && !(value is Guid) && !(value is byte[]))
                {
                    throw new InvalidOperationException("Incorrect Guid value.");
                }

                _value = value;

                if (!IsTypeSet)
                {
                    SetFbDbType(value);
                }
            }
        }

        [Category("Data")]
        [DefaultValue(FbCharset.Default)]
        public FbCharset Charset
        {
            get { return _charset; }
            set { _charset = value; }
        }

        public override bool SourceColumnNullMapping
        {
            get { return _sourceColumnNullMapping; }
            set { _sourceColumnNullMapping = value; }
        }

        #endregion

        #region Properties

        [Category("Data")]
        [DefaultValue((byte)0)]
        public override byte Precision
        {
            get { return _precision; }
            set { _precision = value; }
        }

        [Category("Data")]
        [DefaultValue((byte)0)]
        public override byte Scale
        {
            get { return _scale; }
            set { _scale = value; }
        }

        #endregion

        #region Internal Properties

        internal FbParameterCollection Parent
        {
            get { return _parent; }
            set
            {
                _parent?.ParameterNameChanged(_parameterName, _parameterName);
                _parent = value;
                _parent?.ParameterNameChanged(_parameterName, _parameterName);
            }
        }

        internal string InternalParameterName
        {
            get
            {
                return _internalParameterName;
            }
        }

        internal bool IsTypeSet { get; private set; }

        internal object InternalValue
        {
            get
            {
                switch (_value)
                {
                    case string svalue:
                        return svalue.Substring(0, Math.Min(Size, svalue.Length));
                    case byte[] bvalue:
                        var result = new byte[Math.Min(Size, bvalue.Length)];
                        Array.Copy(bvalue, result, result.Length);
                        return result;
                    default:
                        return _value;
                }
            }
        }

        internal bool HasSize
        {
            get { return _size != default; }
        }

        #endregion

        #region Constructors

        public FbParameter()
        {
            _fbDbType = FbDbType.VarChar;
            _direction = ParameterDirection.Input;
            _sourceVersion = DataRowVersion.Current;
            _sourceColumn = string.Empty;
            _parameterName = string.Empty;
            _charset = FbCharset.Default;
            _internalParameterName = string.Empty;
        }

        public FbParameter(string parameterName, object value)
            : this()
        {
            ParameterName = parameterName;
            Value = value;
        }

        public FbParameter(string parameterName, FbDbType fbType)
            : this()
        {
            ParameterName = parameterName;
            FbDbType = fbType;
        }

        public FbParameter(string parameterName, FbDbType fbType, int size)
            : this()
        {
            ParameterName = parameterName;
            FbDbType = fbType;
            Size = size;
        }

        public FbParameter(string parameterName, FbDbType fbType, int size, string sourceColumn)
            : this()
        {
            ParameterName = parameterName;
            FbDbType = fbType;
            Size = size;
            _sourceColumn = sourceColumn;
        }

        [EditorBrowsable(EditorBrowsableState.Advanced)]
        public FbParameter(
            string parameterName,
            FbDbType dbType,
            int size,
            ParameterDirection direction,
            bool isNullable,
            byte precision,
            byte scale,
            string sourceColumn,
            DataRowVersion sourceVersion,
            object value)
        {
            ParameterName = parameterName;
            FbDbType = dbType;
            Size = size;
            _direction = direction;
            _isNullable = isNullable;
            _precision = precision;
            _scale = scale;
            _sourceColumn = sourceColumn;
            _sourceVersion = sourceVersion;
            Value = value;
            _charset = FbCharset.Default;
        }

        #endregion

        #region ICloneable Methods
        object ICloneable.Clone()
        {
            return new FbParameter(
                _parameterName,
                _fbDbType,
                _size,
                _direction,
                _isNullable,
                _precision,
                _scale,
                _sourceColumn,
                _sourceVersion,
                _value)
            {
                Charset = _charset
            };
        }

        #endregion

        #region DbParameter methods

        public override string ToString()
        {
            return _parameterName;
        }

        public override void ResetDbType()
        {
            throw new NotImplementedException();
        }

        #endregion

        #region Private Methods

        private void SetFbDbType(object value)
        {
            if (value == null)
            {
                value = DBNull.Value;
            }
            _fbDbType = TypeHelper.GetFbDataTypeFromType(value.GetType());
        }

        #endregion

        #region Private Properties

        private int? RealValueSize
        {
            get
            {
                var svalue = (_value as string);
                if (svalue != null)
                {
                    return svalue.Length;
                }
                var bvalue = (_value as byte[]);
                if (bvalue != null)
                {
                    return bvalue.Length;
                }
                return null;
            }
        }

        internal bool IsUnicodeParameterName
        {
            get
            {
                return _isUnicodeParameterName;
            }
        }

        #endregion

        #region Static Methods

        internal static string NormalizeParameterName(string parameterName)
        {
            return string.IsNullOrEmpty(parameterName) || parameterName[0] == '@'
                ? parameterName
                : "@" + parameterName;
        }

        internal static bool IsNonAsciiParameterName(string parameterName)
        {
            return string.IsNullOrEmpty(parameterName) || Encoding.UTF8.GetByteCount(parameterName) != parameterName.Length;
        }

        #endregion
    }
}

[cincuranet]

I mean code that shows some performance numbers from a real-world code.

[BFuerchau]

I'm sorry, i don't have such performancenumbers. It's only a hint, to do this. You can test by yourself to compare List to Dictionary<> to check if a Name of the parameter is already within the list. Dictionary = 1 check (and calculate hash) List = always N checks, while N is growing Also the IndexOf is often called, where the Dictionary is faster. Access this[Name] becomes faster. And so on.

Instead of Dictionary you can use now HybridDictionary: https://docs.microsoft.com/en-us/dotnet/api/system.collections.specialized.hybriddictionary?view=netframework-4.8

[cincuranet]

Again, I'm not saying there isn't a difference. But without real world cases it's optimization for optimization.