Does anyone have experience with cascade delete being slow on large
databases?
It appears that the cascade delete is not making use of the existing
clustered indexes.
If I create statements deleting the same records from the 20 referenced
child tables (with children of their own) using the foreign key column of
the parent table the delete occurs in a few seconds vs over a minute for the
constraint to delete the record and all chldren. Even if the parent has no
children it takes ovr a minute for it to scan the children for potential
orphans. Since I know no way to observe the steps that the constraint is
performing I can only assume that for some reason it is not using the
existing indexes on the table.Gene
Did you have on referensing table an index?
Have you tried to run show plan of the query to see what is going on?
Personally I don't have any problem with perfomance in my VLRD when I
perfom deletion.
"Gene Black" <geblack@.cox.net> wrote in message
news:#LbuG5SCEHA.1548@.TK2MSFTNGP12.phx.gbl...
> Does anyone have experience with cascade delete being slow on large
> databases?
> It appears that the cascade delete is not making use of the existing
> clustered indexes.
> If I create statements deleting the same records from the 20 referenced
> child tables (with children of their own) using the foreign key column of
> the parent table the delete occurs in a few seconds vs over a minute for
the
> constraint to delete the record and all chldren. Even if the parent has no
> children it takes ovr a minute for it to scan the children for potential
> orphans. Since I know no way to observe the steps that the constraint is
> performing I can only assume that for some reason it is not using the
> existing indexes on the table.
>|||When I use show query plan it appears that many of the clustered indexes are
not being used. I see sorts and hash joins happening vs when I construct the
deletions using joins the clustered indexes are used.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eTKja2aCEHA.3152@.TK2MSFTNGP10.phx.gbl...
> Gene
> Did you have on referensing table an index?
> Have you tried to run show plan of the query to see what is going on?
> Personally I don't have any problem with perfomance in my VLRD when I
> perfom deletion.
>
> "Gene Black" <geblack@.cox.net> wrote in message
> news:#LbuG5SCEHA.1548@.TK2MSFTNGP12.phx.gbl...
of
> the
no
>|||After testing it appears that if I overlay a non-clustered index on top of
the existing clustered index the delete operation using the RI constraint
with cascade delete performs the same as the manually constructed delete.
The query plan using showplan looks almost identical but the performance
difference is extensive. This does not seem like a necessary solution as the
columns are already identified and used in the clustered index on the table.
I am still perplexed by the fact that it does not perform as expected until
a nonclustered index is laid over top of the clustered index.
I will have to examine it some more, maybe one of the 60 referenced tables
is missing a clustered index, since those have been applied over time
manually while the overlays were created by automated script following the
relationship tree.
"Gene Black" <geblack@.cox.net> wrote in message
news:%23LbuG5SCEHA.1548@.TK2MSFTNGP12.phx.gbl...
> Does anyone have experience with cascade delete being slow on large
> databases?
> It appears that the cascade delete is not making use of the existing
> clustered indexes.
> If I create statements deleting the same records from the 20 referenced
> child tables (with children of their own) using the foreign key column of
> the parent table the delete occurs in a few seconds vs over a minute for
the
> constraint to delete the record and all chldren. Even if the parent has no
> children it takes ovr a minute for it to scan the children for potential
> orphans. Since I know no way to observe the steps that the constraint is
> performing I can only assume that for some reason it is not using the
> existing indexes on the table.
>
Showing posts with label experience. Show all posts
Showing posts with label experience. Show all posts
Wednesday, March 21, 2012
Friday, March 9, 2012
Reference Book Needed
I'm a consultant with 20+ years of experience. With Crystal Report Writer,
I just sat down and starting playing with it to figure it out, but now I am
confronted with using SQL Reporting Services 2005.
It is definitely giving me a shot of humility because I am finding it the
most frustrating experience ever. I purchased a pretty good book written by
Brian Larson, but it is presented in a procedural format, and is not being
very helpful when I have something specific to accomplish. I'm also trying
to use the online help and newsgroups, but the process is painstakingly
slow.
My challenge of the moment is in constructing a date range expression, but I
am also stuck on the parameters of the format function. I loved Crystal in
that I could use the wizard to give me a jump start with the formatting, and
then could edit it into more advanced queries.
Can anyone offer any suggestions on where I could find a good SQL Reporting
Services reference? I'm open to anything at this point -- website, book,
notes scribbled on a napkin...I know what you mean about books with references instead of walking through
the procedures. I bought Hitchhiker's Guide to SQL Server 2000 Reporting
Services by Peter Blackburn, William R. Vaughn , because I've read some good
reviews about it, and I liked the title and SQL Server 2000 Reporting
Services Step by Step Book/cd Package by S Misner because it came cheap with
the other one. I think I bought them too late, as they focus on how to do
the things I already knew how to do, and the references are poor. But when I
go through them, I can find things that I didn't know. Out of the two, only
one has been updated, "SQL Server 2005 Reporting Services Step by Step" by
S. Misner.
http://www.amazon.co.uk/Server-2005-Reporting-Services-Step/dp/0735622507/sr=1-1/qid=1160306842/ref=sr_1_1/026-7617136-7864435?ie=UTF8&s=books
You might want to check any reviews for it first, as it seems to be a
beginner's book.
My main source of information about Reporting Services has been articles
written by William E. Pearson, III
http://www.databasejournal.com/article.php/1459531
I started reading his articles when I started working with RS in 2004, and
got most of the basics from him. The first tutorials are based on SQL Server
2000 and RS 2000, but the later ones are based on RS 2005. Most of what he
writes about the 2000 edition still holds for 2005, though, so they're not
entirely useless.
I also find Brian Welcker's blog "Direct Reports" usefull
http://blogs.msdn.com/bwelcker/default.aspx
And from his blog you find links to other good RS blogs like Tudor's weblog
(http://blogs.msdn.com/bwelcker/default.aspx) and Chris Hay's Sleazy Hacks
(very usefull, http://blogs.msdn.com/ChrisHays/)
But whenever I need help to find out how to do something, I use the RS news
group. I know there's a forum for it as well, but it wasn't very active when
I started working with RS, and it's hard to teach old dogs new tricks. A lot
of answers can be found in old ng posts, and if you can't find it, ask and
you might get an answer. It's slow, I know, but when you figure out the
basics and the differences from Crystal Reports, it gets better.
Kaisa M. Lindahl Lervik
"Cindy Mikeworth" <CindyMikeworth@.newsgroups.nospam> wrote in message
news:uXbqlNk6GHA.4496@.TK2MSFTNGP05.phx.gbl...
> I'm a consultant with 20+ years of experience. With Crystal Report
> Writer, I just sat down and starting playing with it to figure it out, but
> now I am confronted with using SQL Reporting Services 2005.
> It is definitely giving me a shot of humility because I am finding it the
> most frustrating experience ever. I purchased a pretty good book written
> by Brian Larson, but it is presented in a procedural format, and is not
> being very helpful when I have something specific to accomplish. I'm also
> trying to use the online help and newsgroups, but the process is
> painstakingly slow.
> My challenge of the moment is in constructing a date range expression, but
> I am also stuck on the parameters of the format function. I loved Crystal
> in that I could use the wizard to give me a jump start with the
> formatting, and then could edit it into more advanced queries.
> Can anyone offer any suggestions on where I could find a good SQL
> Reporting Services reference? I'm open to anything at this point --
> website, book, notes scribbled on a napkin...
>
I just sat down and starting playing with it to figure it out, but now I am
confronted with using SQL Reporting Services 2005.
It is definitely giving me a shot of humility because I am finding it the
most frustrating experience ever. I purchased a pretty good book written by
Brian Larson, but it is presented in a procedural format, and is not being
very helpful when I have something specific to accomplish. I'm also trying
to use the online help and newsgroups, but the process is painstakingly
slow.
My challenge of the moment is in constructing a date range expression, but I
am also stuck on the parameters of the format function. I loved Crystal in
that I could use the wizard to give me a jump start with the formatting, and
then could edit it into more advanced queries.
Can anyone offer any suggestions on where I could find a good SQL Reporting
Services reference? I'm open to anything at this point -- website, book,
notes scribbled on a napkin...I know what you mean about books with references instead of walking through
the procedures. I bought Hitchhiker's Guide to SQL Server 2000 Reporting
Services by Peter Blackburn, William R. Vaughn , because I've read some good
reviews about it, and I liked the title and SQL Server 2000 Reporting
Services Step by Step Book/cd Package by S Misner because it came cheap with
the other one. I think I bought them too late, as they focus on how to do
the things I already knew how to do, and the references are poor. But when I
go through them, I can find things that I didn't know. Out of the two, only
one has been updated, "SQL Server 2005 Reporting Services Step by Step" by
S. Misner.
http://www.amazon.co.uk/Server-2005-Reporting-Services-Step/dp/0735622507/sr=1-1/qid=1160306842/ref=sr_1_1/026-7617136-7864435?ie=UTF8&s=books
You might want to check any reviews for it first, as it seems to be a
beginner's book.
My main source of information about Reporting Services has been articles
written by William E. Pearson, III
http://www.databasejournal.com/article.php/1459531
I started reading his articles when I started working with RS in 2004, and
got most of the basics from him. The first tutorials are based on SQL Server
2000 and RS 2000, but the later ones are based on RS 2005. Most of what he
writes about the 2000 edition still holds for 2005, though, so they're not
entirely useless.
I also find Brian Welcker's blog "Direct Reports" usefull
http://blogs.msdn.com/bwelcker/default.aspx
And from his blog you find links to other good RS blogs like Tudor's weblog
(http://blogs.msdn.com/bwelcker/default.aspx) and Chris Hay's Sleazy Hacks
(very usefull, http://blogs.msdn.com/ChrisHays/)
But whenever I need help to find out how to do something, I use the RS news
group. I know there's a forum for it as well, but it wasn't very active when
I started working with RS, and it's hard to teach old dogs new tricks. A lot
of answers can be found in old ng posts, and if you can't find it, ask and
you might get an answer. It's slow, I know, but when you figure out the
basics and the differences from Crystal Reports, it gets better.
Kaisa M. Lindahl Lervik
"Cindy Mikeworth" <CindyMikeworth@.newsgroups.nospam> wrote in message
news:uXbqlNk6GHA.4496@.TK2MSFTNGP05.phx.gbl...
> I'm a consultant with 20+ years of experience. With Crystal Report
> Writer, I just sat down and starting playing with it to figure it out, but
> now I am confronted with using SQL Reporting Services 2005.
> It is definitely giving me a shot of humility because I am finding it the
> most frustrating experience ever. I purchased a pretty good book written
> by Brian Larson, but it is presented in a procedural format, and is not
> being very helpful when I have something specific to accomplish. I'm also
> trying to use the online help and newsgroups, but the process is
> painstakingly slow.
> My challenge of the moment is in constructing a date range expression, but
> I am also stuck on the parameters of the format function. I loved Crystal
> in that I could use the wizard to give me a jump start with the
> formatting, and then could edit it into more advanced queries.
> Can anyone offer any suggestions on where I could find a good SQL
> Reporting Services reference? I'm open to anything at this point --
> website, book, notes scribbled on a napkin...
>
Subscribe to:
Posts (Atom)