Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Data insertion into oracle tables

Sudharshan_Sannathi
6 - Meteoroid

I have processed some data and wanted to insert it into the oracle table.

If the same workflow i run to write data into csv it is taking 30-40secs but to write into the oracle table it is taking lot of time.

 

     No of records         Time taking to write into oracle table

       300                          3:30 mins

       1000                        7:50 mins

       10k                          50mins

 

Properties used for work flow:

1)Append existing

2)"Output Data" to write data into the table.

I have also tried with bulk load but it is also taking approximately same time.

 

How can i minimise this time and Is it possible to write 100k records into oracle table within 5 mins?

 

6 REPLIES 6
fmvizcaino
17 - Castor
17 - Castor

Hi @Sudharshan_Sannathi ,

 

One thing you can try is "If you're using an Oracle ODBC, then try adjusting your Fetch Buffer Size in your ODBC manager. This basically changes the transaction size so making fewer, larger transactions may be beneficial". This information was taken from @CharlieS response to the following post.

 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Reduce-the-time-while-Writing-tables-t...

 

See if that works for you.

 

Best,

Fernando Vizcaino

Sudharshan_Sannathi
6 - Meteoroid

Hi Fernando,

 

Thanks for your response, i have just found the solution.

 

"Datatypes" was the major issue.

I have changed the datatypes in workflow that suits to the oracle DB  - problem is solved now. Now it is taking 17secs, 2mins to load 100k and 1million records respectively.

Piyanuch
5 - Atom

Could you please explain more about data type suitable for Oracle? I also have the same problem as yours. I'm looking forward to hearing from you 🙂

sidd1582
5 - Atom

I am getting the same issue of 1 hour to write 100k rows. Tried oracle bulk loader, does not work.

Hiblet
10 - Fireball

Can you provide information on the data types?  We are having a problem writing to an Oracle table, and this may be the issue.  What types are "good"?

pitmansm
8 - Asteroid

^ need working datatypes (eye emojii)

Labels