I have few delete, insert and update statements. So if I execute this statements in SQL Asst, its taking 15 mins. But if I create macro on top of this statements and execute. It’s taking 30mins.
Kindly suggest me how to improve performance on this scenarios ?
A Macro is exactly the same as a MultiStatement Request, i.e. a single transaction. You probably Delete all rows from a table first and then Insert/Select in that table. This is one of the rare cases where Macros are worse than seperate requests.
When you run it as three individual requests you get a so-called FastPath-Delete avoiding Transient Journal (because the Delete is immediately commited) and a FastPath InsertSelect which also avoids Transient Journal (because the Insert is into an empty table).