UPDATE with commits

[eknight]

Hello,

There is one issue that I haven't been able to solve and I'd appreciate any tips you may have.

The current implementation has an SQL node perform a simple UPDATE on a table. Recently this UPDATE needed to touch ~ 38 million rows. This was unusually large and the step failed for performance reasons. We managed to find a workaround but the problem is still there and could theoretically happen again.

What are the COGNOISe communities recommendations for solving this? We have considered the following:

1) Adding a looping condition node and add a max_rows to the UPDATE node.
2) Using a procedure node to use an SQL LOOP.

The looping condition node add complexity to the jobs and is not easy to copy & paste between jobs. The procedure node doesn't seem to work properly yet.

Thank you,

[MFGF]

Is there a reason you are using a SQL node to update rather than a Fact Build? I would guess your issues stem from the database not being able to process so large a single request?

An alternative might be to collect the rows in a fact build and write them back to the same table using UPDATE refresh type. This way, each row will be read and updated separately. You can also control whether everything is done in one big transaction or whether it is split into a number of shorter transactions.

Another thought if you must use a SQL node is to try to figure out a different unrelated column with a few distinct values that you could use to drive separate UPDATE statements with

eg:
UPDATE yourtable SET yourcolumn='something' WHERE gendercolumn='M';
UPDATE yourtable SET yourcolumn='something' WHERE gendercolumn='F';

In my example, you are using the two distinct gender values to split the update into two separate statements. Might this get you around your database's issue with one big statement?

Just a thought...

MF.

[eknight]

The idea of splitting up the update like that is really neat. I don't think I would ever have thought of that but that could very much do the trick. I would add a COMMIT in between and that would take care of the transaction log filling up.

I suppose the suggestion of making a separate fact build is actually the proper solution. It just seems like a lot of overhead for a relatively simple update that needs to occur.

On a side note, is there any support for PL/SQL in DecisionStream? Can I use PL/SQL in calls to SQL()?

[MFGF]

On a side note, is there any support for PL/SQL in DecisionStream? Can I use PL/SQL in calls to SQL()?

I have never tried it, sorry. Why not try a simple example to see if it works?

MF.

[eknight]

I actually have  :D That's why I asked.

It seems that DS doesn't complain about PL/SQL in the procedure nodes but also doesn't parse it properly. It happily returns "TRUE" or "Successfully completed" even if I have errors in the PL/SQL.

I inteded to post the PL/SQL question as a separate thread (for easier searching) but thought I would ask here quickly in case answer was a simple "NO".