Delete statement consuming more resource

Database
Enthusiast

Delete statement consuming more resource

Hi All,

We are using TD14, here we are having a macro having delete and insert logic.

In the macro Delete is consuming more resource due to this we are having some performance issues.

Please suggest to overcome this issue.

Note: Delete statement as a single statement is using very less resource but inside a macro is using huge resources.

Thanks,

Karthick.

8 REPLIES
Junior Contributor

Re: Delete statement consuming more resource

Hi Karthick,

assuming the delete is a DELETE ALL followed by an INS/SEL:

DELETE ALL is fast if it's the last statement within a transaction and the optimizer knows it will be commited (no transient journal).

INS/SEL is fast if the target table is empty at the begin of the transaction (no transient journal).

Now a macro is exactly the same as a multi-statement request, both DELETE and INS/SEL are part of a single transaction. DELETE is not the last statement, INS/SEL is not in an empty table, so both are journaled (= slow).

Enthusiast

Re: Delete statement consuming more resource

Hi Dieter,

Thanks for your Explanation.

Could you please provide us any suggestion to overcome this issue.

Thanks,

karthick.

Junior Contributor

Re: Delete statement consuming more resource

Hi Karthick,

the solution is self-evident, use seperate statements/transactions instead of a macro. 

Enthusiast

Re: Delete statement consuming more resource

Hi Dieter,

Yes we tried running the statements separately and its working fine even resource usage was very less.

But our client requirement is to use only macro , even we tried BTEQ but that is also not accepted.

Thanks,

Karthick

Junior Contributor

Re: Delete statement consuming more resource

Hi Karthick,

what about two macros or a Stored Procedure?

Enthusiast

Re: Delete statement consuming more resource

Hi Dieter,

how to use two macros/stored procedure here ?

In this macro we have one delete and one insert statement. the main purpose of having these two statements in the macro is if the insert statement failed then the deleted data will be reverted back.

Could you please let me know the same can be achieved through two macros/stored procedure.

Thanks,

Karthick.

Junior Contributor

Re: Delete statement consuming more resource

Hi Karthick,

if you need to be able to rollback the delete there's no other way than both delete and ins/sel in one transaction.

But then you have to pay the price, in worst case the ins/sel fails shortly before finishing :-)

Another solution might be view-switching:

-- two existing copies of the target table, target1 & target2
-- load 1
insert into target2 select * from source;

replace view target_view as
select * from target2;

delete from target1;

-- next load:
insert into target1 select * from source;

replace view target_view as
select * from target1;

delete from target2;
Enthusiast

Re: Delete statement consuming more resource

Thank you Dieter.. will check the possibility