Very Computer

what is faster/better?

using cascade deletes or coding the deletes of dependent tables in the
stored procedures?

currently, weve coded all the deletes in our stored procedures, but we have
foreign keys defined for the tables.

thanks
stephane

I would definitely build the RI constraints as close to the schema as
possible using either cascade deletes on the foreign keys or code them
manually in triggers.  I can assure you this will speed things up
dramatically, but not necessarily in the way you meant.

The problem with not enforcing RI deletes/updates in the schema arises when
a maintenance DBA changes the key or issues a delete on a parent table
without going through the sproc.  If there isn't any schema bound RI
enforcement, whatever time you saved by not firing the sprocs will be eaten
up searching for orphaned records.

Hardware is cheep, people are expensive, the fastest database is the most
constrained one!

Rob

Quote:> what is faster/better?

> using cascade deletes or coding the deletes of dependent tables in the
> stored procedures?

> currently, weve coded all the deletes in our stored procedures, but we
have
> foreign keys defined for the tables.

> thanks
> stephane

Neither is better.  They both have their place.

Use CASCADE RI if:

You ALWAYS want to delete a child when you delete its parent.  My rule of
thumb is if the child rows are "part" of the parent, then use cascade.  Like
if you have an employee and a child names table.  You will never want to
warn the user "Hey, this employee has children, are you sure you want to
delete them?"  Hokey example, but it is Friday afternoon.

Use stored procedure if:

You usually want to delete them.  So the proc knows to delete the children,
but if you issue a delete command, you may not want to accidentally cascade.

Use two calls if:

You want complete control.  So you might have two procedures childDelete

make the client delete the children first, and then the parent.  You might
even have them delete them one at a time.

Bottom line Cascading is nice, but it is doggone destructive if you change
your mind, or you miss some underlying details.

--
----------------------------------------------------------------------------
-----------

Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)

Quote:> what is faster/better?

> using cascade deletes or coding the deletes of dependent tables in the
> stored procedures?

> currently, weve coded all the deletes in our stored procedures, but we
have
> foreign keys defined for the tables.

> thanks
> stephane

Hi Sviau,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with your issue.

From your description, I understand that you would like to know which is faster/better between
Cascade Delete and T-SQL delete. The performance of both of these deleted mechanisms is
quite good and I believe there is little difference on the performance between them. I
recommend that you apply the one that best suits your specific needs and environment.

Cascade RI Constraints and Stored Procedure (T-SQL deletion) each have benefits that
make them useful in special situations. The primary benefit of Stored Procedures is that they
can contain complex processing logic that uses Transact-SQL code.

Both of them have their own specialties/advantages and limitations:

1. Cascading RI allows you to define the actions taken when you attempt to delete a key to
which an existing foreign keys. It allows the deletion of key values to cascade through the
tables defined to have foreign key relationships that can be traced back to the table on which
the modification was performed.

However, please note that CASCADE RI cannot be specified for any foreign keys or primary
keys that have a timestamp column. Moreover, Cascading actions cannot be specified for a
table that has an INSTEAD OF UPDATE or INSTEAD OF DELETE trigger. After a cascading
action has been defined for a table, an INSTEAD OF UPDATE or INSTEAD OF DELETE
trigger cannot be added to it. It also cannot perform "what if" analysis.

2. Stored Procedure is compiled into a single execution plan, which can dramatically improve
the performance if the execution plan is re-used. It assists in achieving a consistent
implementation of logic across applications. You can use triggers (a special type of stored
procedure that automatically takes effect when the data in a specified table is modified) to
automatically perform the deletion. They work no matter what caused the data modification

Triggers are most useful when the features supported by Cascade RI constraints cannot meet
the functional needs of the application. For example:
(a)FOREIGN KEY constraints can validate a column value only with an exact match to a value
in another column, unless the REFERENCES clause defines a cascading referential action.

(b)A CHECK constraint can validate a column value only against a logical expression or
another column in the same table. If your application requires that a column value be
validated against a column in another table, you must use a trigger.

(c)Constraints can communicate about errors only through standardized system error
messages. If your application requires (or can benefit from) customized messages and more
complex error handling, you must use a trigger.

However, please note that these changes can be executed more efficiently through
cascading referential integrity constraints.

Triggers can disallow or roll back changes that violate referential integrity, thereby canceling
the attempted data modification. Such a trigger takes effect when you change a foreign key
and the new value does not match its primary key.

If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger
execution but prior to the AFTER trigger execution. If the constraints are violated, the
INSTEAD OF trigger actions are rolled back and the AFTER trigger is not executed.

3. Above all, Cascade RI Constraints and Stored Procedure (T-SQL deletion) each have
benefits that make them useful in special situations. The primary benefit of Stored Procedures
is that they can contain complex processing logic that uses Transact-SQL code. Therefore,
Stored Procedures (triggers) can support all of the functionality of constraints; however,
triggers are NOT always the best method for a given feature.

Please let me know if my suggestion helps you resolve the problem. If there is anything more I
can assist you with, please feel free to let me know.

Regards,
Billy Yao
Microsoft Online Partner Support

----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.