Performance issue after upgrade .net framework

[michytang]

We have performance issue here after upgrade .net framework and Devart.

Previously: Devart 6.60.280.0 + .net 3.5
Now: Devart 9.0.280.0 n + .net 4.7

After user implement the application in the production. From the trace log, we can see it’s quite slow to retrieve and update data.
After user revert the application, this situation disappear immediately.

And when i test on my local computer in SIT, I found the performance is different when i use different database connection string
1. <add name="iEDMS" providerName="Devart.Data.Oracle"
connectionString="Direct=true;Trim Fixed Char=false;Data Source=xx.xx.xx.xx;PORT=1633;SID=xxxxx;User ID=xxxxx;Password=xxxxx"/>

2. <add name="iEDMS" providerName="Devart.Data.Oracle" connectionString="Data Source=xxxxx;User ID=xxxxx;Password=xxxxx" />

When I use the second connection string it takes only 10% of the time for the same database operation.

IDataReader reader = command.ExecuteReader(cmdBehavior);

As the user is using the first string, now it's quite slow.

We have checked our code to ensure the code is not changed when upgrade the framework.

Please advise any information you know.

Thanks.

[Shalex]

Try upgrading to the newest (9.6.558) build of dotConnect for Oracle. Does this fix the issue? If you do not have access to the licensed v9.6.558, check the same with a trial.

If the issue persists, please localize the performance problem in a small test project and upload it with the corresponding DDL/DML script to ftp://ftp.devart.com (credentials: anonymous / yourEmail).

[michytang]

Hi Shalex,

It seems the OracleCommand.ParameterCheck has issue in Version 9.4.280.0. Pasted my key test code for you to refer.
is there any way to fix the issue without upgrade the Devart or best not to re-package our solution.

Installed Version in Control Panel: 9.6.558.0
Version In Project | ParameterCheck = True | ParameterCheck = False
9.6.558.0 0.3~0.4s 0.2~0.3s
9.4.280.0 0.3~0.4s 0.2~0.3s

Installed Version in Control Panel: 9.4.280.0
Version In Project | ParameterCheck = True | ParameterCheck = False
9.6.558.0 0.3~0.4s 0.2~0.3s
9.4.280.0 1~1.2s 0.2~0.3s


public static void TestDirectConn(string connName)
{
Data.DotConnect.OracleDatabase db = new Data.DotConnect.OracleDatabase(ConfigurationManager.ConnectionStrings[connName].ConnectionString);

OracleCommand cmd = new OracleCommand();

cmd.CommandText = "ProcedureName";
cmd.CommandType = CommandType.StoredProcedure;

OracleParameter para = new OracleParameter(":STRCOND", string.Empty);
OracleParameter para1 = new OracleParameter(":OCURSOR", OracleDbType.Cursor, ParameterDirection.Output);
cmd.Parameters.Add(para);
cmd.Parameters.Add(para1);

Stopwatch sw = new Stopwatch();
sw.Start();

using (DbConnection wrapper = db.GetNewOpenConnection())
{
wrapper.Open();

cmd.Connection = (OracleConnection)wrapper;

///Switch
cmd.ParameterCheck = true;

IDataReader oReader = cmd.ExecuteReader(CommandBehavior.Default );

if (oReader != null)
{
while (oReader.Read())
{
for (int i = 0; i < oReader.FieldCount; i++)
{
string field = oReader.GetName(i);
string value = oReader.GetString(i);

}
}
}
}

sw.Stop();

Console.WriteLine("total Time for " + connName + " :" + sw.ElapsedMilliseconds / 1000.0 + "s");
}

[Shalex]

Either do not use cmd.ParameterCheck in your code with v9.4.280 or upgrade to v9.6.558.
Refer to https://devart.com/dotconnect/oracle/do ... Check.html.

[michytang]

Either do not use cmd.ParameterCheck in your code with v9.4.280 or upgrade to v9.6.558.
Refer to https://devart.com/dotconnect/oracle/do ... Check.html.

Hi Shalex,

We changed to use 9.6.558.0 as you told. and now another issue.
Our test environment is using Oracle 12c, user side 11g.

With the same code as pasted before in my reply, when calling procedure, user side report and issue, "ORA-04043: object PUBLIC does not exist". If I use user.ProcedureName, the message will be "ORA-04043: object PUBLIC.ProcedureName does not exist". but actually, we did not call or have a object named as 'PUBLIC'.

Meanwhile, pure SQL(CommandType.Text) works ok.
And in my dev environment with 12c, both versions are always ok.

When we revert to 9.4.280.0 and connect to user's database, the issue disappeared.

Exception Trace:
at Devart.Data.Oracle.d7.e()
at Devart.Data.Oracle.f.a()
at Devart.Data.Oracle.cs.a(String A_0, Int32 A_1, OracleConnection A_2)
at Devart.Data.Oracle.OracleCommand.DescribeProcedure(String name)
at Devart.Data.Oracle.OracleCommand.CreateStoredProcSql(String procName)
at Devart.Common.DbCommandBase.CreateSql()
at Devart.Common.DbCommandBase.get_Sql()
at Devart.Data.Oracle.OracleCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at Devart.Data.Oracle.OracleCommand.ExecuteReader(CommandBehavior behavior)
at DevartPerformanceTest.Program.TestDirectConn(String connName) in C:\Users\Michytang\Documents\Visual Studio 2015\Projects\DevartPerformanceTest\Program.cs:line 68



below is the sample of the procedure.

Procedure:
create or replace procedure "ProcedureName"(strCond IN VARCHAR2, oCursor OUT UTILITIES.cur_OUT) is
sSQLStatement VARCHAR2(2000);
begin

sSQLStatement := 'SELECT * FROM SampleTableName';

IF strCond IS NULL OR strCond = ' ' THEN
sSQLStatement := sSQLStatement||' WHERE 1=1 ORDER BY colomn';
ELSE
sSQLStatement := sSQLStatement||' WHERE '||strCond || 'ORDER BY colomn';
END IF;

OPEN oCursor FOR sSQLStatement;
end ProcedureName;

Package:
CREATE OR REPLACE PACKAGE Utilities IS
TYPE cur_OUT IS REF CURSOR;
END Utilities;

Package Body:
Nothing related.

Table:
CREATE TABLE "SampleTableNameFTI"
( "SampleTableNameFLTY" NUMBER(9,0),
"SampleTableNameINO" NUMBER(9,0),
"SampleTableNameDESC" VARCHAR2(15),
"SampleTableNameFINM" VARCHAR2(15),
"SampleTableNameDUP" VARCHAR2(1),
"SampleTableNameFLEN" NUMBER(9,0),
"SampleTableNameIS" VARCHAR2(1),
"SampleTableNameX1" NUMBER(9,0),
"SampleTableNameY1" NUMBER(9,0),
"SampleTableNameX2" NUMBER(9,0),
"SampleTableNameY2" NUMBER(9,0),
"SampleTableNameUSCR" VARCHAR2(8),
"SampleTableNameDTCR" DATE,
"SampleTableNameUSAM" VARCHAR2(8),
"SampleTableNameDTAM" DATE,
"SampleTableNameRQVL" VARCHAR2(1),
"SampleTableNameVDRL" VARCHAR2(4)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_SAMPLE" ;
CREATE UNIQUE INDEX "SampleTableNameFT1" ON "SampleTableNameFTI" ("SampleTableNameFLTY", "SampleTableNameINO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_SAMPLE" ;



Also, i get the Json of the OracleCommand. it's same for 9.4.280.0 and 9.6.558.0,you can check the parameters.

{
"CommandText": "ProcedueName",
"CommandType": 4,
"Connection": {
"AutoCommit": true,
"UserId": "xxx",
"Password": "xxx",
"Server": "10.129.xxx.xxx",
"Home": "",
"ClientId": "",
"Direct": true,
"Port": 1521,
"Sid": "xxx",
"ServiceName": "",
"ConnectMode": 0,
"Unicode": false,
"ConnectionString": "direct=true;trim fixed char=false;data source=10.129.xxx.xxx;port=1521;sid=xxx;user id=xxx;",
"State": 1,
"ClientVersion": "9.2.0.0",
"ServerVersion": "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production\nWith the Partitioning, OLAP, Data Mining and Real Application Testing options",
"ShortServerVersion": {
"Major": 11,
"Minor": 2,
"Build": 0,
"Revision": 4,
"MajorRevision": 0,
"MinorRevision": 4
},
"ServerType": 1,
"ConnectionTimeout": 15,
"Database": "",
"TrimFixedChar": false,
"PassParametersByName": false,
"NumberMappings": [ ],
"Name": "",
"Owner": null,
"LogicalTransactionId": null,
"OracleLogicalTransaction": null,
"ModuleName": null,
"ActionName": null,
"ClientInfo": null,
"DataSource": "10.129.xxx.xxx",
"DesignTimeVisible": true,
"InDistributedTransaction": false,
"Site": null,
"Container": null
},
"Parameters": [ "strCond" ],
"Transaction": null,
"Cached": true,
"FetchSize": 0,
"PassParametersByName": false,
"CommandTimeout": 0,
"Notification": null,
"TableValuedResultType": null,
"IsTableValuedFunction": null,
"ImplicitRefCursors": true,
"InitialLobFetchSize": 0,
"DesignTimeVisible": true,
"UpdatedRowSource": 3,
"ParameterCheck": true,
"Name": "",
"Owner": null,
"Site": null,
"Container": null
}

[Shalex]

We have created a test project basing on your description: https://www.devart.com/pub/t=37454_forum.zip.

We cannot reproduce any issue with v9.6.558.

Please modify the project or its DDL.sql and upload it to ftp://ftp.devart.com (credentials: anonymous / yourEmail) or some other file exchange server.

[michytang]

We have created a test project basing on your description: https://www.devart.com/pub/t=37454_forum.zip.

We cannot reproduce any issue with v9.6.558.

Please modify the project or its DDL.sql and upload it to ftp://ftp.devart.com (credentials: anonymous / yourEmail) or some other file exchange server.

Hi Shalex,

Your test project is same as my side.

As I investigate further. I found on user side, the user id in connection string actually is not the Owner of the procedure. Probably design for different application privileges.

user id in connection string： User_A
owner of the object: User_B

then create public SYNONYM to Procedure.

SELECT * FROM SYS.ALL_OBJECTS WHERE OBJECT_NAME LIKE 'ProcedureName%';
PUBLIC ProcedureName 129148 SYNONYM 10/13/2013 2:00:45 AM 07/18/2014 3:40:44 PM
User_B ProcedureName 129149 PROCEDURE 10/13/2013 2:20:55 AM 07/18/2014 3:40:29 PM

As i test using the Sample Project above using my user's db user,
1. when using "User_B.ProcedureName", it's ok,
2. when using "ProcedureName", ORA-04043: object PUBLIC does not exist.
3. when using "User_A.ProcedureName", ORA-04043: object PUBLIC.ProcedureName does not exist

Still the same 9.4.280.0 is ok, but 9.6.558.0 is failed as tested on my side.
Oracle DB .Net Devart Result
11g 4.7 9.4 Success
11g 4.7 9.6 Fail
11g 4.7.1 9.4 Success
11g 4.7.1 9.6 Fail
12c 4.7 9.4 Success
12c 4.7 9.6 Success
12c 4.7.1 9.4 Success
12c 4.7.1 9.6 Success

Another user luckily use the owner to visit 11g db. they gave a good news that they are ok.

Maybe this issue has non-bussiness with 12c.
Please give advise, thanks.

[Shalex]

As i test using the Sample Project above using my user's db user,
1. when using "User_B.ProcedureName", it's ok,
2. when using "ProcedureName", ORA-04043: object PUBLIC does not exist.
3. when using "User_A.ProcedureName", ORA-04043: object PUBLIC.ProcedureName does not exist

We will investigate the issue and notify you about the result.

[michytang]

As i test using the Sample Project above using my user's db user,
1. when using "User_B.ProcedureName", it's ok,
2. when using "ProcedureName", ORA-04043: object PUBLIC does not exist.
3. when using "User_A.ProcedureName", ORA-04043: object PUBLIC.ProcedureName does not exist

We will investigate the issue and notify you about the result.

Hi Shalex,

any version to replace which with better performance and no procedure issue?

[Shalex]

I've noticed a performance drop after we upgraded the .net framework as well. Will you post your findings here, Shalex?

1. Please send us a small test project with the corresponding DDL/DML script for reproducing. Also specify:
a) your provider version (x.x.x)
b) the results you are getting with different .NET Framework versions

any version to replace which with better performance and no procedure issue?

2. The investigation is in progress. As soon as we have any results, we will contact you.

[michytang]

any version to replace which with better performance and no procedure issue?

2. The investigation is in progress. As soon as we have any results, we will contact you.

As our user need to meet the timeline, may I know when the investigation will be end and new version to be ready? We cannot keep waiting.

Can you suggest a middle version of Oracle DotConnect between 9.4.280.0 and 9.6.558.0, As long as they can avoid the two issue in the topic:

1. Performance issue in 9.4,
2. procedure issue in 9.6.

You also can provide us the some trail versions, we can test by our self.

[Shalex]

2. procedure issue in 9.6.

The issue is fixed. We will provide you a trial with the fix by email.

[michytang]

2. procedure issue in 9.6.

The issue is fixed. We will provide you a trial with the fix by email.

Hi Shalex,

thanks. hope-can receive the email soon.

[Shalex]

The internal build with the fix is provided by email.

[michytang]

The internal build with the fix is provided by email.

Hi, Shalex,

thanks. the test is ok. already replied the mail to provide the official release.