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
Cavenous Performance Difference - Direct and Native
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.
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.
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:
)(PORT=))(LOAD_BALANCE = YES)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=)));User Id=***;Password=***;" providerName="Devart.Data.Oracle"/>
" providerName="Devart.Data.Oracle" />
;Direct=True;Sid=;Port=;Persist Security Info=True" providerName="Devart.Data.Oracle" />
;Direct=True;Service Name=;Port=;Persist Security Info=True" providerName="Devart.Data.Oracle" />
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?
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:
)(PORT=))(LOAD_BALANCE = YES)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=)));User Id=***;Password=***;" providerName="Devart.Data.Oracle"/>
" providerName="Devart.Data.Oracle" />
;Direct=True;Sid=;Port=;Persist Security Info=True" providerName="Devart.Data.Oracle" />
;Direct=True;Service Name=;Port=;Persist Security Info=True" providerName="Devart.Data.Oracle" />
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, 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?
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?