Early bird tickets for Inspire 2023 are now available! Discounted pricing closes on January 31st. Save your spot!

Alteryx Designer Discussions

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

Oracle Database read speed too slow

Carlos77
7 - Meteor

I have connected my Alteryx Designer to a corporate Oracle database, I need to fetch datasets sometimes with more than 10 million records. The typical number is between 1 and 5 million. Nr. of columns is usually between 20 and 40.

 

I am testing now the connection, and it takes me around 50-60 minutes to read a dataset of 1.3 million records. This means that a larger set would not be available during a full working day if I started in the morning.

 

When I am using a competing similar software (KNIME), which uses the same driver (stored in a different folder of course), the same SQL command, the same sample took only 5 minutes.

 

Reading previous posts in the community, it seems this issue has never been really solved, more just different workarounds are being proposed. I've tried everything I could find here, but no real success.

 

Unfortunately this topic is a show stopper for me, unless I can get Alteryx to read around 10 million records per hour (or max 2h), I simply cannot use it in my work.

 

Any help would be appreciated, thanks in advance.

 

11 REPLIES 11
afv2688
16 - Nebula
16 - Nebula

Hello @Carlos77,

 

Which connector are you using to access to the oracle database?

 

Regards

Carlos77
7 - Meteor

Hi @afv2688,

 

I'm using the standard Input Data -node with file format Oracle Database (oci:).

 

Pls. see attached the setup -window.

 

Best regards,

 

danilang
18 - Pollux
18 - Pollux

Hi @Carlos77 

 

Have you tried using the other input drivers, OleDB or ODBC?

 

Dan

Carlos77
7 - Meteor

Hello @danilang,

 

Yes, I tried both OleDB and ODBC, there was no notable difference.

 

Best regards,

 

danilang
18 - Pollux
18 - Pollux

@Carlos77 

 

Do your tables have CLOBs?  I've heard that these can be a problem.  Can you try casting them as nvarchars in the query?

 

Another workaround, though definitely not ideal, would be to use the Run Command to call SQLPlus and then parse the output.  

 

Dan

Carlos77
7 - Meteor

Hello @danilang,

 

No, the longest field is only 400 characters (and there's only one that long), most fields are actually 2-8 characters. And even the data in the longer fields is almost never even close to that long.

 

And like said, another software was able to achieve the reading in 5 minutes, so I doubt the data content is a major problem, I could be wrong of course.

 

Best regards,

 

miteshnarottam
Alteryx
Alteryx

Hi Team

 

Please try installing the Simba ODBC driver available from downloads.alteryx.com. The following article references any installation issues which might occur
https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Simba-Oracle-Driver-gives-Error-126...

Mitesh Narottam

Carlos77
7 - Meteor

Hi Mitesh,

 

Many thanks for your help @miteshnarottam  - this solved the issue! Now actually Alteryx outperforms the competing tool. 🙂

 

Best regards,

Carlos77

Jimmy_Payyapilly
5 - Atom

Carlos,

 

I am having a similar scenario like yours where connecting to Oracle database via Alteryx Designer is returning around 5 to 10 million rows in an hour.

I did use the Simba ODBC Oracle connector but no difference in the performance.

Did u install the Oracle 19c client on your machine before using the Simba ODBC Oracle connector because I have an existing Oracle 12c client installed and the Simba connector connected successfully without any errors.

Any suggestions are welcome.

 

Regards,

 

Jimmy

Labels