Hello,
I think I found a performance issue in Oracle.ManagedDataAcces.Client.
The profiler I use shows me that the method OracleParameterCollection.FindParamByName takes about 25% execution time. It's especially visible when parameters count is greater than 200.
In our real application such situation happens when we update (or insert) the tables containing more than 100 columns. The batch job executed by our application normally takes about 130 seconds and 9 seconds is taken by FindParamByName.
I used BindByName=true, because Entity Framework provider (Oracle.ManagedDataAccess.EntityFramework) also does it.
It looks that there is a loop that iterates through all parameters and compares their names with ignoreCase=true. I suppose that some kind of dictionary for storing name => OracleParameter and name => parameter index could help.
I reproduced it in a program that executes simple: "select :param1 as ..., :param2 ... from dual". I'm attaching this program to this post.
On the screenshot below you can see the profiler result. I used a setup with 500 parameters and 100 iterations.
Used DLL: Oracle.ManagedDataAccess, 4.122.19.1:20210305
Program.txt (3.97 KB)
Edit:
I found a following bug report:
Performance Problems With ODPU 12.1.0.2 When Using Named Parameters (0 Bytes)What happened with that?