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 ««12

Grouping Related Rows in Same Table Expand / Collapse
Author
Message
Posted Wednesday, June 19, 2013 10:47 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:15 PM
Points: 4,469, Visits: 6,398
Seems like further clarification of the needs is required. Should "chained" intersections be allowed/considered as one group? Or discarded.

I also note that the cursor version for your data returns:

ShapeID GroupID
------- -------
A1B20 1
D1B19 1
E3A22 2
C5B23 3
A1B11 3
D1B25 4

Since D1B19 and E3A22 intersect, it would seem apparent that a modification to the code would be required to make the output actually put BOTH of those in the same group, i.e.

ShapeID GroupID
------- -------
A1B20 1
D1B19 1
D1B19 2
E3A22 2
C5B23 3
A1B11 3
D1B25 4

Again, some logic requirements are needed for muti-grouping (such as happens for D1B19 here).

OP, what is the actual and complete desired effect of this new set of inputs??


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1465291
Posted Wednesday, June 19, 2013 1:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 4:45 PM
Points: 14, Visits: 71
TheSQLGuru (6/19/2013)
Seems like further clarification of the needs is required. Should "chained" intersections be allowed/considered as one group? Or discarded.


TheSQLGuru (6/19/2013)
Since D1B19 and E3A22 intersect, it would seem apparent that a modification to the code would be required to make the output actually put BOTH of those in the same group, i.e.

Again, some logic requirements are needed for muti-grouping (such as happens for D1B19 here).

OP, what is the actual and complete desired effect of this new set of inputs??


Multi-grouping is not allowed. If a series of shapes intersect in a "daisy chain" fashion then ultimately they all belong to the same group. Making the change that Mark describes produces the desire effect when using the recursive CTE. However, when using the CURSOR the line segment E3A22 shows as a separate group. It would be acceptable in this case but not preferred. Furthermore, since E3A22 is not present in more than one group and all shapes are accounted for, it will suffice.

Thank you Kevin and Mark.

Mike
Post #1465351
Posted Thursday, July 4, 2013 7:19 PM


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 @ 9:53 PM
Points: 3,438, Visits: 5,390
TheSQLGuru (6/19/2013)
tssopa (6/19/2013)
Okay, I am a little dissapointed. I had read how efficient and fast recursive CTE's are compared to CURSOR's, however when I run the CTE against my data set of 1000+ records it takes far too long. When I run the CURSOR it only takes 4 seconds. The shape table has the ShapeID as the primary key and the shape field does have a spatial index. Any thoughts on why the recursive CTE is taking so long?


Recursive CTEs SUCK @SS and should be avoided at almost all costs, IMNSHO. I only use them when there is NO other recourse or testing shows they are optimal for a specific data processing need.



Very strong anti-rCTE statement there!

I'll protest. They are but a tool. Often times they are misused but for some things they are the best option available.

Jeff Moden once suggested to me that any rCTE can be rewritten as a set-based loop. The loop basically looks something like this (using a Temp table):

INSERT INTO #Temp
-- rCTE anchor leg

WHILE -- terminating criteria

INSERT INTO #Temp
-- rCTE recursive leg

END


This can be faster than the rCTE (I did this in one of my articles if you're interested in the proof).



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1470544
Posted Friday, July 5, 2013 8:35 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:15 PM
Points: 4,469, Visits: 6,398
Protest all you want. But do note that I was NOT unequivocal in my statements and did allow for edge-case usage.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1470766
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse