Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Consolidating records - TSQL problem


Consolidating records - TSQL problem

Author
Message
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9028 Visits: 19041
Abu Dina (9/25/2012)
Lynne's solution works if we assume that the retained ID is always the maximum.

But tthe solution doesn't work with the following record set:


drop table dbo.testing
create table dbo.testing (retained int, dropped int)

insert into dbo.testing (retained, dropped)
select 767884, 157441 union all
select 1046261, 157441 union all
select 6699, 157441 union all
select 157441, 73635 union all
select 767884, 73635 union all
select 1046261, 73635 union all
select 6699, 73635 union all
select 1046261, 767884 union all
select 6699, 767884 union all
select 6699, 1046261



I will keep trying to see if I can come up with a solution but if anyone else can think of something then that'd be great!

Thanks in advance.


Yep...

;WITH retained AS (
SELECT t.*
FROM testing t
WHERE NOT EXISTS (SELECT 1 FROM testing i WHERE i.dropped = t.retained)
)
SELECT [Level] = 1, l1.retained, l1.dropped
FROM retained l1
UNION ALL
SELECT [Level] = 2, l1.retained, l2.retained
FROM retained l1
inner JOIN testing l2 ON l2.retained = l1.dropped
UNION ALL
SELECT [Level] = 3, l1.retained, l3.retained
FROM retained l1
inner JOIN testing l2 ON l2.retained = l1.dropped
inner JOIN testing l3 ON l3.retained = l2.dropped
UNION ALL
SELECT [Level] = 4, l1.retained, l3.dropped
FROM retained l1
inner JOIN testing l2 ON l2.retained = l1.dropped
inner JOIN testing l3 ON l3.retained = l2.dropped



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Abu Dina
Abu Dina
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 3323
Thanks Chris,

I'm looking at recursive CTEs but I don't think it will work. Well I've been trying for the last couple of hours and it's still not giving the same result as my original update (which I run multiple times until 0 rows are updated!).

Thanks for your efforts.

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


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9028 Visits: 19041
Abu Dina (9/25/2012)
Thanks Chris,

I'm looking at recursive CTEs but I don't think it will work. Well I've been trying for the last couple of hours and it's still not giving the same result as my original update (which I run multiple times until 0 rows are updated!).

Thanks for your efforts.


You're welcome.

I tried a rCTE too, and although it does resolve the hierarchy, I couldn't get it to come out in the required format - it would take quite a bit of post-processing which would have made it unfeasibly slow.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Abu Dina
Abu Dina
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 3323
Hi Chris,

Here is my attempt doing this with a rCTE

;WITH retained AS (
SELECT t.retained, dropped
FROM testing t
WHERE NOT EXISTS (SELECT 1 FROM testing i WHERE i.dropped = t.retained)
UNION ALL
SELECT b.retained, t1.dropped
FROM testing AS t1
INNER JOIN retained as b
ON t1.retained = b.dropped)

UPDATE t
SET t.retained = uv.retained
FROM testing t
INNER JOIN retained uv
ON t.dropped = uv.dropped



This works with the sample data but something isn't quite right with this. Tried it with a sample of 160000 records, it took 17 seconds.
When I increased the sample record set to 200000, the query just keeps going. It's scaling up badly or there's some dodgy data that the above logic just can't handle efficiently.

This is what the estimated execution plan is telling me:



Tried adding a couple of indexes on retained and dropped columns but that made no difference to the estimated execution plan.

Arrrggghhh! w00t

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


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8592 Visits: 18175
I know it could be a bad solution, and maybe you had tought about it.
But since the rCTE is not scaling well (and it sure won't) why don't you use your original solution in a while loop for @@rowcount > 0?
I'm not sure which will scale better, but it's still an option.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24295 Visits: 37999
Lynn Pettis (9/25/2012)
Abu Dina (9/25/2012)
Lynne's solution works if we assume that the retained ID is always the maximum.

But tthe solution doesn't work with the following record set:


drop table dbo.testing
create table dbo.testing (retained int, dropped int)

insert into dbo.testing (retained, dropped)
select 767884, 157441 union all
select 1046261, 157441 union all
select 6699, 157441 union all
select 157441, 73635 union all
select 767884, 73635 union all
select 1046261, 73635 union all
select 6699, 73635 union all
select 1046261, 767884 union all
select 6699, 767884 union all
select 6699, 1046261



I will keep trying to see if I can come up with a solution but if anyone else can think of something then that'd be great!

Thanks in advance.


I can only write code based on what you provided. Based on the sample data and expected results, what I saw was the max id being retained. With the new data, what are the rules for determining what ID is used? Also, you posted additional data but not additional expected results.


Okay, let me try this sgain from my phone while eating lunch at McDonalds.

Try this, I wrote it on a napkin while eating and looking at the data you posted.



with rCTE as (
select t1.retained, t1.dropped, t1.retained as TopLevel
from dbo.testing t1 left outer join dbo.testing t2 on t1.retained = t2.dropped
where t2.dropped is null
union all
select t1.retained, t1.dropped, r.TopLevel
from dbo.testing t1inner join rCTE r on t1.retained = r.dropped)
update tu set
retained = r.TopLevel
from dbo.testing tu inner join rCTE r on tu.retained = r.retained and tu.dropped = r.dropped;




Let me know if it works. I can't test it on my phone.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Abu Dina
Abu Dina
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 3323
Luis Cazares (9/25/2012)
I know it could be a bad solution, and maybe you had tought about it.
But since the rCTE is not scaling well (and it sure won't) why don't you use your original solution in a while loop for @@rowcount > 0?
I'm not sure which will scale better, but it's still an option.


Thanks Luis, your suggestion is valid. I see that Lynne has come back with a potential solution. I will test tomorrow and report back.

Regards.

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


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24295 Visits: 37999
Here is my code, reformatted the way I like to it.



with rCTE as (
select
t1.retained,
t1.dropped,
t1.retained as TopLevel
from
dbo.testing t1
left outer join dbo.testing t2
on t1.retained = t2.dropped
where
t2.dropped is null
union all
select
t1.retained,
t1.dropped,
r.TopLevel
from
dbo.testing t1
inner join rCTE r
on t1.retained = r.dropped)
update tu set
retained = r.TopLevel
from
dbo.testing tu
inner join rCTE r
on tu.retained = r.retained and
tu.dropped = r.dropped;




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24295 Visits: 37999
Abu Dina (9/25/2012)
Luis Cazares (9/25/2012)
I know it could be a bad solution, and maybe you had tought about it.
But since the rCTE is not scaling well (and it sure won't) why don't you use your original solution in a while loop for @@rowcount > 0?
I'm not sure which will scale better, but it's still an option.


Thanks Luis, your suggestion is valid. I see that Lynne has come back with a potential solution. I will test tomorrow and report back.

Regards.


By the way, no 'e' at the end of Lynn.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9028 Visits: 19041
Lynn Pettis (9/25/2012)
Here is my code, reformatted the way I like to it.



with rCTE as (
select
t1.retained,
t1.dropped,
t1.retained as TopLevel
from
dbo.testing t1
left outer join dbo.testing t2
on t1.retained = t2.dropped
where
t2.dropped is null
union all
select
t1.retained,
t1.dropped,
r.TopLevel
from
dbo.testing t1
inner join rCTE r
on t1.retained = r.dropped)
update tu set
retained = r.TopLevel
from
dbo.testing tu
inner join rCTE r
on tu.retained = r.retained and
tu.dropped = r.dropped;





Nice one, Lynn. I was working on something very similar which includes "evidence" in the data that it works;

;WITH rCTE AS (
SELECT [level] = 1, t1.retained, t1.dropped,
--fullchain = CAST(CAST(t1.retained AS VARCHAR(10)) + ',' + CAST(t1.dropped AS VARCHAR(10)) AS VARCHAR(8000)),
Toplevel = t1.retained
FROM dbo.testing t1
LEFT OUTER JOIN dbo.testing t2 ON t1.retained = t2.dropped
WHERE t2.dropped IS NULL
UNION ALL
SELECT [level] = [level] + 1, t1.retained, t1.dropped,
--fullchain = r.fullchain + ',' + CAST(t1.dropped AS VARCHAR(10)),
Toplevel = r.Toplevel
FROM dbo.testing t1
INNER JOIN rCTE r ON t1.retained = r.dropped
)
--SELECT *
--FROM rCTE
--ORDER BY level, dropped

UPDATE tu SET
retained = r.TopLevel
FROM dbo.testing tu
INNER JOIN rCTE r ON tu.dropped = r.dropped;

SELECT * FROM dbo.testing



Where evidence is the column [fullchain].

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search