Poor performance in DirectMode

Poor performance in DirectMode

Postby hansjoergp » Fri 29 Sep 2017 14:54

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
hansjoergp
 
Posts: 12
Joined: Wed 31 May 2017 14:33

Re: Poor performance in DirectMode

Postby Pinturiccio » Tue 03 Oct 2017 14:47

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.
Pinturiccio
Devart Team
 
Posts: 2046
Joined: Wed 02 Nov 2011 09:44

Re: Poor performance in DirectMode

Postby hansjoergp » Thu 05 Oct 2017 06:10

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
 
Posts: 12
Joined: Wed 31 May 2017 14:33

Re: Poor performance in DirectMode

Postby hansjoergp » Thu 19 Oct 2017 14:56

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
hansjoergp
 
Posts: 12
Joined: Wed 31 May 2017 14:33

Re: Poor performance in DirectMode

Postby Pinturiccio » Tue 24 Oct 2017 11:49

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.
Pinturiccio
Devart Team
 
Posts: 2046
Joined: Wed 02 Nov 2011 09:44

Re: Poor performance in DirectMode

Postby hansjoergp » Tue 24 Oct 2017 13:42

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....
hansjoergp
 
Posts: 12
Joined: Wed 31 May 2017 14:33

Re: Poor performance in DirectMode

Postby Pinturiccio » Tue 24 Oct 2017 15:49

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.
Pinturiccio
Devart Team
 
Posts: 2046
Joined: Wed 02 Nov 2011 09:44

Re: Poor performance in DirectMode

Postby hansjoergp » Wed 25 Oct 2017 12:21

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"
hansjoergp
 
Posts: 12
Joined: Wed 31 May 2017 14:33

Re: Poor performance in DirectMode

Postby Pinturiccio » Fri 27 Oct 2017 13:27

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?

hansjoergp wrote: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?
Pinturiccio
Devart Team
 
Posts: 2046
Joined: Wed 02 Nov 2011 09:44

Re: Poor performance in DirectMode

Postby hansjoergp » Fri 27 Oct 2017 14:33

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
hansjoergp
 
Posts: 12
Joined: Wed 31 May 2017 14:33

Re: Poor performance in DirectMode

Postby Pinturiccio » Mon 30 Oct 2017 14:28

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.
Pinturiccio
Devart Team
 
Posts: 2046
Joined: Wed 02 Nov 2011 09:44

Re: Poor performance in DirectMode

Postby hansjoergp » Mon 30 Oct 2017 15:28

Okay. Tomorrow I will send you an example.

In OCI mode everything works and I get the expected value
hansjoergp
 
Posts: 12
Joined: Wed 31 May 2017 14:33

Re: Poor performance in DirectMode

Postby hansjoergp » Tue 31 Oct 2017 08:13

I have send you the example with the contact form...
hansjoergp
 
Posts: 12
Joined: Wed 31 May 2017 14:33

Re: Poor performance in DirectMode

Postby Pinturiccio » Tue 31 Oct 2017 17:12

Thank you for the provided project. We have reproduced the issue. We will investigate it and post the results here.
Pinturiccio
Devart Team
 
Posts: 2046
Joined: Wed 02 Nov 2011 09:44

Re: Poor performance in DirectMode

Postby hansjoergp » Mon 13 Nov 2017 14:53

Do you have any news?
hansjoergp
 
Posts: 12
Joined: Wed 31 May 2017 14:33

Next

Return to dotConnect for Oracle