Performance issues when running multiple SQL-files

Hi Dmitry,

I have an EasyMorph project in which I do an iteration over multiple SQL-files that are executed on an Oracle database. Great that this is possible in EasyMorph !

However, I have noticed that the iteration jams when the queries are relatively heavy. Are you aware of that problem and possible causes ?

Does the connector make use of the high performance OCI of oracle when executing the statements ?

Could you give me some advise to improve the performance ?

Kind regards

Hi,

EasyMorph’s internal Oracle connector uses Official Oracle ODP.NET, Managed Driver which doesn’t use OCI.

But you can try to use Oracle ODBC driver through ODBC connector in EasyMorph. That driver uses OCI. At least the one installed with Oracle Database 12c Release 1 client does.

Cay you please describe in more detail what do you mean by “iteration jams”?

Hi Andrew,

I have a bunch of SQL statements each in a separate file. I read them from file.
I know that some queries take some time to run when I run them from my sql developer client.
When I iterate over the files and execute them in easyMorph, I have the impression that on some of the heavy queries, It keeps on running without result. Or is it possible that I takes such a long time to run ? Is there an expected difference in runtime in between the sql developer client and running the query from easyMorph ?

Kind regards !

@reynsnivea As far as I can tell, SQL Developer loads and displays only 100-200 first rows of the query result. And then it progressively loads following rows when a user scrolls down displayed dataset.

EasyMorph behaves differently - it loads all the rows from the DB result and only then creates its internal dataset.

So in order to compare query execution time in EasyMorph and SQL Developer, you’ll need to limit the number of rows, returned by the query, to just a few lines.

I don’t think that there is a reason to compare result loading time between EasyMorph and SQL Developer because those are very different tools designed for a different purpose.

But just out of curiosity I exported 10K lines from the same DB table to a CSV file in EasyMorph and SQL Developer. And it took ~5 sec in EasyMorph and ~20 sec in SQL Developer.

@reynsnivea Is it possible that those long-running queries just consumes all the available RAM on your computer? Since EasyMorph stores all the loaded data in memory, its performance degrades significantly when there is no free RAM available.

Hi Andrew,

That’s possible. When I use an R-script with the ROracle driver, I can run the queries. Is there a difference in how EasyMorph does the job because R does not seem to import it into RAM ?

Could you explain me what to do to install the high performance driver and connect via ODBC in EasyMorph ? Will this also import all data in RAM ?

Why not making the high performance connection the default oracle connector ?

Thanks,
Nikolaas

Hi Nikolaas,

I’m not familiar with ROracle driver so I can’t how it’s different from EasyMorph.

Can you please clarify what do you mean by high performance driver?

Regardless of a driver or a data source, all the data, imported by EasyMorph, will be loaded to RAM. It’s by design.

Hi Andrew,

Could you provide some info about how to install and configure the Oracle ODBC driver so that I can use it in EasyMorph for retreiving data faster using OCI ?

Thanks !

Hi Nikolaas,

Installation of Oracle ODBC drivers should be explained in Oracle documentation. We don’t provide installation instructions for 3rd party software.

Once you have Oracle ODBC driver installed, create ODBC connector in EasyMorph and pick Oracle from the list of available SQL dialects, if necessary.

image

Hi Andrew,

I have installed the ODBC driver and connected to it in EasyMorph.
However, the performance is far below the performance of the normal oracle connection method.
According to Oracle the ODBC driver should be high performance.
Any idea what I am doing wrong here ?

Should I use other database commands to execute a query instead of the normal “import from database” action ?

Thanks.
Nikolaas

Hi Nikolaas,

Which version of the ODBC driver you are using?

Hi,

I think it is called “instantclient_19_5”.

What I have done is the installation as described here: https://www.oracle.com/database/technologies/releasenote-odbc-ic.html

Then I had to set the TNS_ADMIN parameter in the path and I created a tnsnames.ora file with the connection string.
After that, I added the data source in the windows data source administrator. From that point, it was available in EasyMorph through ODBC.

So it runs but actually at a far lower performance than the managed Oracle driver which is native in EasyMorph…

Thet question is why ?

Hi Nikolaas,

Can you please open the Windows’ ODBC Data Source Administrator and check which Oracle driver is used by the DSN which you are using to connect to Oracle:

image

And then open “Drivers” tab and send me the exact driver version:

image

I’m using version 12.01.00.02 and it works in EasyMorph ~2.5 times slower that a native connector.
But the following versions should have a better performance.

Regards,
Andrew

Hi Andrew,

See below.

It’s strange that de connector is slower while they promote it as high performance… :slight_smile:

image

image

Hi Nikolaas,

Thank you for the screenshots. I tested version 19 of the ODBC driver and native connector is still much faster.

I also tested ODBC driver with Excel. And import was just 15% faster than import with ODBC connector in EasyMorph.

So it seems that there is nothing wrong with ODBC connector in EasyMorph. And the driver, used by the native connector, is faster than ODBC driver.

It’s possible that some settings of the Oracle ODBC driver may improve import performance. But that’s out of the scope of our support.