Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Consolidating records - TSQL problem Expand / Collapse
Author
Message
Posted Tuesday, September 25, 2012 6:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:55 AM
Points: 6,782, Visits: 13,988
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
Post #1364006
Posted Tuesday, September 25, 2012 7:18 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:54 AM
Points: 708, Visits: 3,290
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
Post #1364024
Posted Tuesday, September 25, 2012 7:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:55 AM
Points: 6,782, Visits: 13,988
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
Post #1364043
Posted Tuesday, September 25, 2012 9:43 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:54 AM
Points: 708, Visits: 3,290
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!


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


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
Post #1364142
Posted Tuesday, September 25, 2012 11:26 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:55 PM
Points: 3,761, Visits: 8,439
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1364194
Posted Tuesday, September 25, 2012 1:04 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:48 PM
Points: 20,734, Visits: 32,499
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.




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)
Post #1364245
Posted Tuesday, September 25, 2012 2:25 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:54 AM
Points: 708, Visits: 3,290
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
Post #1364286
Posted Tuesday, September 25, 2012 11:54 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:48 PM
Points: 20,734, Visits: 32,499
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;





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)
Post #1364443
Posted Wednesday, September 26, 2012 12:07 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:48 PM
Points: 20,734, Visits: 32,499
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.



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)
Post #1364447
Posted Wednesday, September 26, 2012 2:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:55 AM
Points: 6,782, Visits: 13,988
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
Post #1364501
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse