Re: Deleting vs foreign keys

I guess your master table has a considerable tree of child records for
each deleted record, that's why the deletion takes so long. We have this
situation too on top level object deletion. Our solution is to make it
asynchronous, i.e. the user requests it and then a background job does
it. There's no way to make such deletes blazing fast.
Other solutions would be to not delete at all, but mark the top level
record as deleted (using a deleted flag on it). That has the
disadvantage that it's children can still be accessed, unless all access
code checks the master active flag, even for child access. Other
disadvantage is that all the data still is in the data base, taking up
space, possibly slowing down operation. Now that could also be an
advantage if you discover you shouldn't have been deleted that record
just after pushing the "delete" button and receiving the confirmation
that everything was deleted...

Cheers,
Csaba.

On Tue, 2005-10-25 at 17:01, WireSpot wrote:
> On 10/25/05, WireSpot <wirespot(at)gmail(dot)com> wrote:
> > Thanks for the tip, guys. I'll go punch in some indexes and I'll be
> > back to report how much of a difference it made.
>
> Adding indexes made the dropping of entries for the "master" table
> roughly 6 (six) times faster. It's definitely an improvement and I
> suspect it will benefit the application in many other ways.
>
> It's still not perfect though. Based on the figures I've got, some of
> the biggest entries in the "master" table would still take about 45
> minutes to delete along with all their foreign key referrals. So as
> far as practical purposes are concerned, I'm back to square one.
>
> Fortunately, in this case, all the tables hold a redundant site ID.
> The only practical alternative I see is to drop all the foreign keys,
> delete from all the tables based on that redundant ID, then add the
> keys back and hope I don't hit any conflicts.
>
> This is still not perfect, because while I do this there cannot be any
> regular access to the database (can't afford to while foreign keys are
> down). But if the drop becomes blazing fast in this manner, it would
> be worth it... I guess.
>
> Any advice about what went wrong when this database was designed? Was
> there any way that the designers could've kept the consistency offered
> by foreign keys and at the same time allow for fast deletes?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings