I can only repeat the same as Hugo and Najm, it is hard to say anything
without seeing the actual table, indexes, sample data, required results,
and
business requirements/rules. Reading all those posts in the link at
sqlteam.com I can see only sample code provided by Peso (Peter Larsson).
The
problem could be difficult or easy to solve, but hard to say without
seeing
the real requirements.
Perhaps some things to consider are using materialized path or nested sets
to stored the hierarchy of contacts. Those models provide very efficient
retrieval of distance between nodes info, but have more difficult methods
for maintaining data.
Here is just a small sample (using the sample table and data provided by
Peso) on how materialized path may look:
-- Sample table with data
CREATE TABLE Contacts (
c_from CHAR(1),
c_to CHAR(1),
PRIMARY KEY (c_from, c_to));
INSERT INTO Contacts
SELECT 'A', 'B' UNION ALL
SELECT 'B', 'D' UNION ALL
SELECT 'C', 'A' UNION ALL
SELECT 'C', 'E' UNION ALL
SELECT 'G', 'C' UNION ALL
SELECT 'B', 'G' UNION ALL
SELECT 'F', 'D' UNION ALL
SELECT 'E', 'F';
-- Table to store paths
CREATE TABLE Paths (c_path VARCHAR(200) PRIMARY KEY);
This is the real hurdle, recalculating all paths
-- Recursive CTE to populate the paths
WITH PathCTE
AS
(SELECT c_from, c_to,
CAST('.' + CAST(c_from AS VARCHAR(8)) + '.' +
CAST(c_to AS VARCHAR(8)) + '.' AS VARCHAR(200)) AS c_path
FROM Contacts AS C1
UNION ALL
SELECT C.c_from, C.c_to,
CAST(P.c_path + C.c_to + '.' AS VARCHAR(200))
FROM PathCTE AS P
JOIN Contacts AS C
ON P.c_to = C.c_from
WHERE P.c_path NOT LIKE '%.' +
CAST(C.c_from AS VARCHAR(10)) +
'.' +
CAST(C.c_to AS VARCHAR(10)) +
'.%')
INSERT INTO Paths
SELECT c_path FROM PathCTE;
-- Show all paths between B and D
SELECT c_path
FROM Paths
WHERE c_path LIKE '.B.%'
AND c_path LIKE '%.D.';
-- Shortest path distance, longest path distance, and number of paths
SELECT MIN(LEN(c_path) - LEN(REPLACE(c_path, '.', '')) - 2) AS
shortest_distance,
MAX(LEN(c_path) - LEN(REPLACE(c_path, '.', '')) - 2) AS
longest_distance,
COUNT(*) AS paths_cnt
FROM Paths
WHERE c_path LIKE '.B.%'
AND c_path LIKE '%.D.';
Looking at the paths found:
..B.D.
..B.G.C.A.B.D.
..B.G.C.E.F.D.
You may notice the second path reused the first path to reach the
destination. But this could be desired or not, again hard to say with no
requirements. Easy to handle but did not bother...
As you can see calculating the distance is easy, but maintenance offsets
that. Based on your needs and model, if data is static this may do.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|
