how to reduce Time consumption while writing data to oracle database

[Padma_]

Hi Team,

I have 2 scenarios.

I have workflow that pulls data from alteryx server from it's default database, data related how many workflows are running, how many queued etc. The output has to be  written  into an oracle database using output tool(used oracle bulk upload in file format and append existing). This flow generates 95k records.

I first directly ran the workflow as above but after 2 hours only 26% of the workflow ran, so I cancelled the workflow. Because of this 

Secondly I wrote the output to yxdb file, this happend in just 20 mins. Post that i took this yxdb file in input tool and connected to output tool with same oracle bulk upload option still it took 4 hours. 

everyday i need to write 1lakh records to oracle database and schedule this workflow. 

I don't know what is the mistake I am doing, how to reduce the time,  also I don't want to manually take output in yxdb and then run again by writing to oracledb.

can anyone suggest please.

Thanks

Thanks.

[CharlieS · Accepted solution]

The only suggestion I have on the workflow side is adding a Block Until Done tool before the output/write to DB. I think in some scenarios the records are incrementally written as they are prepared and latency in the connection will make the complete process take longer than the two separate processes (preparing the data vs writing the data). 

Now going from yxdb to DB write over 4 hours even with a bulk loader seems slow. The bulk loader is the way to go, so the rest of this might be a conversation best had with your DB admin.

[Padma_]

Thanks a lot for the suggestion and quick Reply from your End.

[Padma_]

Finally, this issue was resolved when i changed the datatype from CLOB to v_wstring(250). my oracle table had varchar2 and when alteryx has clob datatype before. Now after changing datatype Alteryx is able to write the oracle data in 20 mins.