Cascade delete relationship slower?

I have several related tables that have the cascade delete option selected i
n
their relationships. There is one parent table that all the child tables
relate to (sometimes many layers deep). In order to allow deleting of a
parent record I have created a stored procedure that goes through the
hierarchy and deletes the related records in the child tables in the correct
order as to not break referential integrity.
My problem is that this stored procedure is incredibly slow. This seems
strange because for each row you delete in the parent, only a handful of
child rows have to be deleted.
1) Is it possible that the cascade delete option could be slowing things dow
n?
2) Since I am taking care of the cascading of deletes on my own in the
stored proc, should I remove that option from the relationships?
3) What other factors could be contributing to very slow delete performance?Do you have indexes on the FK's in each of the child tables?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6162CFA5-5032-4F82-86E2-092B3B76F1EE@.microsoft.com...
I have several related tables that have the cascade delete option selected
in
their relationships. There is one parent table that all the child tables
relate to (sometimes many layers deep). In order to allow deleting of a
parent record I have created a stored procedure that goes through the
hierarchy and deletes the related records in the child tables in the correct
order as to not break referential integrity.
My problem is that this stored procedure is incredibly slow. This seems
strange because for each row you delete in the parent, only a handful of
child rows have to be deleted.
1) Is it possible that the cascade delete option could be slowing things
down?
2) Since I am taking care of the cascading of deletes on my own in the
stored proc, should I remove that option from the relationships?
3) What other factors could be contributing to very slow delete performance?|||Could you post the DDL for the procedure?
Wild guess: the performance problem is a result of locking. How quick is a
delete from the parent table using just the CASCADEs?
--
"Dan" wrote:

> I have several related tables that have the cascade delete option selected
in
> their relationships. There is one parent table that all the child tables
> relate to (sometimes many layers deep). In order to allow deleting of a
> parent record I have created a stored procedure that goes through the
> hierarchy and deletes the related records in the child tables in the corre
ct
> order as to not break referential integrity.
> My problem is that this stored procedure is incredibly slow. This seems
> strange because for each row you delete in the parent, only a handful of
> child rows have to be deleted.
> 1) Is it possible that the cascade delete option could be slowing things d
own?
> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
> 3) What other factors could be contributing to very slow delete performanc
e?
>
>|||
> 1) Is it possible that the cascade delete option could be slowing things
> down?
Cascade delete option? If you are using that option then this deletion
should just magically occur, whap bam boom. No need for a stored procedure.
I don't believe it will make any difference, as long as there are no rows to
delete. At this point the check for rows to cause NO ACTION will be the
exact same as the check for rows to CASCADE the action to. To optimize it,
think of the select statement you would run to see if there were rows:
select *
from childTable
where key in (parentTableKeyValue1, parentTableKeyValue1, ...,
parentTableKeyValueN)
As Tom mentions, you will probably need to index the columns in the foreign
keys, for this reason (I am just feeling wordy tonight!) Of course, if you
are deleting large number of rows at a time, then indexes might not help.

> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
Well, I don't know if it is hurting performance, but it certainly isn't a
good use of your time to do it both ways. If cascade deletes won't work for
you, then yes, you should remove it.

> 3) What other factors could be contributing to very slow delete
> performance?
Poor disk subsystem, not enough ram, too many concurrent users, gremlins,
etc. Seriously this is a big question. You need to optimize the queries
and look for table scans or cluster index scans (ordered table scans) to
determine if you are getting good plans. Then hit perfmon and see if the
system is performing.
I think that indexing the foreign keys is probably the correct answer, but
it is a very open question.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6162CFA5-5032-4F82-86E2-092B3B76F1EE@.microsoft.com...
>I have several related tables that have the cascade delete option selected
>in
> their relationships. There is one parent table that all the child tables
> relate to (sometimes many layers deep). In order to allow deleting of a
> parent record I have created a stored procedure that goes through the
> hierarchy and deletes the related records in the child tables in the
> correct
> order as to not break referential integrity.
> My problem is that this stored procedure is incredibly slow. This seems
> strange because for each row you delete in the parent, only a handful of
> child rows have to be deleted.
> 1) Is it possible that the cascade delete option could be slowing things
> down?
> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
> 3) What other factors could be contributing to very slow delete
> performance?
>
>

capitalized queries shower?

Hi all,
One of my co-workers claims that a capitalized query like "SELECT foo FROM
bar" would run slower than "select foo from bar." This sounds absurd to me,
and since I can't find any reference that claims his statement is true, I've
decided to ask the community.
Your help is appreciated.
-Oleg.Can you give me the name of that co-worker? I have a bridge to sell ;-)
--
Jacco Schalkwijk
SQL Server MVP
"Oleg Ogurok" <oleg@.ogurok.com.ihatespammers.ireallydo.co> wrote in message
news:10eauif311sp32f@.corp.supernews.com...
> Hi all,
> One of my co-workers claims that a capitalized query like "SELECT foo FROM
> bar" would run slower than "select foo from bar." This sounds absurd to
me,
> and since I can't find any reference that claims his statement is true,
I've
> decided to ask the community.
>
> Your help is appreciated.
> -Oleg.
>|||> One of my co-workers claims that a capitalized query like "SELECT foo FROM
> bar" would run slower than "select foo from bar." This sounds absurd to
me,
I agree 100%. I prefer queries where the keywords are capitalized; much
easier to parse and dissect visually.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||No, that's totally untrue.
But I highly recommend that you take advantage of the situation by making a
very large bet with your co-worker and force this person to prove it
unequivocally in order to win the bet.
"Oleg Ogurok" <oleg@.ogurok.com.ihatespammers.ireallydo.co> wrote in message
news:10eauif311sp32f@.corp.supernews.com...
> Hi all,
> One of my co-workers claims that a capitalized query like "SELECT foo FROM
> bar" would run slower than "select foo from bar." This sounds absurd to
me,
> and since I can't find any reference that claims his statement is true,
I've
> decided to ask the community.
>
> Your help is appreciated.
> -Oleg.
>|||> But I highly recommend that you take advantage of the situation by making
a
> very large bet with your co-worker and force this person to prove it
> unequivocally in order to win the bet.
And hopefully, you've already shut off his NNTP access so he doesn't stumble
upon the common sense brought up here. ;-)|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e3bbHeEYEHA.2816@.TK2MSFTNGP11.phx.gbl...
> I agree 100%. I prefer queries where the keywords are capitalized; much
> easier to parse and dissect visually.
I will concur on that one... All caps is easier for ME to read, but
certainly not a computer :)|||If you open SQL Profiler, from the tools in enterprise manager, do the 2
types of select in query analyzer, you will see that the duration for the
lowercase select will be lower than the uppercase select.
I would assume that the compiler has to convert the uppercase to lowercase
to compile the code first.
It is only a minimal speed difference, but there is a difference.
Jay Freeman
"Oleg Ogurok" <oleg@.ogurok.com.ihatespammers.ireallydo.co> wrote in message
news:10eauif311sp32f@.corp.supernews.com...
> Hi all,
> One of my co-workers claims that a capitalized query like "SELECT foo FROM
> bar" would run slower than "select foo from bar." This sounds absurd to
me,
> and since I can't find any reference that claims his statement is true,
I've
> decided to ask the community.
>
> Your help is appreciated.
> -Oleg.
>|||"Jay Freeman" <j.freeman@.halcyonsoftware.com> wrote in message
news:%23jEpJvEYEHA.1264@.TK2MSFTNGP11.phx.gbl...
> If you open SQL Profiler, from the tools in enterprise manager, do the 2
> types of select in query analyzer, you will see that the duration for the
> lowercase select will be lower than the uppercase select.
Looks the same to me, using this:
set statistics time off
dbcc dropcleanbuffers
dbcc freeproccache
go
set statistics time on
SELECT * FROM PUBS..AUTHORS
go
set statistics time off
dbcc dropcleanbuffers
dbcc freeproccache
go
set statistics time on
select * from pubs..authors
go
Parse and compile time was 0ms both times.

capitalized queries shower?

Hi all,
One of my co-workers claims that a capitalized query like "SELECT foo FROM
bar" would run slower than "select foo from bar." This sounds absurd to me,
and since I can't find any reference that claims his statement is true, I've
decided to ask the community.
Your help is appreciated.
-Oleg.
Can you give me the name of that co-worker? I have a bridge to sell ;-)
Jacco Schalkwijk
SQL Server MVP
"Oleg Ogurok" <oleg@.ogurok.com.ihatespammers.ireallydo.co> wrote in message
news:10eauif311sp32f@.corp.supernews.com...
> Hi all,
> One of my co-workers claims that a capitalized query like "SELECT foo FROM
> bar" would run slower than "select foo from bar." This sounds absurd to
me,
> and since I can't find any reference that claims his statement is true,
I've
> decided to ask the community.
>
> Your help is appreciated.
> -Oleg.
>
|||> One of my co-workers claims that a capitalized query like "SELECT foo FROM
> bar" would run slower than "select foo from bar." This sounds absurd to
me,
I agree 100%. I prefer queries where the keywords are capitalized; much
easier to parse and dissect visually.
http://www.aspfaq.com/
(Reverse address to reply.)
|||No, that's totally untrue.
But I highly recommend that you take advantage of the situation by making a
very large bet with your co-worker and force this person to prove it
unequivocally in order to win the bet.
"Oleg Ogurok" <oleg@.ogurok.com.ihatespammers.ireallydo.co> wrote in message
news:10eauif311sp32f@.corp.supernews.com...
> Hi all,
> One of my co-workers claims that a capitalized query like "SELECT foo FROM
> bar" would run slower than "select foo from bar." This sounds absurd to
me,
> and since I can't find any reference that claims his statement is true,
I've
> decided to ask the community.
>
> Your help is appreciated.
> -Oleg.
>
|||> But I highly recommend that you take advantage of the situation by making
a
> very large bet with your co-worker and force this person to prove it
> unequivocally in order to win the bet.
And hopefully, you've already shut off his NNTP access so he doesn't stumble
upon the common sense brought up here. ;-)
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e3bbHeEYEHA.2816@.TK2MSFTNGP11.phx.gbl...
> I agree 100%. I prefer queries where the keywords are capitalized; much
> easier to parse and dissect visually.
I will concur on that one... All caps is easier for ME to read, but
certainly not a computer
|||If you open SQL Profiler, from the tools in enterprise manager, do the 2
types of select in query analyzer, you will see that the duration for the
lowercase select will be lower than the uppercase select.
I would assume that the compiler has to convert the uppercase to lowercase
to compile the code first.
It is only a minimal speed difference, but there is a difference.
Jay Freeman
"Oleg Ogurok" <oleg@.ogurok.com.ihatespammers.ireallydo.co> wrote in message
news:10eauif311sp32f@.corp.supernews.com...
> Hi all,
> One of my co-workers claims that a capitalized query like "SELECT foo FROM
> bar" would run slower than "select foo from bar." This sounds absurd to
me,
> and since I can't find any reference that claims his statement is true,
I've
> decided to ask the community.
>
> Your help is appreciated.
> -Oleg.
>
|||"Jay Freeman" <j.freeman@.halcyonsoftware.com> wrote in message
news:%23jEpJvEYEHA.1264@.TK2MSFTNGP11.phx.gbl...
> If you open SQL Profiler, from the tools in enterprise manager, do the 2
> types of select in query analyzer, you will see that the duration for the
> lowercase select will be lower than the uppercase select.
Looks the same to me, using this:
set statistics time off
dbcc dropcleanbuffers
dbcc freeproccache
go
set statistics time on
SELECT * FROM PUBS..AUTHORS
go
set statistics time off
dbcc dropcleanbuffers
dbcc freeproccache
go
set statistics time on
select * from pubs..authors
go
Parse and compile time was 0ms both times.