Consultor Eletrônico

Status: Verified

FACT(s) (Environment):

Oracle DataServer

SYMPTOM(s):

Deleting record with a FOR EACH takes a long time to complete

FOR EACH table:
DELETE table.
END.

send-sql-statements take a very short time to delete the same number of records

CAUSE:

When deleting records through a FOR EACH statement, each record must be fetched and deleted one at a time. When working with large number of records, if each record must be fetch first before it can be deleted, this can cause overhead on the delete process.

FIX:

Use a RUN STORED-PROCEDURE statement with the send-sql-statement to delete large number of records at one time.

DEFINE VAR h1 AS INTEGER.
RUN STORED-PROC send-sql-statement h1 = PROC-HANDLE
("delete from <table_name>").
CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = h1.


By using a send-sql-statement, you are passing SQL statements directly to ORACLE.