Oracle db read REALLY slow

[jonesds]

Hi All,

I am trying to conform data from tables across several databases and am having real problems with one of the databases being really slow.  All of the other data sources work fine and the database in question is fine when I test it with a different tool (for example SQL Developer or some Java I wrote).  However, for some unknown reason Alteryx reads it really slowly, around 4 seconds per record.  I need ~100,00 records which at the current rate would take around five days to read!

The database is Oracle and I'm not doing any fancy joins or manipulation in SQL which could (possibly) slow it down.  I have tried running this In-DB but it makes no difference.  I have also tried changing the Default Dedicated Sort/Join Memory Usage but to no avail.

As I have said, it works fine outside of Alteryx so can anyone think of a reason why it could be so slow?

Many thanks

Daryl

[SophiaF]

@jonesds - most of the time performance issues on databases are driver-related, especially if you are seeing no speed improvement by using the in-db tools. Which driver/driver version are you using? In general, OCI is preferred for speed and performance. 

[jonesds · Accepted solution]

So I found the solution to this.  Alteryx was writing to the database with CLOB data types which, when read back in to Alteryx took an inordinate amount of time.

With a bit of jiggery-pokery I have changed the culprit datatypes to VARCHAR2 which works a treat.

I don't know why CLOB would cause such a problem, I can only presume that Alteryx was trying to allocate a huge amount of memory for each CLOB field.

Having solved this a quick search on CLOBs in Alteryx resulted in the following post:

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Clob-Data-poor-performance/idi-p/18727

Anyway, many thanks to all who helped with this and the support offered, much appreciated.