ManagedDataAcces Execute reader performance

[preslyel]

Hello. I have .Net framework API with odp.net.x64 package to access Oracle DB. I'm trying to move my application to .Net core and get Oracle.ManagedDataAccess.Core package for this, but my request to DB became 10 times slower. I have a simple select to DB and dbCommand.ExecuteReader() takes more then 2 sec for executing. In the previous version, it was 300 ms. I can't find any information why it so slow. Any ideas?

[alexkeh]

A couple tools you can use to speed up your SQL is SQL Tuning Advisor and Oracle Performance Analyzer. The former analyzes ad-hoc SQL in isolation. The latter takes a holistic approach and analyzes your overall application, though it is likely to focus on the SQL if it consistently takes a long time relatively speaking to complete.

They both providing tuning advice for your application once they complete their analysis from your run. They are both available from Oracle Developer Tools for Visual Studio. Using these tools should help you speed up your overall performance in both ODP.NET cases.

To explain why one provider executes differently from another provider will require a much deeper analysis. It could be situational. It could be Oracle DB. It could be the provider.

For example, a situational reason for the slow down could occur with the first time you run any SQL. It will be slower than a subsequent run. The DB caches a number of things the first time around, such as the parse, metadata, even the results on the client side. It depends how you have your DB and client set up. The second time SQL execution should be faster, and it could be many times faster since the DB no longer has to populate these caches nor even retrieve these results if they already on the client side. If unmanaged ODP.NET was run after ODP.NET Core, then it would benefit from these existing caches on the server side.

You could collect ODP.NET traces for executing the SQL under similar conditions. That can tell you whether ODP.NET Core is spending the bulk of this time waiting for the DB to return with results or whether there is a slow internal operations within ODP.NET Core itself when compared with unmanaged ODP.NET.

[preslyel]

I think there is no reason to speed up my SQL or collect some trace to be sure that trouble in the provider. I've made a test in the same environment - server, DB, query, network and even PC. And results do not depend on the number of runs. Just the code
dbCommand.ExecuteReader()
takes 2 seconds instead of 300ms when using odp.net.x64.
The query is

SELECT * FROM (
       SELECT *, row_number() over(order by columnName) rnum FROM tableName
       WHERE columnID >= 0
)
WHERE rnum BETWEEN 0 AND 100

and it returns 20 records.

[alexkeh]

I compared unmanaged ODP.NET x64 12.2 vs. ODP.NET Core 18.5 executing the same query (i.e. OracleDataReader reader = cmd.ExecuteReader();). The query I execute tries to emulate what you are doing. I use the HR.EMPLOYEES table.

SELECT * FROM (SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, DEPARTMENT_ID, MANAGER_ID, row_number() over(order by FIRST_NAME) rnum FROM EMPLOYEES WHERE EMPLOYEE_ID >= 0) WHERE rnum BETWEEN 0 AND 100

Over 1000 executions, the results I see are:
Unmanaged ODP.NET: 0.422 seconds
ODP.NET Core: 0.574 seconds

ODP.NET Core is slower by about 36%, but not 10 times slower.