August 13, 2015 at 3:25 am
Hi All,
I have a Transactional table containing IDs that relate to each other. The table highlights the transactions that move one ID to another, by storing an ID and a IDNew (Basically from ID to ID).
My requirement is to create a Inline Table Valued Function that will efficiently return a single ID column to show all related IDs by looking at the ID and the IDNew fields.
I need to pass in a single ID to get all relations or a list of IDs through xml.
I have managed to achieve this by making use of two recursive cte's and then combining the results with union statements to create a distinct list of values. This seems to work fine, but I am not sure if it is the most robust or efficient approach to solve this problem.
This script has a few attempts to try and prevent max recursion issues, I was not sure of a more elegant approach to resolve this!
Included script is my attempt.
Any suggestions for improving this solution would be most appreciated.
thanks!
IF OBJECT_ID (N'tempdb..#Trans', N'U') IS NOT NULL
BEGIN
DROP TABLE #Trans
END
CREATE TABLE #Trans
(
TranID int identity(1,1),
BenID int,
BenIDNew int
)
INSERT #Trans
(BenID, BenIDNew)
VALUES
(114,125),(125,114),(125,129),(129,197),(138,125),(139,125),(197,198),(198,199),(125,114),(129,197),(198,199),(134,135)
SELECT TranID, BenID, BenIDNew
FROM #Trans
DECLARE @BenID int = null, @BenIDs xml = '<IDs><ID>114</ID><ID>134</ID></IDs>'
;WITH cteTrans AS
(
SELECT BenID, BenIDNew
FROM #Trans
GROUP BY BenID, BenIDNew
),
cteRelatedBens AS
(
SELECT CA.BenID, CA.BenIDNew
FROM cteTrans CA
WHERE (@BenID IS NULL
OR BenID = @BenID)
AND (@BenIDs IS NULL
OR BenID IN (SELECT ids.ID.value('.','Int') AS ID FROM @BenIDs.nodes('/IDs/ID') AS ids(ID)))
UNION ALL
SELECT T.BenID, T.BenIDNew
FROM cteTrans T
INNER JOIN cteRelatedBens RB ON T.BenID = RB.BenIDNew
AND T.BenIDNew > RB.BenID
),
cteRelatedBensNew AS
(
SELECT T.BenID, T.BenIDNew
FROM cteTrans T
WHERE (@BenID IS NULL
OR BenIDNew = @BenID)
AND (@BenIDs IS NULL
OR BenIDNew IN (SELECT ids.ID.value('.','Int') AS ID FROM @BenIDs.nodes('/IDs/ID') AS ids(ID)))
UNION ALL
SELECT T.BenID, T.BenIDNew
FROM cteTrans T
INNER JOIN cteRelatedBensNew RBN ON T.BenIDNew = RBN.BenID
AND T.BenID > RBN.BenIDNew
)
,cteAllBens AS
(
SELECT BenID FROM cteRelatedBens
UNION
SELECT BenIDNew FROM cteRelatedBens
UNION
SELECT BenID FROM cteRelatedBensNew
UNION
SELECT BenIDNewFROM cteRelatedBensNew
)
SELECT BenID
FROM cteAllBens
August 13, 2015 at 7:16 am
Bruceo (8/13/2015)
Hi All,I have a Transactional table containing IDs that relate to each other. The table highlights the transactions that move one ID to another, by storing an ID and a IDNew (Basically from ID to ID).
My requirement is to create a Inline Table Valued Function that will efficiently return a single ID column to show all related IDs by looking at the ID and the IDNew fields.
I need to pass in a single ID to get all relations or a list of IDs through xml.
I have managed to achieve this by making use of two recursive cte's and then combining the results with union statements to create a distinct list of values. This seems to work fine, but I am not sure if it is the most robust or efficient approach to solve this problem.
This script has a few attempts to try and prevent max recursion issues, I was not sure of a more elegant approach to resolve this!
Included script is my attempt.
Any suggestions for improving this solution would be most appreciated.
thanks!
IF OBJECT_ID (N'tempdb..#Trans', N'U') IS NOT NULL
BEGIN
DROP TABLE #Trans
END
CREATE TABLE #Trans
(
TranID int identity(1,1),
BenID int,
BenIDNew int
)
INSERT #Trans
(BenID, BenIDNew)
VALUES
(114,125),(125,114),(125,129),(129,197),(138,125),(139,125),(197,198),(198,199),(125,114),(129,197),(198,199),(134,135)
SELECT TranID, BenID, BenIDNew
FROM #Trans
DECLARE @BenID int = null, @BenIDs xml = '<IDs><ID>114</ID><ID>134</ID></IDs>'
;WITH cteTrans AS
(
SELECT BenID, BenIDNew
FROM #Trans
GROUP BY BenID, BenIDNew
),
cteRelatedBens AS
(
SELECT CA.BenID, CA.BenIDNew
FROM cteTrans CA
WHERE (@BenID IS NULL
OR BenID = @BenID)
AND (@BenIDs IS NULL
OR BenID IN (SELECT ids.ID.value('.','Int') AS ID FROM @BenIDs.nodes('/IDs/ID') AS ids(ID)))
UNION ALL
SELECT T.BenID, T.BenIDNew
FROM cteTrans T
INNER JOIN cteRelatedBens RB ON T.BenID = RB.BenIDNew
AND T.BenIDNew > RB.BenID
),
cteRelatedBensNew AS
(
SELECT T.BenID, T.BenIDNew
FROM cteTrans T
WHERE (@BenID IS NULL
OR BenIDNew = @BenID)
AND (@BenIDs IS NULL
OR BenIDNew IN (SELECT ids.ID.value('.','Int') AS ID FROM @BenIDs.nodes('/IDs/ID') AS ids(ID)))
UNION ALL
SELECT T.BenID, T.BenIDNew
FROM cteTrans T
INNER JOIN cteRelatedBensNew RBN ON T.BenIDNew = RBN.BenID
AND T.BenID > RBN.BenIDNew
)
,cteAllBens AS
(
SELECT BenID FROM cteRelatedBens
UNION
SELECT BenIDNew FROM cteRelatedBens
UNION
SELECT BenID FROM cteRelatedBensNew
UNION
SELECT BenIDNewFROM cteRelatedBensNew
)
SELECT BenID
FROM cteAllBens
Here's an ever so slightly different way... I just took the SELECT from the XML variable and made it a CTE, so that you could LEFT JOIN to it and test the ID value for NULL. I'm including the IO STATS for it so you can compare with what you have.
SET STATISTICS IO ON;
CREATE TABLE #Trans (
TranID int identity(1,1),
BenID int,
BenIDNew int
);
INSERT INTO #Trans (BenID, BenIDNew)
VALUES (114,125),
(125,114),
(125,129),
(129,197),
(138,125),
(139,125),
(197,198),
(198,199),
(125,114),
(129,197),
(198,199),
(134,135);
SELECT TranID, BenID, BenIDNew
FROM #Trans
DECLARE @BenID AS int = null, @BenIDs AS xml = '<IDs><ID>114</ID><ID>134</ID></IDs>';
WITH cteTrans AS (
SELECT BenID, BenIDNew
FROM #Trans
GROUP BY BenID, BenIDNew
),
BenIDList AS (
SELECT ids.ID.value('.','Int') AS ID
FROM @BenIDs.nodes('/IDs/ID') AS ids(ID)
),
cteRelatedBens AS (
SELECT CA.BenID, CA.BenIDNew
FROM cteTrans AS CA
LEFT OUTER JOIN BenIDList AS BL
ON CA.BenID = BL.ID
WHERE BenID = ISNULL(@BenID, BenID)
AND (@BenIDs IS NULL OR BL.ID IS NOT NULL)
UNION ALL
SELECT T.BenID, T.BenIDNew
FROM cteTrans T
INNER JOIN cteRelatedBens AS RB
ON T.BenID = RB.BenIDNew
AND T.BenIDNew > RB.BenID
),
cteRelatedBensNew AS (
SELECT T.BenID, T.BenIDNew
FROM cteTrans T
LEFT OUTER JOIN BenIDList AS BL
ON T.BenIDNew = BL.ID
WHERE BenIDNew = ISNULL(@BenID, BenIDNew)
AND (@BenIDs IS NULL OR BL.ID IS NOT NULL)
UNION ALL
SELECT T.BenID, T.BenIDNew
FROM cteTrans AS T
INNER JOIN cteRelatedBensNew AS RBN
ON T.BenIDNew = RBN.BenID
AND T.BenID > RBN.BenIDNew
),
cteAllBens AS (
SELECT BenID
FROM cteRelatedBens
UNION
SELECT BenIDNew
FROM cteRelatedBens
UNION
SELECT BenID
FROM cteRelatedBensNew
UNION
SELECT BenIDNew
FROM cteRelatedBensNew
)
SELECT BenID
FROM cteAllBens
DROP TABLE #Trans
Here's the IO STATS:
Table '#Trans______________________________________________________________________________________________________________000000000100'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(12 row(s) affected)
(12 row(s) affected)
Table '#Trans______________________________________________________________________________________________________________000000000100'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(10 row(s) affected)
Table 'Worktable'. Scan count 12, logical reads 192, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Trans______________________________________________________________________________________________________________000000000100'. Scan count 22, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 13, 2015 at 7:36 am
At first, my modified version of your code used a table variable, but the IO STATS on it were awful, so I went back to a temp table and re-posted both the code and the statistics. However, after doing that, I took a look at the results, and while we get the same results, I'm not sure I understand the results, or perhaps I'm not really clear on the objective. This appears to just be an adjacency list, where you start with some nuimber of starting values, and just navigate your way through the list, based on the New ID becoming the ID to use for each next iteration. However, if I look at the source table and then manually run through that exercise mentally, I don't see a path for some of the results in the query. Can you please clarify exactly what the rules for this are ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 13, 2015 at 7:39 am
You have a circular reference and duplicate rows in your sample data. Is that correct?
August 13, 2015 at 7:45 am
Hi Steve,
Thanks for taking a look and the feedback. I did not think of that approach.
I get exactly the same IO numbers as yours.
Looking at the execution plans side by side, your approach definitely helps with the xml processing and contains fewer operators which is a good start.
I think ultimately, I was looking at trying to eliminate the need for two recursive cte's. I think this could be a potential bottleneck on a bigger data set.
But I am not sure if this will be possible!
Bruce
August 13, 2015 at 7:56 am
Hi Luis,
Yes, there can be duplicates and circular references, that is the complexity.
The business case is as follows.
The table is basically a list of actions that cause movement between beneficiaries, I have just removed the extra fields as they are not needed for this function.
There is no unique constraint between the beneficiaries as any movement can happen between the two.
So 1 transaction can move from beneficiary 1 to beneficiary 2, the very next one could move back to beneficiary 1 or to a separate beneficiary for that matter.
The ultimate goal is just to fetch a unique list of related IDs, dependent on what is passed in. I need all of the movement effected by the ID's passed in.
Hope this explains the goal a bit better..
Bruce
August 13, 2015 at 8:36 am
Hi Steve,
Sorry, my explanation on this might not have been adequate. Let me try to explain again.
From the table BenID and BenIDNew are the movement between Beneficiaries.
What I need to do, is for a specified Beneficiary get all possible relations through the transactions.
I do start with values for the Beneficiary ID(s) that are passed in, those are my anchor transactions.
From the anchor list of transactions (where the BenID or the BenIDNew meet the criteria passed in), I need to navigate through the transactions and pick up all the nested relations.
So from the example: If you run for Ben ID 114, this has movement from BenID 114 to BenIDNew 125 and then from BenID 125 back to BenIDNew 114.
IDs 114 and 125 are my base IDs.
From ID 125, there is movement to 129, from 129 then to 197 etc.
what i need to do, is from just passing in ID 114 get through the full list using this approach.
If i passed in Ben ID 198 for example, the results returned should be all the ID's that move from or to Ben ID 198 and keep iterating through the related records to build up a unique list.
The Result would be ID's 129, 197, 198, 199.
To do this in queries, I would do it this way.
1. Get the base movement
SELECT BenID, BenIDNew
FROM Trans
WHERE BenID = 198 OR BenIDNew = 198
That gives me
BenID 197 to BenIDNew 198
and
BenID 198 to BenIDNew 199
I would then pick up the relations (using the same logic) of BenID 197 and Ben ID 199 and keep getting the IDs until I run out of links.
2. So step two using 197 as the next ID to look at would give me another unique movement of
BenID 129 to BenIDNew 197
3. Step three using ID 199, gives me
BenID 198 to BenIDNew 199 which i have covered already.
August 13, 2015 at 8:45 am
I have just noticed that my script does not work for the scenario, where ID 198 is passed in, it misses out ID 129.
This is because of the where clause on the recursive section of the cte's
AND T.BenIDNew > RB.BenID
if I take that clause out, I get max recursion errors, but as you can see, I cannot get to the correct answers with the clause in place! :ermm:
August 13, 2015 at 8:46 am
Bruceo (8/13/2015)
Hi Steve,Sorry, my explanation on this might not have been adequate. Let me try to explain again.
From the table BenID and BenIDNew are the movement between Beneficiaries.
What I need to do, is for a specified Beneficiary get all possible relations through the transactions.
I do start with values for the Beneficiary ID(s) that are passed in, those are my anchor transactions.
From the anchor list of transactions (where the BenID or the BenIDNew meet the criteria passed in), I need to navigate through the transactions and pick up all the nested relations.
So from the example: If you run for Ben ID 114, this has movement from BenID 114 to BenIDNew 125 and then from BenID 125 back to BenIDNew 114.
IDs 114 and 125 are my base IDs.
From ID 125, there is movement to 129, from 129 then to 197 etc.
what i need to do, is from just passing in ID 114 get through the full list using this approach.
If i passed in Ben ID 198 for example, the results returned should be all the ID's that move from or to Ben ID 198 and keep iterating through the related records to build up a unique list.
The Result would be ID's 129, 197, 198, 199.
To do this in queries, I would do it this way.
1. Get the base movement
SELECT BenID, BenIDNew
FROM Trans
WHERE BenID = 198 OR BenIDNew = 198
That gives me
BenID 197 to BenIDNew 198
and
BenID 198 to BenIDNew 199
I would then pick up the relations (using the same logic) of BenID 197 and Ben ID 199 and keep getting the IDs until I run out of links.
2. So step two using 197 as the next ID to look at would give me another unique movement of
BenID 129 to BenIDNew 197
3. Step three using ID 199, gives me
BenID 198 to BenIDNew 199 which i have covered already.
Okay, that at least clears up any confusion, and now I'm thinking that your query does not return the correct result set. I'm going to need some time to put together a query on this, and I'll post something as soon as I have it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 13, 2015 at 8:57 am
Thanks for taking the time to understand the issue and assist me on this.
August 13, 2015 at 9:23 am
Bruceo (8/13/2015)
Thanks for taking the time to understand the issue and assist me on this.
Now I'm thinking that I had to go through this twice to fully understand. Your query appears to get the right answer after all, now that I realize that the "adjacency" is in either direction. Here's the code I created, but it doesn't appear to be as efficient as yours:
SET STATISTICS IO ON;
CREATE TABLE #Trans (
TranID int identity(1,1),
BenID int,
BenIDNew int
);
INSERT INTO #Trans (BenID, BenIDNew)
VALUES (114,125),
(125,114),
(125,129),
(129,197),
(138,125),
(139,125),
(197,198),
(198,199),
(125,114),
(129,197),
(198,199),
(134,135);
SELECT TranID, BenID, BenIDNew
FROM #Trans
DECLARE @BenID AS int = null, @BenIDs AS xml = '<IDs><ID>114</ID><ID>134</ID></IDs>';
SELECT ids.ID.value('.','Int') AS ID
INTO #BenIDList
FROM @BenIDs.nodes('/IDs/ID') AS ids(ID);
WITH cteTrans AS (
SELECT T.BenID, T.BenIDNew, MIN(T.TranID) AS TranID
FROM #Trans AS T
WHERE NOT EXISTS (SELECT 1 FROM #Trans AS T2 WHERE T2.BenID = T.BenIDNew AND T2.BenIDNew = T.BenID AND T2.TranID < T.TranID)
GROUP BY T.BenID, T.BenIDNew
),
AllBens AS (
SELECT T.BenID, T.BenIDNew, T.TranID
FROM #BenIDList AS BL
INNER JOIN cteTrans AS T
ON BL.ID = T.BenID
UNION ALL
SELECT CT.BenID, CT.BenIDNew, MIN(CT.TranID) OVER(PARTITION BY CT.BenID) AS TranID
FROM cteTrans AS CT
INNER JOIN AllBens AS AB
ON CT.BenIDNew = AB.BenID
AND CT.TranID > AB.TranID
UNION ALL
SELECT CT.BenID, CT.BenIDNew, MIN(CT.TranID) OVER(PARTITION BY CT.BenID) AS TranID
FROM cteTrans AS CT
INNER JOIN AllBens AS AB
ON CT.BenID = AB.BenIDNew
AND CT.TranID > AB.TranID
)
SELECT BenID
FROM AllBens
UNION
SELECT BenIDNew AS BenID
FROM AllBens
DROP TABLE #Trans
DROP TABLE #BenIDList
Here's the stats:
Table '#Trans______________________________________________________________________________________________________________00000000011E'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(12 row(s) affected)
(1 row(s) affected)
(12 row(s) affected)
Table '#Trans______________________________________________________________________________________________________________00000000011E'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(2 row(s) affected)
(1 row(s) affected)
(10 row(s) affected)
Table 'Worktable'. Scan count 44, logical reads 258, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Trans______________________________________________________________________________________________________________00000000011E'. Scan count 54, logical reads 96, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#BenIDList__________________________________________________________________________________________________________00000000011F'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 17, 2015 at 2:44 am
Thanks for the assistance, I will use this as a base with some tweaks for my solution.
August 17, 2015 at 6:58 am
Bruceo (8/17/2015)
Thanks for the assistance, I will use this as a base with some tweaks for my solution.
Glad I could help. Let us know how it works out.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 20, 2015 at 8:22 am
In case you were wondering, this was the final solution.
The other scripts partially worked, if you tested with ID 198, it did not return the full list of relations, looking at the combinations in both directions,
It is not the most efficient solution I am sure, but it works!
DECLARE @BenID AS int = null, @BenIDs AS xml = '<IDs><ID>138</ID></IDs>';
IF OBJECT_ID (N'tempdb..#BenIDList', N'U') IS NOT NULL
BEGIN
DROP TABLE #BenIDList
END
SELECT ids.ID.value('.','Int') AS ID
INTO #BenIDList
FROM @BenIDs.nodes('/IDs/ID') AS ids(ID);
IF OBJECT_ID (N'tempdb..#Trans', N'U') IS NOT NULL
BEGIN
DROP TABLE #Trans
END
CREATE TABLE #Trans
(
TranID int identity(1,1),
BenID int,
BenIDNew int
)
INSERT #Trans
(BenID, BenIDNew)
VALUES
(114,125),(125,114),(125,129),(129,197),(138,125),(139,125),(197,198),(198,199),(125,114),(129,197),(198,199),(134,135)
SET STATISTICS IO ON
;WITH Trans AS -- Get Distinct list of Ben combinations
(
SELECT MinBenID, MaxBenID,
ROW_NUMBER() OVER (ORDER BY MinBenID, MaxBenID) AS RowN
FROM
(
SELECT
MIN(CASE WHEN BenID > BenIDNew THEN BenIDNew ELSE BenID END)AS MinBenID,
MIN(CASE WHEN BenIDNew > BenID THEN BenIDNew ELSE BenID END)AS MaxBenID
FROM #Trans
WHERE BenID IS NOT NULL
GROUP BY BenID, BenIDNew
) Bens
GROUP BY MinBenID, MaxBenID
)
,TranBens AS -- Expand list
(
SELECT MinBenID AS BenID, RowN
FROM Trans
UNION
SELECT MaxBenID AS BenID, RowN
FROM Trans
),
BensRange AS -- Get the Min/Max range for BenIDs
(
SELECT BenID, T.MinBenID, T.MaxBenID,
MIN(TB.RowN) AS RowMin,
MAX(TB.RowN) AS RowMax
FROM TranBens TB
INNER JOIN Trans T ON TB.RowN = T.RowN
GROUP BY BenID, T.MinBenID, T.MaxBenID
)
,MinBens AS -- Recurse list down
(
SELECT
B.ID AS BenID, BR.MinBenID, BR.MaxBenID, BR.RowMin, BR.RowMax
FROM #BenIDList AS B
INNER JOIN BensRange AS BR ON B.ID = BR.BenID
UNION ALL
SELECT BR.BenID, BR.MinBenID, BR.MaxBenID, BR.RowMin, BR.RowMax
FROM BensRange AS BR
INNER JOIN MinBens AS MB
ON (BR.BenID = MB.MinBenID OR BR.BenID = MB.MaxBenID)
AND BR.RowMin < MB.RowMin
)
,MaxBens AS -- Recurse list up
(
SELECT
BenID, MinBenID, MaxBenID, RowMax
FROM MinBens AS MB
UNION ALL
SELECT BR.BenID, BR.MinBenID, BR.MaxBenID, BR.RowMax
FROM BensRange AS BR
INNER JOIN MaxBens AS MB
ON (BR.BenID = MB.MinBenID OR BR.BenID = MB.MaxBenID)
AND BR.RowMax > MB.RowMax
)
,AllBens AS -- Combine Results
(
SELECT MinBenID BenID FROM MinBens
UNION
SELECT MaxBenID FROM MinBens
UNION
SELECT MinBenID FROM MaxBens
UNION
SELECT MaxBenID FROM MaxBens
)
SELECT BenID
FROM AllBens
ORDER BY BenID
stats are not that great, but it does have to do a lot of traversing in both directions
Table '#Trans______________________________________________________________________________________________________________000000005169'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 12, logical reads 462, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Trans______________________________________________________________________________________________________________000000005169'. Scan count 246, logical reads 246, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#BenIDList__________________________________________________________________________________________________________000000005168'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
August 20, 2015 at 11:24 am
Bruceo (8/20/2015)
In case you were wondering, this was the final solution.The other scripts partially worked, if you tested with ID 198, it did not return the full list of relations, looking at the combinations in both directions,
It is not the most efficient solution I am sure, but it works!
DECLARE @BenID AS int = null, @BenIDs AS xml = '<IDs><ID>138</ID></IDs>';
IF OBJECT_ID (N'tempdb..#BenIDList', N'U') IS NOT NULL
BEGIN
DROP TABLE #BenIDList
END
SELECT ids.ID.value('.','Int') AS ID
INTO #BenIDList
FROM @BenIDs.nodes('/IDs/ID') AS ids(ID);
IF OBJECT_ID (N'tempdb..#Trans', N'U') IS NOT NULL
BEGIN
DROP TABLE #Trans
END
CREATE TABLE #Trans
(
TranID int identity(1,1),
BenID int,
BenIDNew int
)
INSERT #Trans
(BenID, BenIDNew)
VALUES
(114,125),(125,114),(125,129),(129,197),(138,125),(139,125),(197,198),(198,199),(125,114),(129,197),(198,199),(134,135)
SET STATISTICS IO ON
;WITH Trans AS -- Get Distinct list of Ben combinations
(
SELECT MinBenID, MaxBenID,
ROW_NUMBER() OVER (ORDER BY MinBenID, MaxBenID) AS RowN
FROM
(
SELECT
MIN(CASE WHEN BenID > BenIDNew THEN BenIDNew ELSE BenID END)AS MinBenID,
MIN(CASE WHEN BenIDNew > BenID THEN BenIDNew ELSE BenID END)AS MaxBenID
FROM #Trans
WHERE BenID IS NOT NULL
GROUP BY BenID, BenIDNew
) Bens
GROUP BY MinBenID, MaxBenID
)
,TranBens AS -- Expand list
(
SELECT MinBenID AS BenID, RowN
FROM Trans
UNION
SELECT MaxBenID AS BenID, RowN
FROM Trans
),
BensRange AS -- Get the Min/Max range for BenIDs
(
SELECT BenID, T.MinBenID, T.MaxBenID,
MIN(TB.RowN) AS RowMin,
MAX(TB.RowN) AS RowMax
FROM TranBens TB
INNER JOIN Trans T ON TB.RowN = T.RowN
GROUP BY BenID, T.MinBenID, T.MaxBenID
)
,MinBens AS -- Recurse list down
(
SELECT
B.ID AS BenID, BR.MinBenID, BR.MaxBenID, BR.RowMin, BR.RowMax
FROM #BenIDList AS B
INNER JOIN BensRange AS BR ON B.ID = BR.BenID
UNION ALL
SELECT BR.BenID, BR.MinBenID, BR.MaxBenID, BR.RowMin, BR.RowMax
FROM BensRange AS BR
INNER JOIN MinBens AS MB
ON (BR.BenID = MB.MinBenID OR BR.BenID = MB.MaxBenID)
AND BR.RowMin < MB.RowMin
)
,MaxBens AS -- Recurse list up
(
SELECT
BenID, MinBenID, MaxBenID, RowMax
FROM MinBens AS MB
UNION ALL
SELECT BR.BenID, BR.MinBenID, BR.MaxBenID, BR.RowMax
FROM BensRange AS BR
INNER JOIN MaxBens AS MB
ON (BR.BenID = MB.MinBenID OR BR.BenID = MB.MaxBenID)
AND BR.RowMax > MB.RowMax
)
,AllBens AS -- Combine Results
(
SELECT MinBenID BenID FROM MinBens
UNION
SELECT MaxBenID FROM MinBens
UNION
SELECT MinBenID FROM MaxBens
UNION
SELECT MaxBenID FROM MaxBens
)
SELECT BenID
FROM AllBens
ORDER BY BenID
stats are not that great, but it does have to do a lot of traversing in both directions
Table '#Trans______________________________________________________________________________________________________________000000005169'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 12, logical reads 462, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Trans______________________________________________________________________________________________________________000000005169'. Scan count 246, logical reads 246, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#BenIDList__________________________________________________________________________________________________________000000005168'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Glad you have something that works. I did go back and test, and sure enough, ID 198 fails, so I re-examined what I was doing, and realized that the problem was related to the way I filtered the original set of data. As I also realized that the order in which the values appear is irrelevant, it was easy to add a couple of CASE statements that could force the value order to be smaller first, larger second. Then I realized that the TranID from the original table would have to get replaced with a ROW_NUMBER(), so that was added to the mix as well, and voila, ID 198 passes the test. This appears to better from a statistics IO perspective as well.
SET STATISTICS IO ON;
CREATE TABLE #Trans (
TranID int identity(1,1),
BenID int,
BenIDNew int
);
INSERT INTO #Trans (BenID, BenIDNew)
VALUES (114,125),
(125,114),
(125,129),
(129,197),
(138,125),
(139,125),
(197,198),
(198,199),
(125,114),
(129,197),
(198,199),
(134,135);
SELECT TranID, BenID, BenIDNew
FROM #Trans
DECLARE @BenID AS int = null, @BenIDs AS xml = '<IDs><ID>198</ID></IDs>';
SELECT ids.ID.value('.','Int') AS ID
INTO #BenIDList
FROM @BenIDs.nodes('/IDs/ID') AS ids(ID);
WITH cteTrans AS (
SELECT
CASE WHEN T.BenID < T.BenIDNew THEN T.BenID ELSE T.BenIDNew END AS BenID,
CASE WHEN T.BenID < T.BenIDNew THEN T.BenIDNew ELSE T.BenID END AS BenIDNew,
ROW_NUMBER() OVER(ORDER BY CASE WHEN T.BenID < T.BenIDNew THEN T.BenID ELSE T.BenIDNew END,
CASE WHEN T.BenID < T.BenIDNew THEN T.BenIDNew ELSE T.BenID END) AS TranID
FROM #Trans AS T
WHERE NOT EXISTS (SELECT 1 FROM #Trans AS T2 WHERE T2.BenID = T.BenIDNew AND T2.BenIDNew = T.BenID AND T2.TranID < T.TranID)
GROUP BY CASE WHEN T.BenID < T.BenIDNew THEN T.BenID ELSE T.BenIDNew END,
CASE WHEN T.BenID < T.BenIDNew THEN T.BenIDNew ELSE T.BenID END
),
AllBens AS (
SELECT T.BenID, T.BenIDNew, T.TranID
FROM #BenIDList AS BL
INNER JOIN cteTrans AS T
ON BL.ID = T.BenID
UNION ALL
SELECT CT.BenID, CT.BenIDNew, MIN(CT.TranID) OVER(PARTITION BY CT.BenID) AS TranID
FROM cteTrans AS CT
INNER JOIN AllBens AS AB
ON CT.BenIDNew = AB.BenID
AND CT.TranID < AB.TranID
UNION ALL
SELECT CT.BenID, CT.BenIDNew, MIN(CT.TranID) OVER(PARTITION BY CT.BenID) AS TranID
FROM cteTrans AS CT
INNER JOIN AllBens AS AB
ON CT.BenID = AB.BenIDNew
AND CT.TranID < AB.TranID
)
SELECT BenID
FROM AllBens
UNION
SELECT BenIDNew AS BenID
FROM AllBens
DROP TABLE #Trans
DROP TABLE #BenIDList
Results:
BenID
114
125
129
197
198
199
STATS:
Table '#Trans______________________________________________________________________________________________________________00000000018E'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(12 row(s) affected)
(12 row(s) affected)
Table '#Trans______________________________________________________________________________________________________________00000000018E'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(6 row(s) affected)
Table 'Worktable'. Scan count 30, logical reads 144, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Trans______________________________________________________________________________________________________________00000000018E'. Scan count 44, logical reads 286, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#BenIDList__________________________________________________________________________________________________________00000000018F'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Let me know what you think...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply