About EasyMorph Tutorials & Examples Web-help

Performance issues when running multiple SQL-files


#1

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


#2

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”?


#3

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 !


#4

@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.


#5

@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.


#6

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


#7

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.