Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Oracle db read REALLY slow

Highlighted
5 - Atom

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

Highlighted
Alteryx
Alteryx

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

Sophia Fraticelli
Premium Support Advisor
Alteryx, Inc.
Highlighted
5 - Atom

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.

Labels