Cavenous Performance Difference - Direct and Native

Cavenous Performance Difference - Direct and Native

Postby daveoggy » Tue 11 Jan 2011 15:39

Hi

I am using dotConnect for Oracle in a reporting application. One of the people who designs the reports complained that SQL she can run in < 1 second in SQL developer, takes more than a minute when used in a report.

I timed the datatable being filled by the report and saw that it was taking more than a minute.

I then decided to change the connection type to direct and saw that the fill took < 1 second for the exact same report/code/sql.

I do not believe that such a large discrepency is intended. Hopefully this is just a known issue with the version of dotConnect for Oracle being used: 5.70.170.0
daveoggy
 
Posts: 14
Joined: Fri 23 Jan 2009 18:15

Postby Shalex » Wed 12 Jan 2011 15:44

If possible, please try using another version of Oracle Client. Also try the latest version (6.0.86) of dotConnect for Oracle. If this doesn't help:
1) please send us a small test project with the corresponding DDL/DML script to reproduce the issue in our environment;
2) the versions of your Oracle Client and Oracle Server.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Postby TheGramix » Fri 14 Jan 2011 06:40

Hello both,
I have the same problem on my system.

Query from toad 250ms, the same from web (devart) 7-8 seconds.

This is what I tried to solve the problem:

1. Connection without oracle client:
in this case the query is slow but if I add the oracle HINTS (/*+ INDEX(...)*/) the query is fast. Not a good solution because I skip the optimizer of oracle.

2. Connection with instant client
the same result as point 1.

3. Connection with Direct mode and SID
this is fast but:
- Oracle RAC (Real Application Cluster) is not supported.
- Direct mode does not support NLS conversion on client side.
- Statement caching is not available.

I have to use a RAC and NLS conversion.

4. Connection with Direct mode and Service Name
the same as point 3 but I don't know if the 3 remarks are valid also in this case (help file seems to be the same of previous version)

Here example of connections:











I am using oracle 11.2, instant client 11.2.0.1.0, DevArt 6.0.86.

My query are complex (WITH ... AS, subqueries, PIVOT, etc etc) and prepare a sample is complicated but I can try it.

Another info, I don't have the problem with ODP.NET, in this case the query is fast with or without HINTS.

I don't like ODP.NET and I would like to use Devart, could you help me?
TheGramix
 
Posts: 8
Joined: Fri 14 Jan 2011 06:14

Postby Shalex » Tue 18 Jan 2011 12:34

TheGramix, here are some comments:

1. There are two connection types: the OCI mode (doesn't depend on the used type of Oracle Client installation) and the Direct mode (doesn't matter whether SID or Service Name is used). So it makes sense to create only two test cases (OCI and Direct).

2. Have you tried to reproduce this issue without using RAC? If it persists with RAC only, please describe the structure of your RAC (key settings) so that we can re-create it in our environment.

3. Send us a small test project to reproduce the issue in our environment with the corresponding DDL/DML script.

4. The same code works with ODP.NET, doesn't it?
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle