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 ?
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).