InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 00:15:42 GMT20http://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspxOn the I/O, the high reads+scans on rCTEs are because it has to check each row that comes up for children rows.Example (simple) hierarchy:1 null2 13 14 25 26 37 3If you crawl starting at 1, it has to then scan the index to find all rows where ParentID = 1. So, 1 seek (shows as a scan in I/O stats) to find "Where ID = 1", then 1 scan to find the children of that node. Then it has to take ID = 2 and scan to find all children of 2, and then the same for all children of 3. So 2 scans for that level. We're already looking at 5 scans and 7 reads, and this hierarchy is 3 levels deep and has 7 total nodes. It gets worse the wider the hierarchy is and the deeper it is.Nested Sets1 122 73 45 68 119 910 10That's the same hierarchy (1 top node, with 2 immediate children, each of which has 2 children). I/O will be 1 scan. If the hierarchy is the clustered index (normally is), the speed is nearly instanteous on these. I've seen a thousand-node hierarchy, 70+ levels deep, with variable width and complexity on the down-paths, take 2 milliseconds to query and return the data. Took longer to pipe it to the web server through Gigabit ethernet than it did to pull it from the SAN. Even without caching.Plus, nested sets doesn't allow for infinite-loop hierarchies. Adjacency, especially multi-parent adjacency, can create that problem. Nested sets also doesn't allow for broken hierarchies. Delete the wrong node and don't correct for it, and an adjacency hierarchy breaks. In the example above, if you delete node 3, nodes 6 and 7 are now orphans. A good trigger can help prevent that, but you can't always count on that covering everything you need. Delete node 2 7 in the nested sets, and 3 4 and 5 6 are not orphans, they just become immediate children of node 1 12.Of course, if you need to move a whole branch around, that's dead easy in adjacency and a pain in the *** in nested sets. Easy but slower with HierarchyID.Check my articles for details.Mon, 17 Sep 2012 09:17:29 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspx[quote][b]Jeff Moden (9/14/2012)[/b][hr][quote][b]GSquared (9/13/2012)[/b][hr]...Tests that don't involve any table-query, just Insert statements, or a RAM-resident Select (like the cCTE), don't test all the factors in building an adjacency hierarchy crawl in a While loop.Here's a test of that kind of thing:...I created a test table a while back with a set of complex hierarchies in it. The one that starts with ID 1, used in this test, is 427 levels deep. So the rCTE needs the MaxRecursion option in order to crawl the whole thing. That slows it down, of course.In my tests (core i7 Quad, 16 Gig RAM, Windows 7 64-bit, running SQL 2008 R2 Dev Edition), the rCTE averages 6 milliseconds for the whole crawl, including the DDL for the Select Into (used to eliminate data-rendering time). The While loop averages 2296 milliseconds.....[/quote]How many rows in that hierarchy and is there any chance of seeinng the similar DDL for the table including the indexing directly related to your queries? If you could provide what the average and max fan-out is for your tests, that would help. I'd like to do some additional testing on this and that information might help me in how I design the typical million row test table on something like this. Thanks, Gus.[/quote]Table structure is the one from my recent hierarchies article.Number of rows and complexity of hierarchies in it vary depending on what I'm testing. This test was 427 levels deep with each level being 1 node. Built that set just to test how deep HierarchyID could go, and didn't bother changing it to test this aspect.I've tested more complex hierarchies While vs rCTE, and the results have always been comparable, with the While Loop being much slower. The slow-down is because of having to filter each run to not rerun what's been pulled in prior runs. There are ways to work around that, but you end up with insanely complex code, and it's still slower than a simple rCTE for an adjacency crawl in every test I've run on it. (Worst workaround I ever saw used a recursive UDF and nested cursors in order to avoid Where Not In. Ouch! Talk about slow!)Mon, 17 Sep 2012 08:59:40 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspx[quote]Duration is usually what I shoot for because that's all end-users can perceive or actually care about (other than accuracy, of course). I'll even use "extra resources" to improve the end-user experience, if need be. There are other times where I look down the road and see that something should not use extra-resources because of future "load" o the machine even if it does mean better performance in the short term. "It Depends".For the IO, as much as I hate to admit it, "It Depends". There's physical IO (disk) and there's logical IO (basically, some form of memory in most cases). As you have found, there's usually a direct correlation between the number of reads something has and what its performance is. That's not always the case (Paul White did a brilliant job on a very high performance Triangular Join, in the past). Still, I also try to limit reads (especially if a large number of reads is involved) because they're either using the disk system or the memory system.For why rCTEs appear to use so many more reads than other methods, I have my own suspicions but don't really want to know that much about the "guts" of it because it's not likely that I can change it. As Paul White once suggested, it may be something as simple as SQL Server considering each row read to being a "read" instead of a measure of how many pages were read. [/quote]Thanks for your time Jeff that was the answer I was looking for, response time is the very reason i'm tunning this ! And whilst the performance with the rCTE solved the response time issues I just could not live with the doubt of where all that IO was coming from ![quote]Ariel Nessi (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[quote]I believe I have a method that will blow the doors off things like that. What are your more lengthy runs doing? For example are you trying to calculate the "rollup cost" of each node? ][/quote][/quote]Actually for those numbers of IO I'm just getting all childs and sub-childs in the hierarchy taking the uppermost registry on the hierarchy ( so it's pretty much like an select * from ) This piece of code is used to filter another table with a join on it.Works like this,User picks Locations it would like to search so when he picks, lets say, France, I'll drill down the hierarchy getting every district, street, city and so on and then join those IDs on another table.When I pick location "Universe" I get that IOFor all that matters my question was answered, IO is not the holy grail (I all cases).Fri, 14 Sep 2012 17:29:23 GMTAriel Nessihttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspx[quote][b]Ariel Nessi (9/11/2012)[/b][hr]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[/quote]I believe I have a method that will blow the doors off things like that. What are your more lengthy runs doing? For example are you trying to calculate the "rollup cost" of each node?Fri, 14 Sep 2012 13:49:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspx[quote][b]Ariel Nessi (9/13/2012)[/b][hr]Alright guys,Jeff,[quote]If you don't think duration is a good enough indication of such things, then you can certainly run the code with SQL Profiler turned on.[/quote] That's exactly my point, when I'm looking to tune things up I always looking for duration,but I always thought IO counts were pretty much directly associated with overall performance, and therefore, with response time. Following by your examples, the fastest one is the one that doesn't even generate IO and one can perceive an IO -> Duration relation with all the others.Now back to my specific case, the rCTE IS faster than the LIKE that's a fact at least for my enviroment, but it skyroofs on IO comparison ! I just would like to understand that behavior.[/quote]Duration is usually what I shoot for because that's all end-users can perceive or actually care about (other than accuracy, of course). I'll even use "extra resources" to improve the end-user experience, if need be. There are other times where I look down the road and see that something should not use extra-resources because of future "load" o the machine even if it does mean better performance in the short term. "It Depends".For the IO, as much as I hate to admit it, "It Depends". There's physical IO (disk) and there's logical IO (basically, some form of memory in most cases). As you have found, there's usually a direct correlation between the number of reads something has and what its performance is. That's not always the case (Paul White did a brilliant job on a very high performance Triangular Join, in the past). Still, I also try to limit reads (especially if a large number of reads is involved) because they're either using the disk system or the memory system.For why rCTEs appear to use so many more reads than other methods, I have my own suspicions but don't really want to know that much about the "guts" of it because it's not likely that I can change it. ;-) As Paul White once suggested, it may be something as simple as SQL Server considering each row read to being a "read" instead of a measure of how many pages were read.Fri, 14 Sep 2012 13:42:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspx[quote][b]GSquared (9/13/2012)[/b][hr]While an rCTE vs a While Loop can be a poor decision, there are other times when it's not.Tests that don't involve any table-query, just Insert statements, or a RAM-resident Select (like the cCTE), don't test all the factors in building an adjacency hierarchy crawl in a While loop.Here's a test of that kind of thing:[code="sql"]IF OBJECT_ID(N'tempdb..#T') IS NOT NULL DROP TABLE #T;DECLARE @Start DATETIME = GETDATE();WITH rCTE AS (SELECT ID, ParentID, 1 AS Lvl FROM dbo.HierarchyTest WHERE ID = 1 UNION ALL SELECT H2.ID, H2.ParentID, rCTE.Lvl + 1 FROM dbo.HierarchyTest AS H2 INNER JOIN rCTE ON H2.ParentID = rCTE.ID) SELECT * INTO #T FROM rCTEOPTION (MAXRECURSION 0);SELECT DATEDIFF(millisecond, @Start, GETDATE()) AS TotalCTETime;IF OBJECT_ID(N'tempdb..#T2') IS NOT NULL DROP TABLE #T;SET @Start = GETDATE();CREATE TABLE #T2 (ID INT, ParentID INT, Lvl INT);INSERT INTO #T2 (ID, ParentID, Lvl) SELECT ID, ParentID, 1 FROM dbo.HierarchyTest WHERE ID = 1;WHILE @@rowcount > 0 INSERT INTO #T2 (ID, ParentID, Lvl) SELECT H2.ID, H2.ParentID, #T2.Lvl + 1 FROM dbo.HierarchyTest AS H2 INNER JOIN #T2 ON H2.ParentID = #T2.ID WHERE H2.ID NOT IN (SELECT ID FROM #T2);SELECT DATEDIFF(millisecond, @Start, GETDATE()) AS TotalWhileTime;[/code]I created a test table a while back with a set of complex hierarchies in it. The one that starts with ID 1, used in this test, is 427 levels deep. So the rCTE needs the MaxRecursion option in order to crawl the whole thing. That slows it down, of course.In my tests (core i7 Quad, 16 Gig RAM, Windows 7 64-bit, running SQL 2008 R2 Dev Edition), the rCTE averages 6 milliseconds for the whole crawl, including the DDL for the Select Into (used to eliminate data-rendering time). The While loop averages 2296 milliseconds.Inserting the While loop version into a table variable, to reduce logging, doesn't materially change the performance (+/- 30 milliseconds compared to temp table). Probably what it gains in lack of logging, it loses in the Where Not In piece of the query (necessary to avoid an infinite loop).On smaller hierarchies, the While Loop version is usually "fast enough" that it doesn't matter. But the rCTE will usually be faster on the same datasets. Has been in ever test I've ever done, but that's just my testing, and YMMV always applies.As an aside, the same table has HierarchyID datatype information on the same hierarchies. The same test structure gives an average query time of 36 milliseconds when querying that way. Slower than the rCTE for such a simple query, but still far faster than the While Loop method. More complex queries will slow down the rCTE significantly, while Hierarchy will, per my tests, stay about the same. Either will usually remain much faster than a comparable While Loop. Nested Sets will be faster to query, of course. Usually sub-millisecond for most common hierarchy structures.[/quote]How many rows in that hierarchy and is there any chance of seeinng the similar DDL for the table including the indexing directly related to your queries? If you could provide what the average and max fan-out is for your tests, that would help. I'd like to do some additional testing on this and that information might help me in how I design the typical million row test table on something like this. Thanks, Gus.Fri, 14 Sep 2012 13:21:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspxOn hierarchies, While and rCTE usually do one index scan per level in the hierarchy. It can blow up to one scan per node in some cases. That's the main advantage of both Nested Sets and HierarchyID, in that they usually just do one index/table scan, and it's a range-scan not a full scan.Fri, 14 Sep 2012 06:19:10 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspxAlright guys,Jeff,[quote]If you don't think duration is a good enough indication of such things, then you can certainly run the code with SQL Profiler turned on.[/quote] That's exactly my point, when I'm looking to tune things up I always looking for duration,but I always thought IO counts were pretty much directly associated with overall performance, and therefore, with response time. Following by your examples, the fastest one is the one that doesn't even generate IO and one can perceive an IO -> Duration relation with all the others.Now back to my specific case, the rCTE IS faster than the LIKE that's a fact at least for my enviroment, but it skyroofs on IO comparison ! I just would like to understand that behavior.GSquared, that was very informative for me, thanks for the input, I'll try that while in with my scenario, so rCTE really tend to be faster than while's for Hierarchy crawling, that's a relief, I'll just check if their IO behavior follow their duration behavior, or if I should throw away this IO/Duration relation I believe that existsThu, 13 Sep 2012 14:14:36 GMTAriel Nessihttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspxWhile an rCTE vs a While Loop can be a poor decision, there are other times when it's not.Tests that don't involve any table-query, just Insert statements, or a RAM-resident Select (like the cCTE), don't test all the factors in building an adjacency hierarchy crawl in a While loop.Here's a test of that kind of thing:[code="sql"]IF OBJECT_ID(N'tempdb..#T') IS NOT NULL DROP TABLE #T;DECLARE @Start DATETIME = GETDATE();WITH rCTE AS (SELECT ID, ParentID, 1 AS Lvl FROM dbo.HierarchyTest WHERE ID = 1 UNION ALL SELECT H2.ID, H2.ParentID, rCTE.Lvl + 1 FROM dbo.HierarchyTest AS H2 INNER JOIN rCTE ON H2.ParentID = rCTE.ID) SELECT * INTO #T FROM rCTEOPTION (MAXRECURSION 0);SELECT DATEDIFF(millisecond, @Start, GETDATE()) AS TotalCTETime;IF OBJECT_ID(N'tempdb..#T2') IS NOT NULL DROP TABLE #T;SET @Start = GETDATE();CREATE TABLE #T2 (ID INT, ParentID INT, Lvl INT);INSERT INTO #T2 (ID, ParentID, Lvl) SELECT ID, ParentID, 1 FROM dbo.HierarchyTest WHERE ID = 1;WHILE @@rowcount > 0 INSERT INTO #T2 (ID, ParentID, Lvl) SELECT H2.ID, H2.ParentID, #T2.Lvl + 1 FROM dbo.HierarchyTest AS H2 INNER JOIN #T2 ON H2.ParentID = #T2.ID WHERE H2.ID NOT IN (SELECT ID FROM #T2);SELECT DATEDIFF(millisecond, @Start, GETDATE()) AS TotalWhileTime;[/code]I created a test table a while back with a set of complex hierarchies in it. The one that starts with ID 1, used in this test, is 427 levels deep. So the rCTE needs the MaxRecursion option in order to crawl the whole thing. That slows it down, of course.In my tests (core i7 Quad, 16 Gig RAM, Windows 7 64-bit, running SQL 2008 R2 Dev Edition), the rCTE averages 6 milliseconds for the whole crawl, including the DDL for the Select Into (used to eliminate data-rendering time). The While loop averages 2296 milliseconds.Inserting the While loop version into a table variable, to reduce logging, doesn't materially change the performance (+/- 30 milliseconds compared to temp table). Probably what it gains in lack of logging, it loses in the Where Not In piece of the query (necessary to avoid an infinite loop).On smaller hierarchies, the While Loop version is usually "fast enough" that it doesn't matter. But the rCTE will usually be faster on the same datasets. Has been in ever test I've ever done, but that's just my testing, and YMMV always applies.As an aside, the same table has HierarchyID datatype information on the same hierarchies. The same test structure gives an average query time of 36 milliseconds when querying that way. Slower than the rCTE for such a simple query, but still far faster than the While Loop method. More complex queries will slow down the rCTE significantly, while Hierarchy will, per my tests, stay about the same. Either will usually remain much faster than a comparable While Loop. Nested Sets will be faster to query, of course. Usually sub-millisecond for most common hierarchy structures.Thu, 13 Sep 2012 12:10:43 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspx[quote][b]Ariel Nessi (9/13/2012)[/b][hr]Jeff,Not only i'm new here as I'm new to SQL Server, but I've already read several of your posts, and others members of this community to be aware that when you state something you can back it up, I didn't mean to doubt you, and the examples are indeed a proof of what you state, I was just surprised rCTE's could perform slower than while's, I'll try to re-write that rCTE and play around with it, I didn't mean to be disrespectful in any way, thanks for you time ! :-)[/quote]Absolutely no offense taken and no disrespect perceived. In retrospect, I'm the one that was out of line because I made a statement about a contested performance point with no immediate proof in the form of code. Never just trust people on things like this. Not even me. Your questions were spot on and appreciated.Thu, 13 Sep 2012 11:37:28 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspxJeff,Not only i'm new here as I'm new to SQL Server, but I've already read several of your posts, and others members of this community to be aware that when you state something you can back it up, I didn't mean to doubt you, and the examples are indeed a proof of what you state, I was just surprised rCTE's could perform slower than while's, I'll try to re-write that rCTE and play around with it, I didn't mean to be disrespectful in any way, thanks for you time ! :-)Thu, 13 Sep 2012 11:27:40 GMTAriel Nessihttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspx[quote][b]ariel_mlk (9/12/2012)[/b][hr][quote]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.[/quote]Alright let me get on my feet again ! I'll write that down on the "obsure rituals" section of my notes, I'll test on that, I'm surprised rCTE are so popular for this kind of job if while loops can save you some big time IO, you have come by this by testing or is there any logic to this ?Thanks for the attention[/quote]You're kind of new here so you don't know. I don't say these types of things unless I can back them up. Normally, I'll include the proof code with whatever post I make such a statement in but was in a hurry last night and didn't.While the following code is not the end-all to be-all on this subject, it does show what I'm talking about. It's a simple "Count from 1 to a million and store it in a table" bit of code. Of course, both the WHILE loop and the rCTE make sucking sounds when you compare them to other methods (a couple of which are also included in the test code below). If you don't think duration is a good enough indication of such things, then you can certainly run the code with SQL Profiler turned on.[code="sql"]RAISERROR('******************************************************************************* Create a table and then insert a million sequential numbers.*******************************************************************************',0,1) WITH NOWAIT;RAISERROR('=============================================================================== WHILE Loop Insert in a single explicit transaction.===============================================================================',0,1) WITH NOWAIT;--===== Environmenntal an other presets DBCC FREEPROCCACHE WITH NO_INFOMSGS; SET NOCOUNT ON;DECLARE @StartTime DATETIME, @DurationMS INT;--===== Start the timer SELECT @StartTime = GETDATE();--===== Create a place to store the results IF OBJECT_ID('dbo.TestNumbers','U') IS NOT NULL DROP TABLE dbo.TestNumbers; CREATE TABLE dbo.TestNumbers (N INT); --New code added--===== Count from 1 to 1000000 and display the count.DECLARE @Counter INT; --Declare a counter SET @Counter = 1; --Preset the counter to 1--===== Insert the million rows in a single transaction -- just like an rCTE/INSERT would. BEGIN TRANSACTION; WHILE @Counter <= 1000000 BEGIN INSERT INTO dbo.TestNumbers (N) --New code added SELECT @Counter; --Display the count (same as before) SET @Counter = @Counter + 1; --Add 1 to counter END; --Is the counter <= 100? --If Yes, branch back to display the count. --If no, continue. COMMIT;--===== Display the duration SELECT @DurationMS = DATEDIFF(ms,@StartTime,GETDATE()); RAISERROR('Duration MS: %u',0,1,@DurationMS) WITH NOWAIT;GORAISERROR('=============================================================================== rCTE Insert in a single implicit transaction.===============================================================================',0,1) WITH NOWAIT;--===== Environmenntal an other presets DBCC FREEPROCCACHE WITH NO_INFOMSGS; SET NOCOUNT ON;DECLARE @StartTime DATETIME, @DurationMS INT;--===== Start the timer SELECT @StartTime = GETDATE();--===== Create a place to store the results IF OBJECT_ID('dbo.TestNumbers','U') IS NOT NULL DROP TABLE dbo.TestNumbers; CREATE TABLE dbo.TestNumbers (N INT); --New code added--===== Insert the million rows in a single transaction WITHcteCounter AS(--==== Counter rCTE counts from 0 to 11 SELECT 0 AS N --This provides the starting point (anchor) of zero UNION ALL SELECT N + 1 --This is the recursive part FROM cteCounter WHERE N < 1000000)--==== Add the counter value to a start date and you get multiple dates INSERT INTO dbo.TestNumbers (N) SELECT N FROM cteCounter OPTION (MAXRECURSION 0);--===== Display the duration SELECT @DurationMS = DATEDIFF(ms,@StartTime,GETDATE()); RAISERROR('Duration MS: %u',0,1,@DurationMS) WITH NOWAIT;GORAISERROR('=============================================================================== CROSS JOIN as a row-source Insert in a single implicit transaction.===============================================================================',0,1) WITH NOWAIT;--===== Environmenntal an other presets DBCC FREEPROCCACHE WITH NO_INFOMSGS; SET NOCOUNT ON;DECLARE @StartTime DATETIME, @DurationMS INT;--===== Start the timer SELECT @StartTime = GETDATE();--===== Create a place to store the results IF OBJECT_ID('dbo.TestNumbers','U') IS NOT NULL DROP TABLE dbo.TestNumbers; CREATE TABLE dbo.TestNumbers (N INT); --New code added--===== Insert the million rows in a single transaction INSERT INTO dbo.TestNumbers (N) SELECT TOP 1000000 N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2;--===== Display the duration SELECT @DurationMS = DATEDIFF(ms,@StartTime,GETDATE()); RAISERROR('Duration MS: %u',0,1,@DurationMS) WITH NOWAIT;GORAISERROR('=============================================================================== Cascading CTE (cCTE) as a row-source Insert in a single implicit transaction.===============================================================================',0,1) WITH NOWAIT;--===== Environmenntal an other presets DBCC FREEPROCCACHE WITH NO_INFOMSGS; SET NOCOUNT ON;DECLARE @StartTime DATETIME, @DurationMS INT;--===== Start the timer SELECT @StartTime = GETDATE();--===== Create a place to store the results IF OBJECT_ID('dbo.TestNumbers','U') IS NOT NULL DROP TABLE dbo.TestNumbers; CREATE TABLE dbo.TestNumbers (N INT); --===== Insert the million rows in a single transaction WITH E1(N) AS ( --=== Create Ten 1's SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 --10 ), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000 E8(N) AS (SELECT 1 FROM E4 a, E4 b), --100,000,000 E16(N) AS (SELECT 1 FROM E8 a, E8 b), --10,000,000,000,000,000 cteTally(N) AS (SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E16) INSERT INTO dbo.TestNumbers (N) SELECT t.N --Some query that uses the sequential numbering FROM cteTally t;--===== Display the duration SELECT @DurationMS = DATEDIFF(ms,@StartTime,GETDATE()); RAISERROR('Duration MS: %u',0,1,@DurationMS) WITH NOWAIT;GORAISERROR('******************************************************************************* Create the table on-the-fly using minimal logginng whenever possible.*******************************************************************************',0,1) WITH NOWAIT;RAISERROR('=============================================================================== WHILE Loop Insert in a single explicit transaction. (Same as before. Cannot use SELECT INTO to create the table)===============================================================================',0,1) WITH NOWAIT;--===== Environmenntal an other presets DBCC FREEPROCCACHE WITH NO_INFOMSGS; SET NOCOUNT ON;DECLARE @StartTime DATETIME, @DurationMS INT;--===== Start the timer SELECT @StartTime = GETDATE();--===== Create a place to store the results IF OBJECT_ID('dbo.TestNumbers','U') IS NOT NULL DROP TABLE dbo.TestNumbers; CREATE TABLE dbo.TestNumbers (N INT); --New code added--===== Count from 1 to 1000000 and display the count.DECLARE @Counter INT; --Declare a counter SET @Counter = 1; --Preset the counter to 1--===== Insert the million rows in a single transaction -- just like an rCTE/INSERT would. BEGIN TRANSACTION; WHILE @Counter <= 1000000 BEGIN INSERT INTO dbo.TestNumbers (N) --New code added SELECT @Counter; --Display the count (same as before) SET @Counter = @Counter + 1; --Add 1 to counter END; --Is the counter <= 100? --If Yes, branch back to display the count. --If no, continue. COMMIT;--===== Display the duration SELECT @DurationMS = DATEDIFF(ms,@StartTime,GETDATE()); RAISERROR('Duration MS: %u',0,1,@DurationMS) WITH NOWAIT;GORAISERROR('=============================================================================== rCTE Insert in a single implicit transaction.===============================================================================',0,1) WITH NOWAIT;--===== Environmenntal an other presets DBCC FREEPROCCACHE WITH NO_INFOMSGS; SET NOCOUNT ON;DECLARE @StartTime DATETIME, @DurationMS INT;--===== Start the timer SELECT @StartTime = GETDATE();--===== Create a place to store the results IF OBJECT_ID('dbo.TestNumbers','U') IS NOT NULL DROP TABLE dbo.TestNumbers; --===== Insert the million rows in a single transaction WITHcteCounter AS(--==== Counter rCTE counts from 0 to 11 SELECT 0 AS N --This provides the starting point (anchor) of zero UNION ALL SELECT N + 1 --This is the recursive part FROM cteCounter WHERE N < 1000000)--==== Add the counter value to a start date and you get multiple dates SELECT N INTO dbo.TestNumbers FROM cteCounter OPTION (MAXRECURSION 0);--===== Display the duration SELECT @DurationMS = DATEDIFF(ms,@StartTime,GETDATE()); RAISERROR('Duration MS: %u',0,1,@DurationMS) WITH NOWAIT;GORAISERROR('=============================================================================== CROSS JOIN as a row-source Insert in a single implicit transaction.===============================================================================',0,1) WITH NOWAIT;--===== Environmenntal an other presets DBCC FREEPROCCACHE WITH NO_INFOMSGS; SET NOCOUNT ON;DECLARE @StartTime DATETIME, @DurationMS INT;--===== Start the timer SELECT @StartTime = GETDATE();--===== Create a place to store the results IF OBJECT_ID('dbo.TestNumbers','U') IS NOT NULL DROP TABLE dbo.TestNumbers;--===== Insert the million rows in a single transaction SELECT TOP 1000000 N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) INTO dbo.TestNumbers FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2;--===== Display the duration SELECT @DurationMS = DATEDIFF(ms,@StartTime,GETDATE()); RAISERROR('Duration MS: %u',0,1,@DurationMS) WITH NOWAIT;GORAISERROR('=============================================================================== Cascading CTE (cCTE) as a row-source Insert in a single implicit transaction.===============================================================================',0,1) WITH NOWAIT;--===== Environmenntal an other presets DBCC FREEPROCCACHE WITH NO_INFOMSGS; SET NOCOUNT ON;DECLARE @StartTime DATETIME, @DurationMS INT;--===== Start the timer SELECT @StartTime = GETDATE();--===== Create a place to store the results IF OBJECT_ID('dbo.TestNumbers','U') IS NOT NULL DROP TABLE dbo.TestNumbers; --===== Insert the million rows in a single transaction WITH E1(N) AS ( --=== Create Ten 1's SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 --10 ), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000 E8(N) AS (SELECT 1 FROM E4 a, E4 b), --100,000,000 E16(N) AS (SELECT 1 FROM E8 a, E8 b), --10,000,000,000,000,000 cteTally(N) AS (SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E16) SELECT t.N --Some query that uses the sequential numbering INTO dbo.TestNumbers FROM cteTally t;--===== Display the duration SELECT @DurationMS = DATEDIFF(ms,@StartTime,GETDATE()); RAISERROR('Duration MS: %u',0,1,@DurationMS) WITH NOWAIT;GO[/code]Here are the resullts from my laptop computer (I5 w/4 core and 6GB ram). Notice that even the minimally logged (SELECT INTO) version of the rCTE is still slower than the While Loop version.[code="plain"]******************************************************************************* Create a table and then insert a million sequential numbers.*******************************************************************************=============================================================================== WHILE Loop Insert in a single explicit transaction.===============================================================================Duration MS: 8180=============================================================================== rCTE Insert in a single implicit transaction.===============================================================================Duration MS: 12666=============================================================================== CROSS JOIN as a row-source Insert in a single implicit transaction.===============================================================================Duration MS: 2016=============================================================================== Cascading CTE (cCTE) as a row-source Insert in a single implicit transaction.===============================================================================Duration MS: 2323******************************************************************************* Create the table on-the-fly using minimal logginng whenever possible.*******************************************************************************=============================================================================== WHILE Loop Insert in a single explicit transaction. (Same as before. Cannot use SELECT INTO to create the table)===============================================================================Duration MS: 8226=============================================================================== rCTE Insert in a single implicit transaction.===============================================================================Duration MS: 9410=============================================================================== CROSS JOIN as a row-source Insert in a single implicit transaction.===============================================================================Duration MS: 370=============================================================================== Cascading CTE (cCTE) as a row-source Insert in a single implicit transaction.===============================================================================Duration MS: 350[/code]Wed, 12 Sep 2012 14:03:40 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspx[quote][b]ariel_mlk (9/12/2012)[/b][hr][quote]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.[/quote]Alright let me get on my feet again ! I'll write that down on the "obsure rituals" section of my notes, I'll test on that, I'm surprised rCTE are so popular for this kind of job if while loops can save you some big time IO, you have come by this by testing or is there any logic to this ?Thanks for the attention[/quote]For Jeff, trust me, it comes from extensive testing.And even then, test, test, and test again. What works well in some situations may not in others.Wed, 12 Sep 2012 11:22:16 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspx[quote]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.[/quote]Alright let me get on my feet again ! I'll write that down on the "obsure rituals" section of my notes, I'll test on that, I'm surprised rCTE are so popular for this kind of job if while loops can save you some big time IO, you have come by this by testing or is there any logic to this ?Thanks for the attentionWed, 12 Sep 2012 11:12:56 GMTAriel Nessihttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspx[quote][b]ariel_mlk (9/11/2012)[/b][hr]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[/quote]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.Tue, 11 Sep 2012 14:26:36 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspxThanks 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 !Tue, 11 Sep 2012 14:17:13 GMTAriel Nessihttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspxA 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.Tue, 11 Sep 2012 13:53:52 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspx1146 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 lostTue, 11 Sep 2012 13:40:59 GMTAriel Nessihttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspxHow many rows in this hierarchy?Fri, 07 Sep 2012 14:56:58 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspxSean,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.Wed, 05 Sep 2012 15:49:53 GMTAriel Nessihttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspx[quote][b]ariel_mlk (9/5/2012)[/b][hr]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[/quote]FWIW those two queries do not produce the same results.Consider the following when your string to split is not null.[code]--Original Query ApproachDECLARE @pCustomerID int, @pUserID int, @pOUIDs varchar(max) = '367', @pLocationIds varchar(max)SET @pCustomerID = 14SET @pUserID = 1--SET @pOUIDs = NULLSET @pLocationIDs = NULLSELECT 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[/code]The results are different in the two queries so your comparison is even further off track. ;-)Wed, 05 Sep 2012 15:33:09 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspxSean,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 toWed, 05 Sep 2012 15:22:39 GMTAriel Nessihttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspx[quote][b]ariel_mlk (9/5/2012)[/b][hr]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 =)[/quote]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...Wed, 05 Sep 2012 15:18:38 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspxSean,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 =)Wed, 05 Sep 2012 15:04:05 GMTAriel Nessihttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspx[quote][b]ariel_mlk (9/5/2012)[/b][hr][code="plain"]ALTER FUNCTION [dbo].[SplitString]( @String VARCHAR(MAX), @Delimeter Char(1)) RETURNS @RtnValue TABLE( Value VARCHAR(100)) AS BEGIN DECLARE @Cnt INT SET @Cnt = 1 WHILE (CHARINDEX(@Delimeter, @String) > 0) BEGIN INSERT INTO @RtnValue (Value) SELECT Data = ltrim(rtrim(Substring(@String,1,Charindex(@Delimeter,@String)-1))) SET @String = Substring(@String,Charindex(@Delimeter,@String)+1,len(@String)) SET @Cnt = @Cnt + 1 END INSERT INTO @RtnValue (Value) SELECT Data = ltrim(rtrim(@String)) RETURNEND[/code]I'm aware this is RBAR and i'm aware of tally techinics but the strings that this functions breaks are usually of something about 200 characters? but mostly it comes as a null, can that make enourmous IO counts?[/quote]No offense but if you are aware that this is subpar for performance why not change it out? This process and every other process that uses your splitter will gain an advantage.Wed, 05 Sep 2012 14:39:50 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspx[code="plain"]ALTER FUNCTION [dbo].[SplitString]( @String VARCHAR(MAX), @Delimeter Char(1)) RETURNS @RtnValue TABLE( Value VARCHAR(100)) AS BEGIN DECLARE @Cnt INT SET @Cnt = 1 WHILE (CHARINDEX(@Delimeter, @String) > 0) BEGIN INSERT INTO @RtnValue (Value) SELECT Data = ltrim(rtrim(Substring(@String,1,Charindex(@Delimeter,@String)-1))) SET @String = Substring(@String,Charindex(@Delimeter,@String)+1,len(@String)) SET @Cnt = @Cnt + 1 END INSERT INTO @RtnValue (Value) SELECT Data = ltrim(rtrim(@String)) RETURNEND[/code]I'm aware this is RBAR and i'm aware of tally techinics but the strings that this functions breaks are usually of something about 200 characters? but mostly it comes as a null, can that make enourmous IO counts?Wed, 05 Sep 2012 13:45:00 GMTAriel Nessihttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspx[quote][b]ariel_mlk (9/5/2012)[/b][hr]Sean, Sorry to not have posted that back in the first post, as i stated I though I was missing something obvious enough to discard ddl, i editted it with more information on the problem. On that small set the query optimizer will even identify the rCTE with as the most expensive piece, but when set gets bigger it shifts gears, but again, the rCTE has more IO/CPU on a Set Statistics IO/TIME ON comparison. So my question really is how can it be more IO/CPU time consuming and be rated as more performatic by the Actual Plan percentage?[/quote]Thanks for the ddl and sample data. There is a critical piece in your cte that is NOT in the original query. You have the SplitString function in the cte. Can you post the code for that? I have a feeling I may know why your performance is taking a hit.Wed, 05 Sep 2012 13:39:21 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspxSean, Sorry to not have posted that back in the first post, as i stated I though I was missing something obvious enough to discard ddl, i editted it with more information on the problem. On that small set the query optimizer will even identify the rCTE with as the most expensive piece, but when set gets bigger it shifts gears, but again, the rCTE has more IO/CPU on a Set Statistics IO/TIME ON comparison. So my question really is how can it be more IO/CPU time consuming and be rated as more performatic by the Actual Plan percentage?Wed, 05 Sep 2012 13:34:39 GMTAriel Nessihttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspx[quote]what first querie does is a like on Hierarchy column, what mine does is a rcte from ID to ParentID pretty much like BOL examples... what I really don't understand is why query optimizer tells me the rcte is cheaper execution wise whilst it produces way more IO and CPU time than the like approach, by that i mean that this :[/quote]It may require a little more IO and CPU but overall it will be WAY faster because it doesn't have to do a full scan just to join the data. The way you are doing with ID to ParentID will allow for the index on those columns to be used in your query. I am no guru when it comes to how the query engine works but I can say that your version will be far superior to the "Like join" approach. If you want it to go even faster you might consider moving to the nested sets model that CELKO talked about.Wed, 05 Sep 2012 12:25:44 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspx[quote][b]ariel_mlk (9/5/2012)[/b][hr]Celko and Sean,Thanks for the responses, I'm sorry i don't think i was clear enough, what I have is something likeID ParentID Hierarchy1 NULL 12 1 1.23 2 1.2.34 1 1.4...what first querie does is a like on Hierarchy column, what mine does is a rcte from ID to ParentID pretty much like BOL examples... what I really don't understand is why query optimizer tells me the rcte is cheaper execution wise whilst it produces way more IO and CPU time than the like approach, by that i mean that this :[code="other"]SELECT stChild.OUID, stChild.OUParentID, stChild.CustomerID, stChild.Name, stChild.NameFull, stChild.Hierarchy, stChild.HierarchyDepth, stChild.Enabled, st.OUID as OUIDAncestorFROM SomeTable st INNER JOIN SomeTable stChild ON stChild.Hierarchy + '.' LIKE st.Hierarchy + '.%'[/code]produces less IO and CPU time than a cookie cutter rcte having a anchor on top-level rows and recursing on childsEdit : just setting sql code in the right container ( was in a quote )[/quote]Without ddl and sample data there is no way to know what is going on. If you need to know how to post that see the first link in my signature.If you have the parentid as a column you really should join like I demonstrated. The way you are joining using like is going to cause this to be horribly slow.Wed, 05 Sep 2012 12:19:59 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspxCelko and Sean,Thanks for the responses, I'm sorry i don't think i was clear enough, what I have is something likeID ParentID Hierarchy1 NULL 12 1 1.23 2 1.2.34 1 1.4...what first querie does is a like on Hierarchy column, what mine does is a rcte from ID to ParentID pretty much like BOL examples... what I really don't understand is why query optimizer tells me the rcte is cheaper execution wise whilst it produces way more IO and CPU time than the like approach, by that i mean that this :[code="other"]SELECT stChild.OUID, stChild.OUParentID, stChild.CustomerID, stChild.Name, stChild.NameFull, stChild.Hierarchy, stChild.HierarchyDepth, stChild.Enabled, st.OUID as OUIDAncestorFROM SomeTable st INNER JOIN SomeTable stChild ON stChild.Hierarchy + '.' LIKE st.Hierarchy + '.%'[/code]produces less IO and CPU time than a cookie cutter rcte having a anchor on top-level rows and recursing on childsEdit : just setting sql code in the right container ( was in a quote )Wed, 05 Sep 2012 11:16:27 GMTAriel Nessihttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspxCan you re-write this as a nested sets model? The bad news is that when you write the cursor to get the old stuff loaded, you often find out that the hierarchy was screwed up.Tue, 04 Sep 2012 17:43:28 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspx[quote][b]ariel_mlk (9/4/2012)[/b][hr]Hi people, I'm tunning a database and i've stumbled by some hierarchy queries, on the good old form of ID, ParentID, to work out the hierarchy the previous developers used a like logic as inSELECT TB1.* FROM TABLE1 as TB1 ON TABLE1 as TB2 on TB2.Hierarchy + '.' LIKE TB1.Hierarchy + '.%'where hierarchy field contains the "path" to the parent nodes for example 1.2.3.4.5 would mean, this row is 5, parent is 4, grandparent is 3 and so forthi've rewritten that in a CTE, and the query plan shouts out at me that it takes something like 25% of the cost in batch when running the rCTE approach and the like approach ( which would lead me to think that the rCTE is by far faster than the original ) but the like clause seems to take way lesser reads, i've read somewhere that the query plan only looks at the cost of a first execution of a rCTE, but as I understand rCTE were meant for jobs like this one, so how can a nasty non-sargable like go by less reads than a rCTE ?sry if the post is not clear enough,I haven't posted the query plans in the belief that i'm just misunderstanding something, if it ain't the case, i'll promptly post those[/quote]God that is awful. At least they are former developers so they won't continue creating stuff like that for you to work with. That is an adjacency list that is denormalized. I would start by getting rid of anything other than the immediate parent. The rest of that is nothing but pain. Once you start joining on like '%' you are in for a horrible performance. Say goodbye to indexing. I tossed together some very simplified ddl and and sample data to see if your problem is what I think it is. [code];with BadAdj ( KeyVal, SomeVal, ParentKeyVal, AwfulPath)as( select 1, 'Top Dog', null, '1' union all select 2, 'Next Dog', 1, '1.2' union all select 3, 'Grandchild', 2, '1.2.3' union all select 4, 'Uber Grandchild', 3, '1.2.3.4' union all select 5, 'Step Grandchild', 4, '1.2.3.4.5')select * from BadAdj[/code]If I understand what you are facing...you have something like AwfulPath instead of the simple ParentKeyVal? If so, do you have the ability to change the ddl into something more usable? Even if you have to leave the whole path adding a new column to identify the parent would go a long way to making this easier to work with.Tue, 04 Sep 2012 14:38:12 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1354147-392-1.aspxHi people, I'm tunning a database and i've stumbled by some hierarchy queries, on the good old form of ID, ParentID, to work out the hierarchy the previous developers used a like logic as inSELECT TB1.* FROM TABLE1 as TB1 ON TABLE1 as TB2 on TB2.Hierarchy + '.' LIKE TB1.Hierarchy + '.%'where hierarchy field contains the "path" to the parent nodes for example 1.2.3.4.5 would mean, this row is 5, parent is 4, grandparent is 3 and so forthi've rewritten that in a CTE, and the query plan shouts out at me that it takes something like 25% of the cost in batch when running the rCTE approach and the like approach ( which would lead me to think that the rCTE is by far faster than the original ) but the like clause seems to take way lesser reads, i've read somewhere that the query plan only looks at the cost of a first execution of a rCTE, but as I understand rCTE were meant for jobs like this one, so how can a nasty non-sargable like go by less reads than a rCTE ?sry if the post is not clear enough,I haven't posted the query plans in the belief that i'm just misunderstanding something, if it ain't the case, i'll promptly post thoseEdit : As requested, here is the DDL of the tables (Please note, i've took away field that are irrelevant for the problem logic)[code="other"]--Table DDLCreate Table #Hierarchy ( [OUID] [int] Primary Key, [OUParentID] [int] NULL, [CustomerID] [int] NOT NULL, [Enabled] [bit] NULL, [Hierarchy] [varchar](256) NULL, )--Sample Insert of 26 rowsSELECT '364',NULL,'14','1','364' UNION ALLSELECT '365','364','14','1','364.365' UNION ALLSELECT '366','365','14','1','364.365.366' UNION ALLSELECT '367','366','14','1','364.365.366.367' UNION ALLSELECT '368','367','14','1','364.365.366.367.368' UNION ALLSELECT '369','367','14','1','364.365.366.367.369' UNION ALLSELECT '370','367','14','1','364.365.366.367.370' UNION ALLSELECT '371','364','14','1','364.371' UNION ALLSELECT '372','371','14','1','364.371.372' UNION ALLSELECT '373','372','14','1','364.371.372.373' UNION ALLSELECT '374','373','14','1','364.371.372.373.374' UNION ALLSELECT '375','374','14','1','364.371.372.373.374.375' UNION ALLSELECT '376','372','14','1','364.371.372.376' UNION ALLSELECT '377','376','14','1','364.371.372.376.377' UNION ALLSELECT '378','377','14','1','364.371.372.376.377.378' UNION ALLSELECT '379','372','14','1','364.371.372.379' UNION ALLSELECT '380','379','14','1','364.371.372.379.380' UNION ALLSELECT '381','380','14','1','364.371.372.379.380.381' UNION ALLSELECT '382','372','14','1','364.371.372.382' UNION ALLSELECT '383','382','14','1','364.371.372.382.383' UNION ALLSELECT '384','383','14','1','364.371.372.382.383.384' UNION ALLSELECT '385','372','14','1','364.371.372.385' UNION ALLSELECT '386','385','14','1','364.371.372.385.386' UNION ALLSELECT '387','386','14','1','364.371.372.385.386.387' UNION ALLSELECT '388','372','14','1','364.371.372.388' UNION ALLSELECT '389','388','14','1','364.371.372.388.389'--Original Query ApproachDECLARE @pCustomerID int, @pUserID int, @pOUIDs varchar(max), @pLocationIds varchar(max)SET @pCustomerID = 14SET @pUserID = 1SET @pOUIDs = NULLSET @pLocationIDs = NULLSELECT 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 ApproachWITH LocationHierarchy As ( SELECT ouf.OUID,ouf.OUParentID,ouf.CustomerID,ouf.Hierarchy,ouf.Enabled FROM #Hierarchy ouf Where OUID in (select Value from dbo.SplitString(@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[/code]Tue, 04 Sep 2012 13:35:32 GMTAriel Nessi