Performance tuning of delete from many tables

Yikes. Loops within loops within loops! I’m not surprised this is slow.

Generally speaking, running SQL within a loop => slow.

If you want this to run quickly you’re going to need to get rid of these. Ideally you want to execute a delete against each table just once.

Some tips to help with this:

Use subqueries in the delete

Instead of doing something like:

for rws in (select * from container_candidates) loop delete from container_item where id = rws.containeritemid end loop;

Put the query from your cursor loop in the delete itself:

delete from container_item where id in ( select containeritemid from container_candidates );

This finds all the rows in one execution, no need for looping!

Use the having clause to find compounds with exactly one container

Instead of looping through the container candidates to count to ensure there’s only one, use the having clause to only return those where there is exactly one. Then put this in a subquery to do the delete, e.g.:

delete compounds where sampleid in ( SELECT SAMPLEID FROM CONTAINER_CANDIDATES JOIN COMPOUNDS ON COMPOUNDS.ContainerItemId = CONTAINER_CANDIDATES.ContainerItemId WHERE ContainerId = inContainerId group by SAMPLEID having count(distinct ContainerId) = 1 )

I’m not sure exactly what your logic is, so you’ll probably need to change this. This is just to give you the general idea.

Ideally you’ll be able to replace all the code in ClearContainer with just four delete statements. This should help it run quicker. If it’s still too slow once you’ve done this, please trace the session with:

exec DBMS_monitor.session_trace_enable ( null, null, true, true );

And format the trace file with tkprof. Then post the results here. For more on how to do this, read:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan#tkprof

If you’re not sure how to refactor your delete statements, please post details of what you’ve tried and why this is wrong and we’ll do our best to help.

Related:

Leave a Reply