Macro performance issue

Database
N/A

Macro performance issue

I have a Macro with 1 Delete followed by 2 Inserts followed by 3 Updates - Total 5 Statements.

These 5 statements if run separately outside of the macro complete within a total time of 15 minutes.

But as a macro, it takes 2 Hrs. This is on a 1 CCU Cloud unit.

Any insights on why it would be so bad in performance within a macro ?

Thanks.

Tags (1)
1 REPLY
Junior Contributor

Re: Macro performance issue

Let me guess:

The DELETE is a DELETE ALL and the INSERTs are INSERT/SELECTs.

When you run them individually you get

#1: a FastPath DELETE (no Transient Journal)

#2: a FastPath INS/SEL (no TJ)

#3-5: INS/SEL & INS (with TJ)

But DELETE is only FastPath when it's the last statement on the target table within that transaction and INS/SEL is only FastPath when the target table is empty at the beginning of the transaction. Now a macro is the same as a MultiStatement Request and all six statements are part of one transaction.

The solution is simple, don't use a macro :)

You might get best performance when you run the DELETE standalone & combine both INS/SEL into 1 MultiStatement (if possible, otherwise do the bigger first).