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 ««1234»»»

rCTE vs LIKE for Hierarchy Expand / Collapse
Author
Message
Posted Wednesday, September 5, 2012 3:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 14, 2014 8:14 PM
Points: 32, Visits: 184
Sean,

No offense taken at all ! right now the procedure that i copied here is the only one that uses it, i just did some copy paste of existing code ( yeah I was lazy ) as this is still not in production, but it will be changed, i'm just yet looking for where the rCTE IO comes from right now, or if I'm misinterpreting results.
I don't have the actual numbers right now but the rCTE does something like a hundred table scans on the #hierarchy table ! the like approach doesn't gets even near that and still is outperformed by the rCTE. Right now I'm more worried on understanding that behavior than using a tally for string split, which I *think* will cause minor improvement on this, or RBARs can really be THAT bad on, such small strings (200 is small isn't it) ?
thanks for the attention so far =)
Post #1354912
Posted Wednesday, September 5, 2012 3:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 13,081, Visits: 12,545
ariel_mlk (9/5/2012)
Sean,

No offense taken at all ! right now the procedure that i copied here is the only one that uses it, i just did some copy paste of existing code ( yeah I was lazy ) as this is still not in production, but it will be changed, i'm just yet looking for where the rCTE IO comes from right now, or if I'm misinterpreting results.
I don't have the actual numbers right now but the rCTE does something like a hundred table scans on the #hierarchy table ! the like approach doesn't gets even near that and still is outperformed by the rCTE. Right now I'm more worried on understanding that behavior than using a tally for string split, which I *think* will cause minor improvement on this, or RBARs can really be THAT bad on, such small strings (200 is small isn't it) ?
thanks for the attention so far =)


Get rid of that RBAR function and then start your comparison. You are trying to read stats on a query that you know you need to drastically change. Change the query first and then look at what it is doing. I realize that with the code you posted it isn't doing anything but still...


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1354920
Posted Wednesday, September 5, 2012 3:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 14, 2014 8:14 PM
Points: 32, Visits: 184
Sean,

Thanks for the input ! i'll get that done by tomorrow and come back to buzz you guys again =)
hopefully i'll improve the procedure more than i expect it to
Post #1354923
Posted Wednesday, September 5, 2012 3:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 13,081, Visits: 12,545
ariel_mlk (9/5/2012)
Sean,

Thanks for the input ! i'll get that done by tomorrow and come back to buzz you guys again =)
hopefully i'll improve the procedure more than i expect it to


FWIW those two queries do not produce the same results.

Consider the following when your string to split is not null.

--Original Query Approach
DECLARE @pCustomerID int,
@pUserID int,
@pOUIDs varchar(max) = '367',
@pLocationIds varchar(max)
SET @pCustomerID = 14
SET @pUserID = 1
--SET @pOUIDs = NULL
SET @pLocationIDs = NULL


SELECT DISTINCT ou.OUID
FROM
(SELECT
ouChild.OUID,
ouChild.CustomerID,
ouChild.Enabled,
ou.OUID as OUIDAncestor
FROM #Hierarchy ou
INNER JOIN #Hierarchy ouChild ON ouChild.Hierarchy + '.' LIKE ou.Hierarchy + '.%'
)ou
WHERE
ou.CustomerID = @pCustomerID
AND ou.Enabled = 1
AND
(
@pOUIDs IS NULL
OR
@pOUIDs LIKE '%,' + CAST(OUIDAncestor AS VARCHAR(20)) + ',%'
);
-- CTE Approach

;WITH LocationHierarchy As
(
SELECT
ouf.OUID,ouf.OUParentID,ouf.CustomerID,ouf.Hierarchy,ouf.Enabled
FROM
#Hierarchy ouf
Where
OUID in (select val from dbo.Split(@pOUIDs,',')) or @pOUIDs is null
UNION ALL
SELECT
ouc.OUID,ouc.OUParentID,ouc.CustomerID,ouc.Hierarchy,ouc.Enabled
FROM
#Hierarchy ouc INNER JOIN
LocationHierarchy on ouc.OUParentID = LocationHierarchy.OUID
Where
ouc.Enabled = 1 and ouc.CustomerID = @pCustomerID
)
Select
DISTINCT ou.OUID
from
LocationHierarchy ou

The results are different in the two queries so your comparison is even further off track.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1354925
Posted Wednesday, September 5, 2012 3:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 14, 2014 8:14 PM
Points: 32, Visits: 184
Sean,

that would produce different results indeed, but the old code would expect ',367,' to work properly, i can't test it right now, but i assume that with those leading and ending comma's they produce the same output, or i have screwed up on the copy paste.
Post #1354927
Posted Friday, September 7, 2012 2:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:08 PM
Points: 35,362, Visits: 31,899
How many rows in this hierarchy?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1356258
Posted Tuesday, September 11, 2012 1:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 14, 2014 8:14 PM
Points: 32, Visits: 184
1146 rows, which should not be a problem, but some procedures take up to 4 minutes to run with the like code, and that's pretty bad, my concern is that the rCTE code generates something like 100 table scans with 10k+ logical reads, and I'm a begginer to this but I don't think this much IO is ok, the LIKE code posted does something like 1 scan 68 logical reads, yet performs timewise much much worse and that's where i get lost
Post #1357670
Posted Tuesday, September 11, 2012 1:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
A thousand-row adjacency hierarchy should be pretty fast to resolve using an rCTE. You'll get a lot of I/O (which you've already seen), but performance should be pretty fast. It'll depend on the average depth and width of the branches, of course.

The Like method, done correctly, can use indexes, so the number of scans/reads will be low, but the overall performance would normally be pretty poor. That's what you're seeing, if I understand your posts correctly, so that matches theory and practice appropriately. Like operations with no variable on the leading edge, can use indexes and can actually be SARGable, so they can be quite efficient when done well. However, that will only allow a limited number of hierarchy operations, like finding all ancestors or all descendents, without being efficient at things like finding the immediate ancestor or the fifth descendent.

With that Hierarchy column, you're already about 90% of the way to a HierarchyID solution. On such a small table, one of those will be amazingly fast. You posted in the SQL 2008 forum, does that mean you're using SQL 2008? If so, you should test converting to HierarchyID on this.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1357681
Posted Tuesday, September 11, 2012 2:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 14, 2014 8:14 PM
Points: 32, Visits: 184
Thanks for the replys, I've always linked IO reads to performance, so i was very confused when i saw a solution generating higher IO and still performing way better, yes I'm using a SQL Server 2008 and I would love to get my hands into changing that field to a HierarchyID but i'll have to study impacts that could cause on existing code so for now i'll ride with a rCTE, I'm aware this is already a bit off the topic, but anyone could point some read (book,blog,alikes) so I could take better grasp with IO / Performance relationship so I won't get frustated again with IO? Thanks Guys !
Post #1357695
Posted Tuesday, September 11, 2012 2:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:08 PM
Points: 35,362, Visits: 31,899
ariel_mlk (9/11/2012)
1146 rows, which should not be a problem, but some procedures take up to 4 minutes to run with the like code, and that's pretty bad, my concern is that the rCTE code generates something like 100 table scans with 10k+ logical reads, and I'm a begginer to this but I don't think this much IO is ok, the LIKE code posted does something like 1 scan 68 logical reads, yet performs timewise much much worse and that's where i get lost


I agree. Believe it or not, a While loop that does the same thing as the rCTE will work at least as fast and sometimes faster and with a heck of a lot less reads.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1357699
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse