Re: Help about Sql Delete performance

[Jim Oberholtzer <midrangel@xxxxxxxxxxxxxxxxx>]

I think this is one of those cases where “SQL is the answer, what’s the problem” is flat wrong.   


Jim Oberholtzer
Agile Technology Architects

> On Jan 26, 2025, at 12:48 PM, Mark Waterbury <mark.s.waterbury@xxxxxxxxxxxxx> wrote:
>
> Hi, Marco,
>
> Since you said you are deleting such a large percentage of rows or records, perhaps it would be faster to do something like this:
>
> #1. ensure you have a good back-up of the base table or PF and indexes or LFs, before doing the steps below.  Perhaps create a new save file and save the PF and LFs there, especially if your back-up tapes are "off-site."
>
> #2. create a new table (PF) with the same structure as the original, in a different library, using SQL or CRTDUPOBJ with DATA(*NO).  Note: this new table / PF has no indexes or LFs, except perhaps the intrinsic access path for the "primary key".
>
> #3. copy from the original table only all the records or rows you want to keep -- that is, omitting those you want to delete to this new PF table.
>
> #4. temporarily remove all the indexes of the original table, or use RMVM to remove the member (access paths) of any LFs over that PF, and any triggers or referential integrity rules, and possibly stop journaling the table PF and index LFs, if journaled (likely if commitment control is used).
>
> #5. issue CLRPFM to clear the original table / PF member.  This should be fairly fast, without any extra indexes or LFs to maintain (for now)
>
> #6. copy all the records or rows you want to keep, saved in that other library, back to the original table PF.  This should be relatively fast, as we have not yet-reactivated all of the indexes or LFs (see next step).
>
> #7. re-add all of the indexes over the base table (PF) -- likely "best simple" way to do it is to have used RMVM on all of the LFs for the indexes, and now use ADDLFM to re-add those members.  (This method preserves definitions of the indexes / LFs.)   Re-add any triggers and constraints, and restart journaling if needed. 
>
> At this point, the system will re-build all of the access paths, depending on the settings on each index or LF for "Maintain access path . . . MAINT:  *IMMED, *REBLD or *DLY".
>
> SUMMARY
> I think this should be much faster than trying to delete al of those rows using normal SQL or even native RPG I/O DELETEs because of all of the access path maintenance incurred on each row or record.
>
> I hope that helps,
>
> Mark S. Waterbury
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.