Write into DB using Bulk performance issue

[MHP_Guy]

I have workflow with two containers both read data from HANA DB do some joins and write the data into an Oracle DB using Bulk.

However, one is fast the other is unbelievable slow.

Fast Flow: 

Slow: 

Any idea what could be the issue/reason? The Output is in both cases identical configured.

Any debugging possibilities? 

[Raj]

@MHP_Guy The performance difference seems to stem from the workflow structure:

1. In the faster workflow, you're using two joins followed by another join to move forward before writing the data to the database.
2. In the slower workflow, you're first joining two datasets, then using a union to include the remaining data from the right anchor. This approach is repeated a couple more times for subsequent inputs.

The second approach ensures no data is missed, which could be more suitable depending on your use case. However, do check for duplicate primary keys, as they can increase the number of rows and the overall data size, potentially impacting performance.

Tips for improvement:

• If all your data sources are from a database, consider using the Join In-DB tool. This will significantly enhance performance since operations are handled directly within the database.

Hope this helps! Feel free to mark this as resolved if it addresses your issue.

[apathetichell]

before your smaller join sides (your left) ---> reconfigure this to a summarize tool. drope what you don't need for fields. group by others. drop the unique. unique is a memory eater.

having that ---> assuming dataquantity is similair and the DB is simialir ---I'd ask about the field structure and quantity of data there.

[MHP_Guy]

Actually, the problem isn't the Workflow it is only the last step which cause the issue. Everything else is processing okay and fast.

The fast one need ~2 min to load the data into the DB. The slow on is running for hrs. The DB is the same. Not verry complex data all is varchar or number. 

[apathetichell]

First --- turn off amp. Why --- because AMP can effect data integrity. 2) for number 2---- turn off your output data writing to your db. Write a .yxdb instead. take .yxdb. open new workflow. write directly from .yxdb to your database. I am doing this to test if your issue is the write ---> or something upstream. If this has the same time issue, turn on enahnced logging in your ODBC 64. Pick up your ODBC logs -> you have an issue somewhere with your driver. maybe it's a type that Oracle doesnt like.Maybe it's a data limit. Maybe it's how you reach that database. I don't know. But it should be in your ODBC logs --> not your Alteryx logs.

[MHP_Guy · Accepted solution]

I found the issue. I added by mistake a Column of type v_wString which causes the issue. After changing it to v_String everything is running fine.