[Firebird-net-provider] [FB-Tracker] Created: (DNET-532) The performance when doing parametrized builk inserts is poor

[Toni M. (JIRA) <tr...@fi...>]

The performance when doing parametrized builk inserts is poor
-------------------------------------------------------------

                 Key: DNET-532
                 URL: http://tracker.firebirdsql.org/browse/DNET-532
             Project: .NET Data provider
          Issue Type: Improvement
          Components: ADO.NET Provider
    Affects Versions: 4.0.0.0
         Environment: Any
            Reporter: Toni Martir
            Assignee: Jiri Cincura


The performance when executing a query with named parameters (50 or more) is very poor.
Example:
 With current code it  takes about 40 seconds to insert 20.000 records.
 With small optimizations it take about 5 seconds.

The problem is in FbParameterCollection.IndexOf

I obtained the much better results modifying FbParameterCollection and FbParameter,
but may be this is not the correct way...

With the proposed optimization only FbParameterCollection must be modified and small modification to FbParameter.

      SortedList<string, int> sortedparams;
      SortedList<string, int> sortedparamsint;

Constructor
         this.sortedparams = new SortedList<string, int>();
         this.sortedparamsint = new SortedList<string, int>();

Add
            this.parameters.Add(value);
            sortedparams.Add(value.ParameterName,this.parameters.Count-1);
            sortedparamsint.Add(value.InternalParameterName, this.parameters.Count - 1);
IndexOf
         int idx = sortedparamsint.IndexOfKey(parameterName);
         if (idx == -1)
            idx = sortedparams.IndexOfKey(parameterName);
         return idx;

To detect parameter changes, in FbParameter:
		public override string ParameterName
		{
			get { return this.parameterName; }
			set { 
             this.parameterName = value;
             if (this.parent != null)
                this.Parent.RebuildSorted();
         }
		}

And define RebuildSorted in the FbParameterCollection
      internal void RebuildSorted()
      {
         sortedparams.Clear();
         sortedparamsint.Clear();
         for (int idx = 0;idx <parameters.Count;idx++)
         {
            sortedparams.Add(parameters[idx].ParameterName,idx);
            sortedparamsint.Add(parameters[idx].InternalParameterName,idx);
         }
      }



-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira