In cascading parameters if we change the value of parent parameter then the
child parameter must reflect the changes â?¦ means it should set â'Select Allâ'
automatically as default.
--
asharmaUse query and dont hard code then a post back happens everytime.
Amarnath,MCTS
"asharma2004" wrote:
> In cascading parameters if we change the value of parent parameter then the
> child parameter must reflect the changes â?¦ means it should set â'Select Allâ'
> automatically as default.
> --
> asharma|||Hi! Actually it is a 3-level dependency. Here it is..
Param_head => Country = Select country from country [single valued param]
Param1=> Region =Select region from region where country in (@.country) [Multi]
Param2=> City= Select city from cities where region in (@.region)
Let's say I selected USA for Country. For this case, Region should display
all the states in USA and City should display all the Cities in USA in
default. However, when I select just one State (i.e California), default
values on City are correct and all selected, but when I try to add another
state, i.e Utah, City displays all the cities in Utah and California but only
California cities are selected.
I could not get around this. Is this a bug or something?
Thanks!
--jarm
"Wei Lu [MSFT]" wrote:
> Hello Jarm,
> Could you please let me know the query you use for those 2 parameters?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Jarm,
Yes, I reproduce this issue.
This is because after you change the first parameter, the report will not
refresh so that the default value for the second parameter will not refresh.
The only time that dependent parameters are updated is when an upstream
parameter change causes dependent parameter selections to be invalid. This
satisfies the vast majority of cases where users do not an upstream
parameter change to blow away their other settings (consider a report with
many parameters, all of which might need to be reset if the user decides to
make a change to one of the upstream values).
We'll consider doing this in a future release.
Thank you for your understanding.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label parent. Show all posts
Showing posts with label parent. Show all posts
Sunday, March 11, 2012
cascading deletes through a 1-M join table
Hi all,
I have three tables, two with unique integer PK's and a join table for a 1-M
join.
PARENT JOIN CHILD
+--+ +--+--+ +--+
+ 7 + --> + 7 + 45 + --> + 45 +
+--+ +--+--+ +--+
+ 7 + 197 + --> + 197 +
+--+--+ +--+
I want to be able to delete a PARENT record and have it clean up the JOIN
and all CHILD entries.
The join table is the Foreign key table. If I set up cascade delete from
Parent to JOIN that works, but i can't set up cascade delete from JOIN to
CHILD because the keys in the JOIN table are not primary keys.
I know that I can do this in a trigger on JOIN but this is a natural
straight parent->child delete, is there a way to cascade delete through the
JOIN to the CHILD?
thanks!
John"John Mott" <johnmott59@.hotmail.com> wrote in message
news:%23A0gjCoUGHA.5108@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have three tables, two with unique integer PK's and a join table for a
> 1-M join.
> PARENT JOIN CHILD
> +--+ +--+--+ +--+
> + 7 + --> + 7 + 45 + --> + 45 +
> +--+ +--+--+ +--+
> + 7 + 197 + --> + 197 +
> +--+--+ +--+
> I want to be able to delete a PARENT record and have it clean up the JOIN
> and all CHILD entries.
> The join table is the Foreign key table. If I set up cascade delete from
> Parent to JOIN that works, but i can't set up cascade delete from JOIN to
> CHILD because the keys in the JOIN table are not primary keys.
> I know that I can do this in a trigger on JOIN but this is a natural
> straight parent->child delete, is there a way to cascade delete through
> the JOIN to the CHILD?
>
Create a unique index on the JOIN table to support the relationship to
CHILD.
David
I have three tables, two with unique integer PK's and a join table for a 1-M
join.
PARENT JOIN CHILD
+--+ +--+--+ +--+
+ 7 + --> + 7 + 45 + --> + 45 +
+--+ +--+--+ +--+
+ 7 + 197 + --> + 197 +
+--+--+ +--+
I want to be able to delete a PARENT record and have it clean up the JOIN
and all CHILD entries.
The join table is the Foreign key table. If I set up cascade delete from
Parent to JOIN that works, but i can't set up cascade delete from JOIN to
CHILD because the keys in the JOIN table are not primary keys.
I know that I can do this in a trigger on JOIN but this is a natural
straight parent->child delete, is there a way to cascade delete through the
JOIN to the CHILD?
thanks!
John"John Mott" <johnmott59@.hotmail.com> wrote in message
news:%23A0gjCoUGHA.5108@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have three tables, two with unique integer PK's and a join table for a
> 1-M join.
> PARENT JOIN CHILD
> +--+ +--+--+ +--+
> + 7 + --> + 7 + 45 + --> + 45 +
> +--+ +--+--+ +--+
> + 7 + 197 + --> + 197 +
> +--+--+ +--+
> I want to be able to delete a PARENT record and have it clean up the JOIN
> and all CHILD entries.
> The join table is the Foreign key table. If I set up cascade delete from
> Parent to JOIN that works, but i can't set up cascade delete from JOIN to
> CHILD because the keys in the JOIN table are not primary keys.
> I know that I can do this in a trigger on JOIN but this is a natural
> straight parent->child delete, is there a way to cascade delete through
> the JOIN to the CHILD?
>
Create a unique index on the JOIN table to support the relationship to
CHILD.
David
Thursday, March 8, 2012
Cascade updates
I ran a bulk update on a SQL database for a range between dateswhere there
were trigger and possibly foreign keys. the parent updated fine but it
changed multiple child records although they were not relatedHi
Can you show us DDL + sample data + your query?
"Dangerfield" <Dangerfield@.discussions.microsoft.com> wrote in message
news:2E57BC94-A3B0-46CA-9349-C2DEB7C79F46@.microsoft.com...
> I ran a bulk update on a SQL database for a range between dateswhere there
> were trigger and possibly foreign keys. the parent updated fine but it
> changed multiple child records although they were not related
were trigger and possibly foreign keys. the parent updated fine but it
changed multiple child records although they were not relatedHi
Can you show us DDL + sample data + your query?
"Dangerfield" <Dangerfield@.discussions.microsoft.com> wrote in message
news:2E57BC94-A3B0-46CA-9349-C2DEB7C79F46@.microsoft.com...
> I ran a bulk update on a SQL database for a range between dateswhere there
> were trigger and possibly foreign keys. the parent updated fine but it
> changed multiple child records although they were not related
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?
>
>
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?
>
>
Cascade Delete Question
I am attempting to clarify how the cascade delete works. For example I have a Parent table and two child tables used for lookup that have the ID_FK = ID_PK from the Parent table.
tblUsers
-----
ID_PK
Fname
Lname
...
tblUser_Phone
------
ID_FK
Phone
...
tbUser_Email
------
ID_FK
Email
...
If I needed to delete all records from tblUser_Phone would this delete my USER entry from the parent table tblUsers if I have cascade delete enabled?
Thanks in advance.I believe cascade delete only deletes children records, so the parent table would be safe.|||I believe you are correct. Cascading only travels down the hierarchy, not up.
tblUsers
-----
ID_PK
Fname
Lname
...
tblUser_Phone
------
ID_FK
Phone
...
tbUser_Email
------
ID_FK
...
If I needed to delete all records from tblUser_Phone would this delete my USER entry from the parent table tblUsers if I have cascade delete enabled?
Thanks in advance.I believe cascade delete only deletes children records, so the parent table would be safe.|||I believe you are correct. Cascading only travels down the hierarchy, not up.
Subscribe to:
Posts (Atom)