Alteryx output tool taking 1 hour to update ~80k records

Question

Hi I would appreciate some help with the following:

Alteryx is taking 1 hour to update ~80k records when updating a table with a primary key, using an oracle server connection.

The output tool settings are default, except the following: File Format = "oracle .oci"; Output Options = "Update: Error on Update Failure"; Append Field Map = "By Field Name".

There is no pre or post SQL. I am not getting any errors but the update takes an hour when the rest of the workflow takes seconds to run.

The Primary Key in my destination oracle table has a char 64 data type, which I can't map directly in Alteryx (its currently set as a "V_WString: Forced") - I don't know if this might be causing an issue?

apathetichell · Answer

Just seeing this message --- my hunch is some of this is just expected behavior. I'd toggle off primary key constraint and write this to a new db. Does it write lighting fast? If so--- your issue is the primary key constraint. That's a killer on large dbs. Index rebuilding sucks. If your write without the primary key to a new db is still slow --- troubleshoot the driver and how you are writing. If you are using OCI --- can't you output to a blob storage --- and trigger some function in OCI to do this for you?

dreldrel · Answer

Normally, it shouldn't take that long for 80K data. But you can turn on Disable All Browse Tools in your settings, this can save some memories for you as well

jrlindem · Answer

Hey there, I’ve experienced similar, but It’s been a while.

Things to consider:

* Computer memory (RAM).  Depending on the demand of your workflow and how many records you’re processing this can be a big determiner.  Alteryx can be resource hungry.
* As far as hard drive space, make sure you have enough to handle the cache’ing that your workflow may require as it’s packaging portions of data to transfer.
* Close Alteryx designer down completely before running this workflow to ensure all the temporary data that Alteryx stores during sessions is cleared out.  You may even need to go to your /temp location and manually clear this out every now and again.

Also, I’ve found some articles that might help with other areas:

* Oracle Connections:  Oracle
* Changing Packet Size:  Solved: Change Packet Size in OLEDB - Alteryx Community
* Memory Use:  Memory Use
* Output Tool Transaction Size Settings:  What is the Alteryx Designer Transaction Size option for Output Tool?

Hard to tell exactly what might be causing your issues, but these are areas worth exploring.  If all that fails to resolve, open a service ticket so that one of the support team can review your log files and maybe they can see what’s going on or where it’s hanging up.

Hope that helps,  -Jay