Re: Discussing 3 different strategies for deleting from multiple tables
- From: "Mark J. McGinty" <mmcginty@xxxxxxxxxxxxxxx>
- Date: Sat, 15 Oct 2005 17:57:08 -0700
You're right that method #1 is costly, that approach is suitable for
periodic maintenance... is it really that important that orphaned records
get cleaned-up in real-time on a per top-level delete basis?
Method #2 is a little more frugal, but still pricey, and I can certainly see
your point about query complexity -- btw, what db engine restricts use of
parameters in sub-queries?
I doubt method #3 will work reliably, if at all, but if it did, it would
involve many, many more server round trips under the covers. (ADO is famous
for generating excessive server traffic in certain situations.) It would
also almost certainly be many times slower than using set-based ops.
The significant question is, what db engine will you be using? If SQL
Server, you might want to consider using triggers to cascade these deletes,
that way they would always be maintained even if rows were deleted outside
of your app. Another advantage would be that the set of IDs required by
each lower-level delete would already be resolved when the triggers are
called, reducing complexity of each op to only that required to maintain the
next level down. You'd have to make sure that no locking conflicts would
result, and of course, nested triggers would have to be enabled.
Last note, certainly the cascading delete paradigm is not atypical, but 12
relationships tends a little towards the frisky side.
-Mark
"Jiho Han" <jhan@xxxxxxxxxxxxxxxx> wrote in message
news:a19ab9b12c47fb8c79ed85940777f@xxxxxxxxxxxxxxxxxxxxxxx
>I have a database where tables are structured so that deleting a record in
>one of the table must also result in deletes from 8 other tables.
> I am guessing that this is not atypical in applications out there. This
> is due to how the tables are hierarchically related to one another. In
> some situations, it may involve more than 12 tables even.
>
> Having said that I would like to discuss and get some feedback on what the
> best strategy may be to accomplish this goal. Note that the solution
> obviously must incorporate ADO.
> Also note that none of the databases have "hard" forein key relationships
> defined and assume that I cannot create them either.
> I will only use 3 tables for brevity - Account, Contact, Opportunity - and
> assume the following relationship. FYI, Account contains around 20K
> records, Contact 80K, and Opportunity 700K.
>
> Account.Accountid = Contact.Accountid
> Contact.Contactid = Opportunity.Contactid
>
> So a deleting an account must result in a delete of all contacts under
> that account and a delete of all opportunities under those contacts as
> well.
>
> --------------------
> Method 1 -
>
> DELETE FROM ACCOUNT WHERE ACCOUNTID = ?
> DELETE FROM CONTACT WHERE ACCOUNTID NOT IN (SELECT ACCOUNTID FROM ACCOUNT)
> DELETE FROM OPPORTUNITY WHERE CONTACTID NOT IN (SELECT CONTACTID FROM
> CONTACT)
>
> I can see this method as the slowest but at the same time simple and
> foolproof.
> This results in one parameterized query followed by two more trips to the
> database to delete any orphaned records. This method also has the added
> side-effect of possibly cleaning up any "dirty" data from any other
> sessions.
>
> And perhaps the above queries can be rewritten using EXISTS to make it
> more efficient. But the idea is same. Perhaps something like this:
>
> DELETE FROM OPPORTUNITY WHERE NOT EXISTS (SELECT CONTACTID FROM CONTACT
> WHERE CONTACTID = OPPORTUNITY.CONTACTID)
> --------------------
> Method 2 -
>
> DELETE FROM OPPORTUNITY WHERE OPPORTUNITYID IN (
> SELECT OPPORTUNITYID FROM OPPORTUNITY A1 INNER JOIN CONTACT A2 ON
> (A1.CONTACTID = A2.CONTACTID) WHERE A2.ACCOUNTID = ?
> )
> DELETE FROM CONTACT WHERE ACCOUNTID = ?
> DELETE FROM ACCOUNT WHERE ACCOUNTID = ?
>
> This method works by deleting from the "leaf" and works up to the "root".
> This may be more efficient but as the number of tables involved gets
> larger and larger, the queries will get more complex. Also, the first
> delete statement in the example will not work since you cannot have a
> parameter in the subquery. Although it's not essential, I would rather
> have a solution that does not use string concatenation to generate a
> query.
> --------------------
> Method 3 -
>
> SELECT A1.ACCOUNTID
> FROM ACCOUNT A1 LEFT JOIN CONTACT A2 ON (A1.ACCOUNTID = A2.ACCOUNTID)
> LEFT JOIN OPPORTUNITY A3 ON (A2.CONTACTID = A3.CONTACTID)
> WHERE A1.ACCOUNTID = ?
>
> I would retrieve the result into a recordset and simply delete all rows
> returned by calling .Delete method on each row. And when I Update the
> recordset, ADO will generate all necessary delete sql statements. I am
> basically benefitting from not specifying Unique Table property and thus
> letting ADO delete all involved rows from all tables in the query. The
> added benefit of this method is that there is a single trip to the
> database (well two, if you count the select). However, as more tables are
> involved, the query gets complex and the number of joins required goes up.
> --------------------
>
> I am leaning towards method 1 for the moment (at least while in initial
> development). Method 2 seems like it doesn't provide much more benefit to
> Method 3 as the level of depth grows larger, the resulting subquery will
> approximate the query in Method 3 anyway.
>
> I would love for some feedback on this and experiences you've had on
> similar situations.
> Thanks so much.
>
> Jiho Han
> Senior Software Engineer
> Infinity Info Systems
> The Sales Technology Experts
> Tel: 212.563.4400 x216
> Fax: 212.760.0540
> jhan@xxxxxxxxxxxxxxxx
> www.infinityinfo.com
>
>
.
- Follow-Ups:
- References:
- Prev by Date: Creating Access MDB's with ADOX.
- Next by Date: Memory Leak with Recordset?
- Previous by thread: Discussing 3 different strategies for deleting from multiple tables
- Next by thread: Re: Discussing 3 different strategies for deleting from multiple tables
- Index(es):
Relevant Pages
|
|
