Page 1 of 2
Poor performance in DirectMode
Posted: Fri 29 Sep 2017 14:54
by 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
Re: Poor performance in DirectMode
Posted: Tue 03 Oct 2017 14:47
by 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.
Re: Poor performance in DirectMode
Posted: Thu 05 Oct 2017 06:10
by 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..
Re: Poor performance in DirectMode
Posted: Thu 19 Oct 2017 14:56
by 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
Re: Poor performance in DirectMode
Posted: Tue 24 Oct 2017 11:49
by 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.
Re: Poor performance in DirectMode
Posted: Tue 24 Oct 2017 13:42
by 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....
Re: Poor performance in DirectMode
Posted: Tue 24 Oct 2017 15:49
by 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.
Re: Poor performance in DirectMode
Posted: Wed 25 Oct 2017 12:21
by 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"
Re: Poor performance in DirectMode
Posted: Fri 27 Oct 2017 13:27
by 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?
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?
Re: Poor performance in DirectMode
Posted: Fri 27 Oct 2017 14:33
by 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
Re: Poor performance in DirectMode
Posted: Mon 30 Oct 2017 14:28
by 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.
Re: Poor performance in DirectMode
Posted: Mon 30 Oct 2017 15:28
by hansjoergp
Okay. Tomorrow I will send you an example.
In OCI mode everything works and I get the expected value
Re: Poor performance in DirectMode
Posted: Tue 31 Oct 2017 08:13
by hansjoergp
I have send you the example with the contact form...
Re: Poor performance in DirectMode
Posted: Tue 31 Oct 2017 17:12
by Pinturiccio
Thank you for the provided project. We have reproduced the issue. We will investigate it and post the results here.
Re: Poor performance in DirectMode
Posted: Mon 13 Nov 2017 14:53
by hansjoergp
Do you have any news?