Forum OpenACS Q&A: How to batch update security_inherit_p efficiently?

Collapse
1: How to batch update security_inherit_p efficiently?
Posted by Tilmann Singer on 01/25/03 09:45 PM
I need to set security_inherit_p to 'f' on 80000 acs_objects and observed that when doing this with a simple update statement within a pl/sql loop it takes about a second for each row which would result in the loop to run for at least 22 hours.

Any suggestions for speeding this up, e.g. by disabling some triggers and manually recreating the tables that they would have updated or something like that? Or could someone give me at least a short overview which triggers/tables are being involved?

This is on oracle and for a one-time migration, so the system is not being accessed by other users during the time the update runs.

Collapse
2: Re: How to batch update security_inherit_p efficiently? (response to 1)
Posted by Don Baccus on 01/25/03 11:12 PM
Get rid of the loop! Can you create a select statement that will find all the objects for you?

Something like 

update foo
set bar = 0
where pkey in (select pkey
               from foo
               where ...)
should be much faster than doing 80,000 separate update statements.
Collapse
3: Re: How to batch update security_inherit_p efficiently? (response to 1)
Posted by Don Baccus on 01/26/03 02:28 AM
It will undoubtably still run slow because changing security_inherit_p to 'f' will modify a bunch of rows for each object in the table that maps an object to its context parent.

Check the triggers in, I believe, acs-objects-create.sql.

You may be able to drop the trigger then write a PL/SQL proc to create the map afterward.

But writing the proc may take longer than the day you'll save ...

maybe if you're lucky the massive update approach will only take 21.5 hours!

Collapse
4: Re: How to batch update security_inherit_p efficiently? (response to 1)
Posted by Patrick Giagnocavo on 01/26/03 04:16 AM
Would it not be possible to set autocommit off in order to speed things up?

For the pl/sql loop, would something like "alter session disable commit in procedure" help?

Why not try it against 5 or 10 objects and see if the loop speeds up?

I don't know if you are running this in sqlplus or using some kind of db api calls; but you might want to try the other method and see if the speed is higher (eg if using sqlplus try via AOLserver/tcl, and vice versa).