Updating rows Oracle table via Output data tool very slow -Alteryx not using Index unique

Question

Hi everyone,

Unfortunately we have encountered a really hard to solve issue regarding the performance of updating records in a Oracle database table (for security reasons we can’t use any real table/attribute names).

Context:

We have a workflow that updates a table in our Oracle database on creditor information on a daily basis. The table in Oracle is called “CREDITORS”. The workflow consists of multiple parts, for simplicity: First, the workflow pulls data via API call (download tool) from the source system, lets call this SOURCE. Second, we call the records that are currently valid (have a datestamp of the year “8888”) from the table “CREDITORS”. Third, we compare the pulled records from SOURCE with the active records from CREDITORS. From here we know which records technically need to be appended or updated.

Appending new rows to the CREDITORS table has been no problem and the performance is fine. Currently there are 5399 records inserted in about 1,2 sec. Updating records has been a real pain for us as it does work but it doesn’t seem to be using the right index. It takes about 3 minutes for 6399 records to be updated. Let’s get more into detail:

This is the configuration of our output data tool:

Output data tool for updating records to Oracle

In Oracle Enterprise Manager we can see the following plan when Alteryx tries to update the CREDITORS table:

If we compare the way Alteryx tries to update the table with PowerCenter, we see that PowerCenter does use INDEX UNIQUE SCAN instead of INDEX SKIP SCAN. That is probably the core of our problem that we like to solve (probably, since there’s always another solution). As one can see there is a index on the tabel in Oracle and the primary key of the table consists out of 4 columns.

From what we can read from various topics on the community is that it might have something to do with the datatypes going from Alteryx to Oracle. As you can see in the screenshot from Oracle Enterprise Manager there are some implicit conversions going on in the background. We basically tried to change datatypes of the 5 fields going into the data output tool (for the update).

FieldOracle datatypeKey fieldExtra informationDatatype in Alteryx CREDVARCHAR2(16)P2 Tried V_String, WString, String and V_WString MVARCHAR2(10)P1 Tried V_String, WString, String and V_WString SVARCHAR2(40)P4 Tried V_String, WString, String and V_WString VDATEP3format =  dd-mm-yyyy hh24:mi:ssDatetime as there is a time component TDATE format =  dd-mm-yyyy hh24:mi:ss

Datetime as there is a time component

The last select tool before the data output tool doing the update to Oracle

We also tried to play with pre-sql to use the hint option of Oracle but that (obviously) doesn't do the trick.

select /*+ INDEX(CREDIT CREDITPK) */*

To conclude, we would really appreciate help in getting our update to Oracle to perform faster!

Sebastiaandb · Answer

Hi @danilang,

Thanks for your reply!

The use of a staging/temp table to update the target table seems like a good workaround for the problem.

However, our architects don't want us to use any workarounds. We think there might be a problem with the characterlanguage going out from Alteryx to Oracle, we believe Alteryx selects a certain outgoing characterset dynamically but that doesn't end up right for Oracle, that's why you would see those implicit conversions going on (and why it doesn't use the unique index). To bad you can't just select the outgoing character language (like you can in Powercenter). For what it's worth we found out that updating does do significantly faster from the Alteryx gallery (possible due to not having to go through multiple network security checkpoint/routers) as in 1 million updated rows in 12 minutes. Still this is not fast (and not fast enough for our stakeholders) if you compare it to other solutions. Unfortunately we don't have access to Oracle Enterprise Manager ourselves to check the query coming in from the Gallery, we're wondering if that one does use the index or not (probably not). We'll keep you/this topic updated as it might be useful to other users.

danilang · Answer

Hi @Sebastiaandb

The use of Skip scan as opposed to Unique is probably baked in to the back end code in the Output tool and probably can't be changed.  One way that we've worked around it in the past is Delete then Append into a staging table that has an On Insert trigger to modify the main table.

Dan