Issue Details (XML | Word | Printable)

Key: DNET-604
Type: Improvement Improvement
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Jiri Cincura
Reporter: Pascal
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
.NET Data provider

Improvement the time to execute parametred queries

Created: 24/Apr/15 12:37 PM   Updated: 08/Aug/15 07:18 AM
Component/s: ADO.NET Provider
Affects Version/s: 4.6.2.0
Fix Version/s: 4.7.0.0


 Description  « Hide
When FirebirdSql.Data.FirebirdClient is used to insert a lot of records in a table using parametred query, it's possible to gain time in the FbCommand.UpdateParameterValues :

Change this code :
  for (int i = 0; i < this.statement.Parameters.Count; i++)
            {
                index = i;

                if (this.namedParameters.Count > 0)
                {
                        index = this.Parameters.IndexOf(this.namedParameters[i]);
                        if (index == -1)
                        {
                            throw new FbException(String.Format("Must declare the variable '{0}'", this.namedParameters[i]));
                        }
                }

by this one :
  for (int i = 0; i < this.statement.Parameters.Count; i++)
            {
                index = i;

                if (this.namedParameters.Count > 0)
                {
                    if (this.Parameters[i].ParameterName != this.namedParameters[i]) // Added by webpac
                    {
                        index = this.Parameters.IndexOf(this.namedParameters[i]);
                        if (index == -1)
                        {
                            throw new FbException(String.Format("Must declare the variable '{0}'", this.namedParameters[i]));
                        }
                    }
                }

And then, if the namedParamers[i] is the same then Parameters[i], it doesn't looking for all nameParameters.

I tried to insert 1000 records with 50 parameters, without the optimisation, it dures 10 seconds and with the optimisation it dures 7 seconds.

Regards,

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Jiri Cincura added a comment - 25/Apr/15 05:48 PM
Maybe it would be even better to build a dictionary for it and look up in it. Haven't you tried that?

Pascal added a comment - 28/Apr/15 03:27 PM
I didn't look at all the source code to understand the différences between parameters and namedparameters.
I think it's possible that is the only performance issue when trying to find the good parameter.
But it's possible that using a dictionary will guard to do possible others performance issues so it's a good idea to use a dictionary.

Regards,

Jiri Cincura added a comment - 02/Jul/15 06:50 PM
Would you mind trying this (https://ci.appveyor.com/project/cincura_net/netprovider/build/0.0.0.171) build? I made few changes internally.

Still not sure the dictionary is worth it, because the method is called with every execution and hence the dictionary will be recreated every time.

Your check is good idea for fast-path. But needs to respect the logic of IndexOf, hence easy to break. Maybe now, with the internal changes, it will be OK.

Pascal added a comment - 08/Aug/15 07:18 AM
I got "Project not found or access denied." when I click on your link, so I can't try it.