Poor performance in DirectMode

[hansjoergp]

Hello,

we have build now a version of our software with the dotConnect driver (as ORM-Mapper we use XPO from devexpress).
If we use it in direct mode the test application takes always about 1 min. If we use direct = false, than it takes about 8 sec.
Without devart the application takes about 6sec.

The application have calls to different stored procedures, inserts and selects.

What I can do to make the application faster? For us it is really important to use the direct mode, because we don't want to install the Oracle Client.

Regards
Hansjörg

[Pinturiccio]

Please create and Send us a simple test project, which reproduces the issue. If the project requires DDL/DML scripts for database objects, please send them too.

If your files have a size of several megabytes or more in total, you can archive them and upload it to our ftp server ( ftp://ftp.devart.com , credentials: anonymous/anonymous ) or to any file exchange server so that we could download it from there. And Send us the password to the archive.

[hansjoergp]

I will check if it is possible to extract the parts to a small programm..the application (and database) is really complicated and so this is not so easy..

[hansjoergp]

I have made now a lot of tests. One Point which leads to a really bad performance is the switch DescribeStoredProcedure from OracleConnectionStringBuilder. If I set it to false it runs a lot faster.

Another strange thing is that it seems that the performance is not on every machine bad. I have a Virtual Box where I make the tests and there my test program takes e.g 6 seconds to run and the Oracle Client takes 2.5 seconds. On the host machine the Devart driver with direct mode is only a little bit slower.

On a client PC we have the same problem. I don't know at the moment how I can make a test program so that you can check the problem.

Do you have any idea

[Pinturiccio]

At the moment Direct is slower than OCI. However, we made big changes to our provider. With these changes Direct mode performance was greatly improved. We are testing these changes, and we will notify you when they will be available in a public build. We will send you test Devart assemblies with these changes via email.

[hansjoergp]

On my test machine there is no difference between the test driver and the original one. If I have enabled DescribeStoredProcedure than the runtime is 1 min 27 sec. If it is disabled it is 23sec. With the oracle driver it is 3 sec....

[Pinturiccio]

Please make sure that the assemblies, which we sent to you, are used instead of the assemblies installed with dotConnect for Oracle. If you add the received assemblies to your project, but dotConnect for Oracle is installed, then assemblies from the GAC will be used. You can debug your project and see the assembly versions and from where they are loaded.

[hansjoergp]

You are right. The application used the assemblies from the GAC. I have now uninstalled the assemblies from GAC and now it uses the assemblies which you send me. But now nothing is working anymore.

Code: Select all

OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder();

oraCSB.Server = server;
oraCSB.Sid = sid;
oraCSB.Port = port;
oraCSB.Direct = true;
oraCSB.DescribeStoredProcedure = false;
oraCSB.UserId = userid;
oraCSB.Password = password;

var connection = new OracleConnection(oraCSB.ConnectionString);
connection.Open();
connection.CreateCommand("select * from (select N0.SNID as F0 from SERIENNUMMERN N0 where N0.SNHERSTELLERSERIENNUMMER = 'HP_DRUCKER_2_STOCK_2') where RowNum <= 1").ExecuteScalar();

With this code I don't get anymore an value. With the old version I get an value.

The log with dbmonitor is

25.10.2017 13:49:20 n/a dotConnect for Oracle monitoring is started Complete
25.10.2017 13:49:20 0,000 Creating pool manager Complete
25.10.2017 13:49:20 0,016 Creating pool with connections string: "User Id=RADIX;Server=ORACLEDEV2;Direct=True;Sid=ODI202;Port=1521;Describe Stored Procedure=False" Complete
25.10.2017 13:49:20 0,000 Creating object Complete
25.10.2017 13:49:20 0,641 Open connection: "User Id=RADIX;Server=ORACLEDEV2;Direct=True;Sid=ODI202;Port=1521;Describe Stored Procedure=False" Complete
25.10.2017 13:49:20 0,344 Connect: "User Id=RADIX;Server=ORACLEDEV2;Direct=True;Sid=ODI202;Port=1521;Describe Stored Procedure=False" Complete
25.10.2017 13:49:22 0,000 Creating object Complete
25.10.2017 13:49:22 0,000 Prepare: select * from (select N0.SNID as F0 from SERIENNUMMERN N0 where N0.SNHERSTELLERSERIENNUMMER = 'HP_DRUCKER_2_STOCK_2') where RowNum <= 1 Complete
25.10.2017 13:49:22 0,031 Execute: select * from (select N0.SNID as F0 from SERIENNUMMERN N0 where N0.SNHERSTELLERSERIENNUMMER = 'HP_DRUCKER_2_STOCK_2') where RowNum <= 1 Complete

Do you have any idea?
We use "Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production"

[Pinturiccio]

We could not reproduce the issue. We successfully receive a value from Oracle 12c with the same query. The value, returned by ExecuteScalar, is not assigned to any variable in your example. Can this be the reason why you think that nothing is returned?

25.10.2017 13:49:22 0,031 Execute: select * from (select N0.SNID as F0 from SERIENNUMMERN N0 where N0.SNHERSTELLERSERIENNUMMER = 'HP_DRUCKER_2_STOCK_2') where RowNum <= 1 Complete

You use the dbMonitor tool too. Select this row and look in the SQL window. There should be the number of row(s) affected. What is displayed in your case?

Try these assemblies also with the OCI mode. Which are the results?

[hansjoergp]

I forgot only to copy the assignment.
With direct mode set to false I get an value.
If I set it to true than the value is "\0". If I go to the SQL tab than I get always "-- -1 row(s) affected.". I have made now also an test with an Oracle 9 database and there is the same problem

[Pinturiccio]

Please send us DDL/DML scripts of tables with which this issue can be reproduced. Please also create and send us a small complete test project which reproduces the issue with these DDL\DML scripts.
Tell us, which result is returned in the OCI mode.

[hansjoergp]

Okay. Tomorrow I will send you an example.

In OCI mode everything works and I get the expected value

[hansjoergp]

I have send you the example with the contact form...

[Pinturiccio]

Thank you for the provided project. We have reproduced the issue. We will investigate it and post the results here.

[hansjoergp]

Do you have any news?